SQL question: Returning most recent order record (must include record ID)

J

J Miro

Suppose you have a table named Orders with some fields including: PK,
CustName, OrderDate. PK is an autonumber field, so although a customer may
have multiple records, each record will have a unique value in the PK field.
How would you write the SQL statement to return the most recent order record
for all the customers?



PK CustName OrderDate

1 John Doe 5/5/05

2 Bob Smith 5/7/05

3 John Doe 5/10/05

4 Bob Smith 5/11/05



Using the sample above, records 3 and 4 would be returned:

3 John Doe 5/10/05

4 Bob Smith 5/11/05


Thanks in advance. J. M.
 
A

Allen Browne

1. Create a query that uses this table.

2. In query design view, depress the Total button on the toolbar (upper
sigma icon.) Access adds a Total row to the grid.

3. Drag the CustName into the grid.
Accept Group By in the Total row under this field.

4. Drag the OrderDate field into the grid.
In the Total row, choose Max.

The query returns each customer name, and their most recent order date.

If you want the PK as well, there are 4 techniques discussed in this
article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

BTW, hopefully the CustName field is just an example. You really need a
separate table of customers, such as you see in the Northwind sample
database.
 
Top