Customers Based On Last Order Date

[MaxQTime]

Hi Everyone, I was looking to pull a list of Dealers based on when their last sale was. I have the DB set up with multiple tables, the 2 tables that are relevant to this is the Dealer table and the Orders Table. Each order is linked to a specific dealer and dealers can have 0, 1 or many orders. Each order has a date ordered field with it.

I need to be able to pull a list of dealers who have ordered from us within the last year and the last 6 months. Is there a way to do this with a single query or is it going to get more complicated?

I'm having a bit of trouble trying to wrap my head around how to do this.

[CJ_London]

you just need one query. Join the two tables on dealerID and use criteria on the order date. Not sure what your criteria needs to be since any orders in the last 6 months will also be within the last year - perhaps provide some examples of what you mean. SQL might look something like

SELECT DISTINCT tblDealers.*
FROM tblDealers INNER JOIN tblOrders ON tblDealers.DealerPK=tblOrders.DealerFK
WHERE OrderDate>Dateadd("yyyy",-1,Date())

[ArviLaanemets]

On fly:

SELECT DISTINCT d.*, lo.LastOrder
FROM
tblDealers d
INNER JOIN
(SELECT o.DealerFK, MAX(o.OrderDate) AS LastOrder FROM tblOrders o GROUP BY o.DealerFK WHERE o.OrderDate >= DateAdd(mm, -6, DateSerial(Year(Date),Month(Date),1)))) lo ON lo.DealerFK = d.DealerPK

Of course you may have a saved query instead of subquery.

[MaxQTime]

Thank you, the SELECT DISTINCT and MAX was exactly what I was looking for.

[orange]

If this is solved, please use Thread Tools and mark it so.