Forcing NULL entries in ACCESS queries

Question

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!

*...using LEFT OUTER JOIN but this is not available in ACCESS* of course it's available. — forpas, Sep 10 '20 at 10:18
Harun24HR - please see OP forpas - There seems to be a difference of opinion eg https://stackoverflow.com/questions/49075392/access-sql-full-outer-join — PetGriffin, Sep 11 '20 at 06:47

score 1 · Accepted Answer · answered Sep 10 '20 at 10:49

1

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;

answered Sep 10 '20 at 10:49

Gordon Linoff

1,242,037
58
646
786

Hi this gives the expected result when I change the date range to 2-3/1/2020 and is in a more understandable form (for me at least) than the other suggestions, so thanks for your help – PetGriffin Sep 10 '20 at 14:59

Harun24hr · Answer 2 · 2020-09-10T11:08:08.830

Edit: Simplified query

SELECT Customers.CustID, Sum(Transactions.tValue) AS Total
FROM Customers LEFT JOIN Transactions ON Customers.CustID = Transactions.CustID
WHERE (Transactions.xDate BETWEEN #2020/01/03# AND #2020/01/04#) AND (Customers.CustType='Gold')
GROUP BY Customers.CustID;

You can sum total of union query result group by customer id. try below

I assume your CustID field is Number data type. If it is string data type then you need to change DLookup() function criteria part like DLookup("CustType","Customers","CustID='" & t.CustID & "'")

SELECT ut.CustID, Sum(ut.Total) AS Total
FROM (SELECT c.CustId, 0 as Total, c.CustType
    FROM Customers AS c
    GROUP BY c.CustId,c.CustType

    UNION

    SELECT t.CustId, SUM(t.tValue) AS Total, DLookup("CustType","Customers","CustID=" & t.CustID ) as CustType
    FROM Transactions AS t
    WHERE t.xDate BETWEEN #2020/01/03# AND #2020/01/04# GROUP BY t.CustId)  AS ut GROUP BY ut.CustID, ut.CustType
HAVING (((ut.CustType)='gold'));

Note: `Value` is a reserve word for `MS-Access`. So, I have used `tValue` for transactions table. — Harun24hr, Sep 10 '20 at 10:10
Hi your original query gives the answer I was expecting (2 rows when I change the date range to 2-3/1/2020 - see OP) but the edited version only returns 1 row but thanks for your suggestion — PetGriffin, Sep 10 '20 at 14:53

score 0 · Answer 3 · answered Sep 10 '20 at 10:14

Consider:

Query1:

SELECT CustId, SUM(Value) AS Total
FROM Transactions
WHERE xDate BETWEEN #2020/02/01# AND #2020/03/01#
GROUP BY CustId;

Query2:

SELECT Customers.CustID, Query1.Total
FROM Customers LEFT JOIN Query1 ON Customers.CustID = Query1.CustId
WHERE (((Customers.[CustType])="Gold"));

score 0 · Answer 4 · answered Sep 10 '20 at 10:32

0

LEFT OUTER JOIN is just called LEFT JOIN with Access-SQL, and works generally as expected.

See here for more information on its usage and limitations.

answered Sep 10 '20 at 10:32

cjb110

1,310
14
30

Forcing NULL entries in ACCESS queries

4 Answers4