which customers have no transactions in the last year

[geoffsx]

I have customer table and a linked transaction table which shows what the
customer bought, the date the item was purchased and its value.
I want to produce a list of customers who have not had a transaction within
the last 12 months.
Hopefully someone can help

 

[John W. Vinson]

I have customer table and a linked transaction table which shows what the
customer bought, the date the item was purchased and its value.
I want to produce a list of customers who have not had a transaction within
the last 12 months.
Hopefully someone can help

A NOT EXISTS subquery can do this:

SELECT <whatever fields you want>
FROM Customers
WHERE NOT EXISTS
(SELECT CustomerID FROM Transactions WHERE Transactions.CustomerID =
Customers.CustomerID AND TransactionDate >= DateAdd("m", -12, Date()))

 

[Tom van Stiphout]

On Thu, 19 Feb 2009 16:46:01 -0800, geoffsx

(I'm using Northwind sample db and A2007)
I approach this in two steps. Query1 gets the most recent orderdate
for each customer:
SELECT Max(Orders.[Order Date]) AS [MaxOfOrder Date], Orders.[Customer
ID]
FROM Orders
GROUP BY Orders.[Customer ID];

Then I create a second query where I join the Customers table with
Query1:
SELECT Customers.*, Query1.[MaxOfOrder Date]
FROM Customers LEFT JOIN Query1 ON Customers.ID = Query1.[Customer ID]
WHERE (((Query1.[MaxOfOrder Date])<=DateAdd("m",-12,Date())));

Note that I am using a Left Outer Join to pick up all customers,
whether they have orders or not. If you only want customers that have
at least one order, use an inner join.

-Tom.
Microsoft Access MVP

 

[Allen Browne]

Tom and John both suggested using a subquery.

If that's a new concept, here's a bit more information:
http://allenbrowne.com/subquery-01.html#NotThere