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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top