How I query for the most recent date for a record?

[ejohnso4]

Hello. I have an access database that carries medical information on patients. We are wanting to follow these patients and look at a specific lab value (PSA) over time. I have a PSA table, which has the patient's ID number, the date of the PSA, and the PSA value. Each patient might have 4 or 5 PSA values on different dates. I want to query only the most recent PSA value for all of the patients. However, when I try it ends up listing all the PSA values and not just the most recent. Do you have a solution or know a function to help with this? Here is an example of what the table looks like now:

ID Date PSA Value
1 1/5/10 4.0
1 2/2/10 3.0
1 3/3/10 2.0
2 1/8/10 8
2 2/10/10 5


3 1/1/10 10
3 2/15/10 2

All I want is the patient 1 3/3/10 value, the patient 2 2/10/10 value, and patient 3 2/15/10 value- i.e. most recent. I would look at them individually but there are over 1000 patients in the database, so that is not optimal. Any help??
3
3

[pbaldy]

This should help:

http://www.baldyweb.com/LastValue.htm

[TheShabz]

Dont know why you would use 2 queries. In the dropdown on the design view with Totals activated, leave ID with Group By and choose Max for the Date field. That should suffice, no?

[pbaldy]

Dont know why you would use 2 queries. In the dropdown on the design view with Totals activated, leave ID with Group By and choose Max for the Date field. That should suffice, no?

That will get you the max date, not the corresponding PSA value.

[TheShabz]

correct your are sir.