Criteria to Select Most Recent Date

[Guest]

I have a created a query that lists the all of the purchase orders issued
since the beginning of time and the date each purchase order was issued by
part number. Thus, each part number shows multiple purchase orders. I am
really only interested in the most recent purchase order for each part
number. How do I set up criteria to select the most recent purchase order
issue date for each part number?

 

[Douglas J. Steele]

SELECT PartNumber, Max(OrderDate)
FROM MyTable
GROUP BY PartNumber

To do this through the visual query builder, drag the two columns into the
grid, and change the query into a Totals query (through the View menu, or by
clicking on the Totals icon on the button bar: the Totals icon is the
capital Sigma key).

Changing to a Totals query adds a new row, labelled Total, to the grid.
Leave it as Group By under PartNumber, but change it to Max under the
OrderDate.

 

[Ed Robichaud]

Create a Totals query based on your current query. "Group by" [purchase
order#] and select "max" for [order date]

-Ed

 

[Guest]

Base another query on your existing query and include a subquery in its WHERE
clause to identify the latest order date per part number, e.g.

SELECT Q1.*
FROM YourQuery As Q1
WHERE Q1.OrderDate =
(SELECT MAX(Q2.OrderDate)
FROM YourQuery AS Q2
WHERE Q2.PartNumber = Q1.PartNumber);