How I can get only last date order for all customers listed in report?

[haris]

Hello!
I have a next problem. I would like make report which contain list of CUSTOMERS with more ORDERS. I have next fields:
- CUSTOMER
- ORDER (AMOUNT)
- DATE.
I wish make a list for all CUSTOMERS but only last ORDER for each of them (specified in DATE field).

I will be very gratefull for help.
Thank you,
Haris

 

[DBL]

Create a query for your report using all the fields and all the necessary tables to make it up. Include the Date field from the Orders Table and in the sort order criteria make it "ascending". Use the Totals button on the toolbar and where it says Group By in the Date column select Max. That will show you the last order date for each customer that has an order.

HTH

[This message has been edited by DBL (edited 04-18-2002).]

 

[Pat Hartman]

You need to do this with two queries:

Query1:
Select CustomerId, Max(OrderDate) as MaxOrderDate
From YourTable
Group By CustomerId;

Query2:
Select T.CustomerId, Q.MaxOrderDate
from tblCustomer as T Left Join Query1 as Q ON T.CustomerId = Q.CustomerId;