PetGriffin PetGriffin - 6 months ago 6
SQL Question

SQL JOINs not working as expected

I have two MS ACCESS tables a and b like this

a: custId, custName
b: date, transId, custId, spend

I would like to know how each customer spent over a time period (eg Jan 2016), regardless of whether they actually made a transaction that month. The SQL query I am using is

SELECT a.custId, SUM(b.spend) FROM b
LEFT JOIN a ON b.custId = a.custId
WHERE b.date >= #01/01/2016# AND b.date <= #31/12/2016#
GROUP BY a.custId


I was expecting to get a record set with all the customer ID's from table a with either the total spend (if they were active) or a NULL (if they were not). But ACCESS only returns the spend for customers whose ID appears in Table b in the month of Jan and ignores the rest. I am fairly sure I must have done something wrong with the query but don't know what is is. All suggestions gratefully received. Thanks!

Answer

This is hard to do in MS Access. First, note that you would seem to want the "a" table first, not second, so you keep all customers.

The WHERE turns the LEFT JOIN into an INNER JOIN. The normal solution in SQL would move the condition to the ON clause:

SELECT a.custId, SUM(b.spend)
FROM a LEFT JOIN
     b
     ON b.custId = a.custId AND
        b.date >= #01/01/2016# AND b.date <= #31/12/2016#
GROUP BY a.custId;

However, I don't think this works in MS Access. Instead, use a subquery:

SELECT a.custId, SUM(b.spend)
FROM a LEFT JOIN
     (SELECT *
      FROM b
      WHERE b.date >= #01/01/2016# AND b.date <= #31/12/2016#
     ) as b
     ON b.custId = a.custId
GROUP BY a.custId;
Comments