I also created a sample database and did the following (similar to micron) to delete the unwanted records.
Created a query QmaxDatesByProd to get the Max orderdate for each Group
Code:
SELECT TestProds.Product
,Max(TestProds.OrderDate) AS MaxOfOrderDate
,TestProds.Vendor
FROM TestProds
GROUP BY TestProds.Product
,TestProds.Vendor;
Then, created another query QryDeleteWhereNotExists to delete the unwanted records - the ones not in QryMaxDatesByProd
Code:
DELETE *
FROM TestProds AS TP
WHERE NOT EXISTS (
SELECT "x"
FROM QmaxDatesByProd AS Q
WHERE TP.product = Q.product
AND TP.vendor = q.vendor
AND tp.orderdate <> q.MaxOfOrderDate
)
But, I recommend that you review my previous post and be cautious when "physically DELETING" records.