How to fetch latest records from table

[India62]

I have table with date and different lab values for each patient.
Some lab values repeats every month, some every quarter and some semiannually.
I need query to have all values in one row for each patient with only latest or last values.

[pbaldy]

It's difficult to read that. Are you looking for the first or both of these:

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

[India62]

Please see attached what i am looking for

[India62]

Sorry please see attached

[pbaldy]

I think you want 3 of the first query, for A, B & C. Then the second query would join them all.

[India62]

Thanks
But not sure what it means?
You mean I should have four queries and then join them ?
Please explain little more

[pbaldy]

Can you post a sample database with actual data (nothing personal of course)?

[India62]

Here it is.
there 3 Patients ID
When I run query, I need for each patiet ID last result (latest) from each field
There are more fields in actual database
Thanks for help

[June7]

I tried to open your db but Access says it is in inconsistent state and tries to recover it but fails. Do Compact & Repair and replace the upload.

Does each lab test have its own field? That would not be fully normalized data structure but does make this type of output easy. I suspect you should be able to do a Totals query with grouping on the patient ID. Use the Max function in the Totals row for each of the data fields.

[India62]

I don’t know it opens on my computer
Attached is in table structure in excel format.
What I need is when I run query all yellow highlighted value should appear for that particular patient ID
Hope it helps to explain
Thanks

[June7]

I misunderstood that you want the maximum value of each lab test. That would have been easy. Getting the latest lab result is complicated because the lab results are not all on same date. I used DFirst function, like:
SELECT DISTINCT Lab.ID, DFirst("[Dry Weight]","Lab","Not IsNull([Dry Weight]) And ID='" & [ID] & "'") AS DW, DFirst("[KG]","Lab","Not IsNull([KG]) And ID='" & [ID] & "'") AS KG
FROM Lab
ORDER BY Lab.ID;

I normally avoid DFirst and DLast (also First and Last) because requires records to be sorted (in this case by descending date order) and can't rely on that records have been entered into table in the correct order nor that the order will be maintained. However, your data sample shows the correct order. If this does not reflect the actual situation, will have to do an intermediate query to perform the correct sort and then use that query as source for my suggestion.

EDIT: I did try to use DLookup first but it was late last night and wasn't seeing it but think after a good night's sleep might have it.
SELECT Lab.ID, DLookUp("[Dry Weight]","Lab","[Date]=#" & DMax("[Date]","Lab","ID='" & [ID] & "' And Not IsNull([Dry Weight])") & "#") AS LastDW FROM Lab;

As you can see, either method will mean a very long query after you build the expression for each lab test.

[India62]

Thanks
I will try and will let you know. But can we sort date with another update query every time before we run this quey ?
I may not able to work on this today or tommorow but will let you know by Friday.

[India62]

I just tried in my sample database and it works ( yes I do have to find way to sort data before running this query). But you are right it will be long query.
Thanks any way this was very useful.
I will update as SLOVED once done with final check

[June7]

As I said, build a query that does the sort and save it. Then use that query as source for the DFirst query. You mention update query - why, what needs to be updated?