Query or SQL to return multiple columns with one unique column

Question

I am working is MS Access 2007 with a query that contains the following columns: PATIENT_NUMBER DATE_OF_VISIT BMI

The query contains multiple DATE_OF_VISITS (with associated BMI) for each PATIENT_NUMBER. I have been trying to use SQL to give me an unduplicated list of all patients with the most recent date of visit and the BMI at that visit.

So far I can get a list of the most recent date of visit for each patient but when I try and get BMI as well I start getting multiple instances of patients (since their BMI fluctuates over time)

Answer 1

It always helps to include any queries that you've tried in the question. I think you need something like this:

select t.PATIENT_NUMBER, t.DATE_OF_VISIT, t.BMI
from t inner join
     (select PATIENT_NUMBER, max(DATE_OF_VISIT) as maxdate
      from t
      group by PATIENT_NUMBER
     ) tmax
     on t.PATIENT_NUMBER = tmax.PATIENT_NUMBER and t.DATE_OF_VISIT = tmax.maxdate;

This assumes that there are not duplicate dates for the same patient. If so, you will need some other method to disambiguate them.