How do I list just the most recent orders in an orders table

[dougconran]

Hi,

I have an orders table (partno,description,qty_ordered,order-date,customer) that has some 1000 entries for about 200 parts (ie on average, each part no has 5 entries).

I want to be able to list full details for just the most recent order of each part. I've tried using max(order_date) and Group By but still get a full listing.

I'm using Access 97, what should my sql statement look like?

TIA

Doug Conran

 

[glalsop]

Perhaps you could post what you have thus far.

-Gary

 

[amorous]

This should do it:

SELECT t1.*
FROM Orders t1
WHERE t1.Order_Date In (select top 1 t2.Order_Date from Orders t2 where t2.partno = t1.partno ORDER BY t2.Order_Date Desc);


-VJ

 

[JonFer]

This is more efficient for getting the single most recent date but doesn't allow you to change easily to the 2 or 3 most recent dates, unlike amorous' SQL which is easily expanded.

SELECT t1.*
FROM Orders t1
WHERE t1.Order_Date=(select Max(t2.Order_Date) from Orders t2 where t2.partno = t1.partno);

 

[dougconran]

Thanks VJ (and Gary for responding so quickly), that has worked a treat.

Rgds

Doug