PetGriffin PetGriffin - 2 years ago 67
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 >= #01/01/2016# AND <= #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 Source

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)
     ON b.custId = a.custId AND >= #01/01/2016# AND <= #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)
     (SELECT *
      FROM b
      WHERE >= #01/01/2016# AND <= #31/12/2016#
     ) as b
     ON b.custId = a.custId
GROUP BY a.custId;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download