Top values query

[acrocker3434]

Hi-
I have two tables: Patients and Office Visits. There is a one to many relationship between the two (one patient has many office visits). At each office visit we collect different data points:

table=visit
MRN
date
height
weight
blood pressure
hem A1c

I have a top values query set up so that I can see each patient's most recent office visit. The SQL view looks like this:

SELECT Patients.MRN, Max(Visits.Date) AS MostRecentVisit, Patients.LastName, Patients.FirstName, Patients.DOB
FROM Patients INNER JOIN Visits ON Patients.MRN = Visits.MRN
GROUP BY Patients.MRN, Patients.LastName, Patients.FirstName, Patients.DOB;

Now, I want to be able to see the height, weight, and hem A1c associated with that visit BUT when I add these fields to the query I get ALL of them, not just the one associated with the most recent visit.

Any thoughts???

THANK YOU

 

[MarkK]

If you just want the one record for the last visit then you don't need to do a summation (GROUP BY). Also, it looks like your Max(Visits.Date) will not necessarily return the last visit date for the given patient but rather the Max(Date) from the table.
If you just want the visit data you could do

SELECT TOP 1 [Date], Height, Weight, Pressure, Hemo
FROM Visits
WHERE MRN = [prmPatientMRN]
ORDER BY [Date] DESC;

But I'd be most likely to create a Patient form that has a visits subform that sorts by Date DESC and shows ALL visits for that patient. Then the first subform record is the last visit, and at a glance you can get some idea of changes over time and how frequently that particular patient makes appointments, etc...

 

[ByteMyzer]

Try:

SELECT Patients.MRN,
       Visits.Date AS MostRecentVisit,
       Patients.LastName,
       Patients.FirstName,
       Patients.DOB,
       Visits.height,
       Visits.weight,
       Visits.[hem A1c]
FROM     ( Patients
INNER JOIN Visits ON Patients.MRN = Visits.MRN )
INNER JOIN (
             SELECT Visits.MRN, MAX(Visits.Date) AS MaxDate
             FROM Visits
             GROUP BY Visits.MRN
           ) Visits_2 ON  Visits.MRN  = Visits_2.MRN
                      AND Visits.Date = Visits_2.MaxDate;