Most recent date query

[Pbear88]

I’m trying to design a query that returns the most recent date for a given criteria. I’ve tried using the Max variable in the Group by section in the query, but that does not work. I think I might need to use the DMax function, but I’m not sure on the proper syntax for the query I’m trying to run.

There are two tables, “PatientsInfo” and “NutritionInfo”. They are linked by PatientID(one-to-many). I’m trying to return the Lastname, Firstname (from the “PatientsInfo” table), and the most recent DateNutr, and VitDlevel, where VitDlevel is >0 and <20.1. DateNutr and VitDlevel are from the “NutritionInfo” table.

Expected outcome would look something like:

Doe|John|6/12/18|14.5
Smith|Jane|3/7/18|18.0

But I’m getting:

Doe|John|6/12/18|17.0
Doe|John|2/14/18|14.5
Smith|Jane|3/7/18|18.0
Smith|Jane|12/17/17|18.0
Smith|Jane|3/14/17|12.6

Any thoughts or suggestions?
Thank you!

[ranman256]

you can only use 2 fields, name,Max(date)
(otherwise you get the max of 3 fields)

so:
Q1 get the max date
select name,Max(date) from table

then Q2, get all the data using Q1:
select * from table ,Q1 where Q1.name = table.name

[Pbear88]

Thanks for the quick reply. The Max date in the table for a person might not have the correct criteria for the VitDLevel. It’s not recorded every time a new record is entered.

[CJ_London]

The Max date in the table for a person might not have the correct criteria for the VitDLevel

modify Q1 to

Q1 get the max date for a given range
select name,Max(date) from table where VitDlevel is >0 and <20.1

[Pbear88]

It's still not working. the max date in the table might not be the max date with the correct VitDlevel criteria. So once I try to add the specific criteria to the query, I get multiple results. If I can only use 2 fields for the Max function, I think I need to tackle this from another angle.

[CJ_London]

Are you responding to post #2 or #4?

[Pbear88]

All of the above. Tried them all and no luck

[CJ_London]

Provide the sql you used for post#4 together with some example data used in that query that illustrates the problem

[Pbear88]

SELECT NutritionInfo.PatientID, Max(NutritionInfo.NutriDate) AS MaxOfNutriDate
FROM NutritionInfo
GROUP BY NutritionInfo.PatientID, NutritionInfo.VitDlevel
HAVING (((NutritionInfo.VitDlevel)>1 And (NutritionInfo.VitDlevel)<20.1));

That gives multiple entries of a PatientID where I'm expecting the most recent date for the given VitDlevel criteria:

PatientID	MaxOfNutriDate
8900001	10/8/2018
8900001	11/13/2018
8900002	11/5/2018
8900002	1/2/2019

[CJ_London]

this

GROUP BY NutritionInfo.PatientID, NutritionInfo.VitDlevel
HAVING (((NutritionInfo.VitDlevel)>1 And (NutritionInfo.VitDlevel)<20.1));


should be this
WHERE (((NutritionInfo.VitDlevel)>1 And (NutritionInfo.VitDlevel)<20.1))
GROUP BY NutritionInfo.PatientID

[Pbear88]

That did it! Thank you so much, Ajax.