CODEWITHSUNDEEP
databasems-access
Zmur
Zmur

Reputation: 334

MS Access finding customers that have previous year orders and don't have this year orders

This problem is taking too long to solve on my own, so I hope someone can help me.

I have a simple MS Access database: two tables (Customers and Orders) and I need to get those customers, that have placed an order last year and haven't placed one this year. This database is used to send CD's with information to clients. They subscribe once a year and then they receive a CD each month. What I want is to find those, who have forgotten to subscribe this year.

The table "Customers" is very simple: has fields like "Customer name","address", "e-mail" and so on. It is linked with table "Orders", where all the orders for each customers are stored. It is also pretty basic, has fields like: "Order Status", "Order type","Order year" (here, the year for which the order has been made is stored), "Quantity" and comments.

I tried to create 2 queries, each would gather orders for this and for previous year (by using that "Order year" field), but I cannot figure out what to do next, how to pull out the customers that have order in say 2015 and don't have one in 2016?

Upvotes: 0

Views: 197

Answers (3)

kostas
kostas

Reputation: 461

     SELECT a.*,b.orderyear
     FROM (select t1.* from customers t1 inner join orders o on t1.ID=o.CustomerID where o.orderyear=2015) a
     LEFT join (select * from orders where orderyear=2016) b 
     ON a.ID=b.CustomerID 
     WHERE b.orderyear is null

pulls out the customers that have order in say 2015 and don't have one in 2016

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34180

If you're doing it in Access, I would do it in stages:-

(1) set up a query (Query1) joining customers to Orders and selecting keyID where subscriptYear=2015. Might want to set Unique Values or use GROUP BY to avoid duplicates.

(2) Set up a similar query (Query2) for subscriptYear=2016.

(3) Set up a third query with Query1 left joined to Query 2 on keyID and specify Query2.keyID=NULL

Upvotes: 0

cf_en
cf_en

Reputation: 1661

Something like this should work:

SELECT C.* FROM Customers C
  INNER JOIN Orders O ON C.Id = O.CustomerID AND O.OrderYear = 2015
  LEFT JOIN Orders O2 ON C.Id = O2.CustomerID AND O.OrderYear = 2016
WHERE O2.Id IS NULL

It's going to get the customers that have an order record from 2015, and then it's going to set up an outer join on orders for 2016, which will return NULL for the 2016 orders fields if there is no match. The WHERE clause is then filtering out everything that does have a matching 2016 order.

Upvotes: 0

Related Questions