Query Top 3 dates for each KEY

[ETID]

Hi,

I have a table of multiple comments called "comments", each comment has a date, each date & comment combination is related to a master table by 4 KEY fields,...

I.E.

|--------KEY FIELDS---------|
| KEY1 | KEY2 | KEY3 | KEY4 | Date | Comment

How do I query just the comments table and get the most recent 3 comments (by date) for each record KEY?


Thanks,

 

[dhookom]

Having 4 fields creating a primary key makes for lots of extra work. I always use a single key. If you had a single field, your query might look like:
SELECT *
FROM tblA
WHERE PriKey IN
(SELECT TOP 3 PriKey
FROM tblA A
WHERE tblA.GroupKey = A.GroupKey
ORDER BY DateField DESC);

Also, Date is a function so you should avoid using Date as a field name.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.