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?
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?