Max query function

[Cordeiro82]

Hello,

I am working with a purchase order table that has a field called [CreateDate]. Each [ItemNumber] might have multiple purchase orders with a different [CreateDate].

How can I run query to only show the records for the last created puchase order for each item?

Thanks,

cordeiro82

 

[Cordeiro82]

I wanted to add some more information

There are five fields in the table [ItemNumber], [CreateDate], [PONumber], [OpenQty], [Supplier]

In addition to finding the last purchase order created (Max[CreateDate]) for each item is it possible to only return the largest qty if there are more than two Purchase orders for any given item on the same date?

 

[dhookom]

Are you suggesting you don't have a single field primary key in your table? Can you add one? If so,

SELECT *
FROM tblNoNameProvided
WHERE ID = (SELECT TOP 1 ID FROM tblNoNameProvided N WHERE N.ItemNumber= tblNoNameProvided.ItemNumber ORDER BY CreateDate Desc, OpenQty Desc, ID Desc)

Duane MS Access MVP
Now help me support United Cerebral Palsy