Forcing NULL entries in ACCESS queries

I have two ACCESS tables as follows

CustId  CustType
1        Silver
2          Gold
3          Gold

xDate   CustId  Value
01/01/2020  2   100
01/01/2020  1   50
02/02/2020  2   100
03/01/2020  2   200
03/01/2020  1   50
04/01/2020  2   100
04/01/2020  3   100

I would like to know the total spent between two dates for Gold customers and set up the following query:

SELECT a.CustId, SUM(t.Value) AS Total
FROM Transactions AS t 
LEFT JOIN 
(
    SELECT CustId FROM Customers WHERE CustType = 'Gold'
) a 
ON a.CustId = t.CustId
WHERE t.xDate BETWEEN #2020/01/03# AND #2020/01/04# 
AND NOT a.CustId IS NULL
GROUP BY a.CustId;

This gives me the following:

2   300
3   100

If I change the query to BETWEEN #2020/01/02# AND #2020/01/03# I get this:

2   200

Everything is fine so far. However, for the second query I would like to have the total transaction value for Gold customers as zero (or NULL) even if they have not spent anything during the period ie

2   200
3     0

I believe that I can do this in other dB’s using LEFT OUTER JOIN but this is not available in ACCESS. This post How do I write a full outer join query in access suggests using UNION. My attempt was

SELECT c.CustId, 0 as Total
FROM Customers c
WHERE c.CustType = 'Gold'
UNION
SELECT t.CustId, SUM(t.Value) AS Total
FROM Transactions t
WHERE t.xDate BETWEEN #2020/01/03# AND #2020/01/04# 
GROUP BY t.CustId;

but this produced the following results:

CustId  Total
1   50
2   0
2   200
3   0

This result contains all types of customer and has a duplicate for Customer 2. I’m sure the answer is obvious if you know how but I just don’t know how! All suggestions gratefully received – thanks!

Answer

If you want all Gold customers, then Customers should be the first table in the LEFT JOIN. There is also no need for a subquery on customers. However, MS Access does want one on Transactions:

SELECT c.CustId, NZ(SUM(t.Value)) AS Total
FROM Customers as c LEFT JOIN
     (SELECT t.*
      FROM Transactions as t
      WHERE t.xDate BETWEEN #2020/01/03# AND #2020/01/04# 
     ) as t
     ON t.CustId = c.CustId
WHERE c.CustType = 'Gold'
GROUP BY c.CustId;

Advertisement

Answer