Access 2002 SQL for joining three tables

I have been trying to get this to work for a while now. I have 3 tables. First table has the Sales for customers which include the CustomerID, DateOfSales (Which always has the first of the month). The second table has the CustomerName, CustomerID. The third table has which customers buy what product lines. They are stored by CustomerID, ProductID.

I want to get a list (from one SQL hopefully) that has ALL the customers that are listed as buying a certain ProductID AND the maxDate from the Sales. I can get all of them IF there are sales for that customer. How the heck do I get ALL customers that buy the certain ProductID AND the maxDate from Sales or NULL if there is no sales found?

SalesList |CustomerList|WhoBuysWhat
----------|------------|-----------
maxDate   |CustomerID  |CustomerID
CustomerID|            |ProductID=17

This is as close as I got. It gets all max dates but only if there have been sales. I want the CustomerID and a NULL for the maxDate if there were no sales recorded yet.

SELECT WhoBuysWhat.CustomerID, CustomerList.CustomerName,
Max(SalesList.MonthYear) AS MaxOfMonthYear FROM (CustomerList INNER
JOIN  SalesList ON CustomerList.CustomerID = SalesList.CustomerID) INNER
JOIN  WhoBuysWhat ON CustomerList.CustomerID = WhoBuysWhat.CustomerID
WHERE  (((SalesList.ProductID)=17)) GROUP BY WhoBuysWhat.CustomerID,
CustomerList.CustomerName;

Is it possible or do I need to use multiple SQL statements? I know we should get something newer than Access 2002 but that is what they have.

Answer

You want LEFT JOINs:

SELECT cl.CustomerID, cl.CustomerName,
       Max(sl.MonthYear) AS MaxOfMonthYear
FROM (CustomerList as cl LEFT JOIN
      (SELECT sl.*
       FROM SalesList sl
       WHERE sl.ProductID = 17
      ) as sl
      ON cl.CustomerID = sl.CustomerID 
     ) LEFT JOIN
     WhoBuysWhat wbw
     ON cl.CustomerID = wbw.CustomerID
GROUP BY cl.CustomerID, cl.CustomerName;

Advertisement

Answer