Stumped on a sub query

[GregD]

This is a many table of Chart numbers and labs. Each lab has a lab date and a lab value. There are 65 different labs and some chart numbers may have multiples of each, while others may only have one each of 6 or 7 labs.

What I’m trying to get are the 3 most recent dates of each lab for each chart. I’ve tried several different things, but nothing is quite right. The one below seemed to be the closest, but it wasn’t really all that close. I got the same three lab names for all chart numbers. It returned all of the labs of those three, instead of the top 3 of all.

I also tried the top 3 LabDates, but then I only received 3 dates for multiple labs and charts. When I tried doing a combination of the 2 I would get various errors. One tantalizing error had to do with the EXISTS reserved word, but I couldn’t get it to work.

Any help is appreciated. There is a “one” table of patients that goes with the “many” side of this tblLabs table that is not incorporated in this query. [ChartNumber] is the join field.

SELECT tblLab.ChartNumber, tblLab.ItemName AS LabName, tblLab.LabDate, tblLab.LabValue
FROM tblLab
WHERE (((tblLab.ItemName) In (SELECT
Top 3 [ItemName]
FROM
tblLab ORDER BY [Chartnumber], [ItemName], [LabDate] ASC)))
ORDER BY tblLab.ChartNumber, tblLab.ItemName, tblLab.LabDate DESC;