last three records only based on dates

[jademaddy]

I have a table that contains the following fields.

Store Date Loss

This table currently holds about 4 years worth of audit results for each of
my stores. Each store possibly having at least 20 records and there are
approx 800 stores.

Could someone point me in the general direction of producing a query that
will show just the lastest three records per store based on the date field.

Access 2003

Thanks

 

[John Spencer]

You'll need a subquery for this to get the TOP 3 records per store

SELECT *
FROM AuditResults
WHERE {Date] in
(SELECT TOP 3 T.[Date]
FROM AuditResults as T
WHERE T.Store = AuditResults.Store
ORDER BY T.[Date] DESC)