Union Query not returning expected results (1 Viewer)

[Randy]

I have the following query. All I am doing is comparing the count of an individuals system access on 10/4 vrs 11/16. So XYZ had 4 "inheritances" assigned on 10/4 and 4 on 11/16. I was expecting something like this
XYZ 4 4
ABC 5 5
DEF 3 0
GHI 0 5

DEF left the company and was removed from the system
GHI was hired after 10/4


what I am getting is this
XYZ 4
ABC 5
DEF 3


XYZ 4
ABC 5
GHI 3

In this example XYZ and ABC where employees at both dates. DEF was an employee on 10/4 but not on 11/22

and GHI was hired after 10/4


SELECT tbldata.userid AS UserId, Count(tbldata.inheritance) AS Count10_4_2016, 0 AS Count11_22_2016
FROM tbldata
WHERE (((tbldata.effectivedate)=#10/4/2016#))
GROUP BY tbldata.userid;

UNION ALL

SELECT tbldata.userid AS UserId, 0 AS Count10_4_2016, Count(tbldata.inheritance) AS Count11_22_2016
FROM tbldata
WHERE (((tbldata.effectivedate)=#11/22/2016#))
GROUP BY tbldata.userid;

what am I missing?

 

[plog]

Your missing what a UNION query is. It just adds multiple sets of rows together. Query1 returns 3 rows, Query2 returns 3 rows; the UNION of Query1 and Query2 will return 6 rows.

What you want is a FULL OUTER JOIN query (http://www.w3schools.com/Sql/sql_join_full.asp). However, Access doesn't support them. So you need to hack one together--spoiler alert--it actually involves a UNION query.

So to achieve what you want, search this forum or google this:

Full Outer JOIN In Microsoft Access

 

[Randy]

thanks doing searches on Full Outer Join in Access. I understand the concept. I will play with that.