Display only matches with most recent date

[Andy Lavery]

I have two tables: Customers and Orders. Among other
fields, they contain the following:

Customers: Customer ID #, Purchaser
Orders: Customer ID#, Date, Order Notes

The customer ID# field links the two tables. I would like
a query to display for each Purchaser, the date and notes
of the MOST RECENT order only. How can I do this? Thanks.

 

[Ashby]

Create a query joining the two tables on the Customer ID #
field.

On the query menubar - Click on View and select "Totals"

A new row will appear in the query grid called "Total"

Set all of the totals for each field to "Group" except the
Date Field which should be "MAX".

This should work.

 

[Andy Lavery]

Sadly, this does not work. It still shows every order for
each Customer. Any other suggestions?

Here is the SQL statement. tblProjectData contains the
customer information. DSR is the Customer Identifier and
key field. LOB is the Customer Name. tblNoteData contains
order information. Thanks for your help!

SELECT tblProjectData.DSR, tblProjectData.LOB, Max
(tblNoteData.Date) AS MaxOfDate, tblNoteData.Description
FROM tblProjectData INNER JOIN tblNoteData ON
tblProjectData.DSR = tblNoteData.DSR
GROUP BY tblProjectData.DSR, tblProjectData.LOB,
tblNoteData.Description;

 

[John Verhagen]

Try:
SELECT tblProjectData.LOB, tblNoteData.Date, tblNoteData.Description
FROM tblProjectData INNER JOIN tblNoteData ON tblProjectData.DSR =
tblNoteData.DSR
WHERE (((tblNoteData.Date)=(select max(date) from tblNoteData as ND where
ND.DSR=tblProjectData.DSR)));