Help with a Union Query

[slan110]

Hello,

I am pretty new to Union Queries and am looking for some help. I have 2 pretty simple queries that bring back the Count of Distinct SSNs from 2 different places by plan #. The goal of my union query is to combine the counts, so for instance if plan 1 has 3 SSNS in query 1 and 2 SSNS in query 2, the union query would show a Count 5 SSNS. Can this be done? This is my SQL: 

SELECT Subquery_AdvDetailPPNTSContrib1.[Plan No], Count(Subquery_AdvDetailPPNTSContrib1.[Soc Sec #]) AS [CountOfSoc Sec #]
FROM Subquery_AdvDetailPPNTSContrib1
GROUP BY Subquery_AdvDetailPPNTSContrib1.[Plan No]
UNION
SELECT Subquery_AdvDetailPPNTSContribFlatDollar1.[Plan No], Count(Subquery_AdvDetailPPNTSContribFlatDollar1.[Soc Sec #]) AS [CountOfSoc Sec #]
FROM Subquery_AdvDetailPPNTSContribFlatDollar1
GROUP BY Subquery_AdvDetailPPNTSContribFlatDollar1.[Plan No]

The two column names in my two queries are the same: Plan No and Soc Sec #. 

The output is giving me two rows for plans with SSNS on both queries- is there a way to add them together to give only 1 row per plan? 

[#2 · Accepted solution]

can you first Create a Union query without Totaling:

SELECT Subquery_AdvDetailPPNTSContrib1.[Plan No], Subquery_AdvDetailPPNTSContrib1.[Soc Sec #]
FROM Subquery_AdvDetailPPNTSContrib1
UNION ALL
SELECT Subquery_AdvDetailPPNTSContribFlatDollar1.[Plan No], Subquery_AdvDetailPPNTSContribFlatDollar1.[Soc Sec #]
FROM Subquery_AdvDetailPPNTSContribFlatDollar1


Then create your Total Query from the above query.