Selecting most recent record

[RAWC]

I need to find the most recent record for a patient in a table of Lab Results. From this table, I need to get the Patient Chart Number, a specific test (Hgb A1C), the result of that test, and the most recent results (there are multiple records (dates) in the table for each patient). The problem seems to be that if I select DISTINCT, it is recognizing the each patient test as being a distinct value. I have tried MAX on the date, and I am still getting multiple results for each patient. I can not send database, due to HIIPA rules and patient confidentiality.

 

[cmmrfrds]

Can you put a timestamp column on the table. If so, it will always be unique and you can do a max to get the latest. The date must not have the time part filled in so that 2 records with the same date cannot be distinquished by date alone. You could make sure the time gets entered in the date/time field. What kind of database? Access 2000?

 

[RAWC]

cmmrfrds
The database is Access 2002. I can add the timestamp, but that wont help with with the existing records (since they all would have the same time... right???). I really hate to have to go through this table and remove the old records manually, especially since I will have to use this query every month. The multiple records are needed for other queries (they show if patients are being tested on a regular basis, and that it is within a defined time period).

 

[WildHare]

If a patient has two or more of the same test on the same day, I don't see how you can figure you which one was "most recent" without a time factor in there too. If, however that is not the situation, then a MAX(date-guy) with a criteria of Patient and Test-name should return your correct record.

MostRecentTestDate = DMAX("[dateguy]", "[tableguy]", "[tableguy]![patientguy] = " & PatientID & " and [tableguy]![testidguy] = " & TestID)

Using appropriate quotes if needed...

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[RAWC]

Ok... figured it out. And maybe I am not good at explaining what I am doing wrong! The problem was (I think!), that I was trying to pull too much into the initial query. Once I broke it up into multiple queries, it worked. First I got the patient number and the latest date. Then I went for the results. I am sure there was a way to do it all with one query, but it works, and my boss is happy. Thanks for the input!