subquery to return most recent date per customer.

[Sprtrmp01]

I haven't been able to find exactly what I am looking for in older forum posts (at least not anything that I can get to work), so I figured I would see if anyone can help me out.



I have 2 pretty simple tables. the first is a "customers" table that just has info related to customers: CustomerID_PK, CustomerName, etc
the second table holds a list of order dates (and other info, but for this question, I am only concerned with dates): OrderID_PK, CustomerID_FK (related to PK on "customers" table), OrderDate, etc. each customer has multiple orders, and this table really only holds a list of dates.

What I need to do is build a query that returns the customerID_PK and CustomerName (from "customers" table) and the MOST RECENT order date for EACH CUSTOMER. in other words, I need the most recent order date for CUST001, the most recent order date for CUST002... and so on. no other dates, just the most recent date for EACH customer.

so far, in query design view, I have
CustomerID_PK (sorted in ascending order)
CustomerName
OrderDate (sorted in descending order)

and I need to put a sub-query somewhere that would look at either the customerID_PK field or the OrderDate field and eliminate all the the first (most recent date)

not much experience with SQL or sub-queries, so any help is greatly appreciated.

Thanks in advance

[June7]

Use an aggregate query or if you want to return all fields, use TOP N nested query. Review http://allenbrowne.com/subquery-01.html#TopN

[sprtrmp]

when I use the top n query, I only get back the FIRST record. only one out of hundreds.
I am looking for the first date for each customer. I'm sure I'm doing something wrong, but not sure what.

[June7]

The referenced link is an example of your exact situation. What have you attempted? Post code such as SQL statement.

If you only want last order date, then use an aggregate query.

SELECT CustomerID_PK, CustomerName, Max(OrderDate) AS MaxDate FROM Orders INNER JOIN Customers on CustomerID_PK=CustomerID_FK GROUP BY Customer_ID, CustomerName;

Or build a report and use its Sorting & Grouping features with aggregate calcs. Report allows display of detail data as well as summary calcs.

[sprtrmp]

here is the basic SQL statement and I have not added anything for the subquery.

SELECT tblCust.CustID_PK, tblCust.LName, tblOrder.OrderDate
FROM tblCust INNER JOIN tblOrder ON tblCust.CustID_PK = tblOrder.CustID_FK
ORDER BY tblCust.CustID_PK, tblOrder.OrderDate DESC;

[June7]

That does not follow either example provided.

Try the aggregate in post 5.

[sprtrmp]

I have been poking around online, and I came across the following:

http://www.databasedev.co.uk/access_max_function.html

Was a tremendous help and it described pretty much the exact problem I was trying to solve. Works PERFECTLY!!