Date of last order!

[linanderson]

Hello,

I have a database which in one part records customer orders. What I would like to is identify customers who have not ordered for a given time period i.e. 30 days.

I know once I have built the query, I will require a parameter for the time period i.e.30 days.

I started by trying to record the last date ordered for a customer in a query, but am having problems with this. I had a query including information from my table for Orders (tblOrders). This had:

Order ID
Customer Name
Date Order Required

I do not need any more info apart from these fields.

The results would include all orders, so for some customers there would be numerous 'Order ID' & 'Date Order Required'. In the query, I did try to select the summary option, to group information by Customer - but the 'Date Order Required' was always the first date ordered. What I would like is only the last ones for all customers to be shown.

Can someone provide some guidance on how I can achieve my objective.

Thank you.

 

[Pat Hartman]

This needs to be done in two parts. you need to identify the last order and then you need to get its ID.

query1:
Select CustomerID, Max(orderDate) as MaxOrderDate
From tblOrders
Group by CustomerID;
query2:
Select q.CustomerID, q.MaxOrderDate, t.OrderID
From query1 as q Inner Join tblOrders as t On query1.MaxOrderDate = t.OrderDate;

query2 will return multiple rows if multiple orders were placed on the same date. If you only want to see the last one, you can change query2 to:
query2alt:
Select q.CustomerID, q.MaxOrderDate, Max(t.OrderID) As MaxOrderID
From query1 as q Inner Join tblOrders as t On query1.MaxOrderDate = t.OrderDate
Group by q.CustomerID, q.MaxOrderDate;