One to Many relationship - Match ID with the "many" table and return Max value of specific field

[mor1195]

Hello. I am very new to Access and I am having problems with my query.



I have one table with customer ID's and their names, and another table with the customer ID's and their orders including dates.
Many customers have placed more than one order, so in table 2, the ID is repeated.

-----> I want a query that shows every UNIQUE customer ID in one field, and the other field would be the LATEST date that the customer ordered.

Can someone help with this? It's pretty urgent.

Example:
TABLE 1
ID NAME
1 X
2 Y
3 Z

TABLE 2
ID ORDER ID DATE
1 56 13-JAN
1 87 17-JAN
2 80 15-JAN

I want a query that results like this:
ID LATEST ORDER DATE
1 87 17-JAN
2 80 15-JAN
3


THANKS!!!

[orange]

Your table structures or names(perhhaps) are a little confusing. Name and Date are reserved words in Access.
Consider:

TblCustomer 
CustomerID  pk
CustNAME  text

with data
1    John X Doe
2    Cody Y AckBear
3    Hesa Z Wynner

tblOrder
CustomerID  FK Long Integer
ORDER ID        PK
OrderDate  DATE 

with data 

1         56                13-JAN
1         87                17-JAN
2         80                15-JAN


SQL along these lines(untested):

SELECT  CustomerID
, Max( OrderDate) AS LatestOrderDate
FROM tblOrder 
GROUP BY  CustomerID;