Join 2 Querys

[afslaughter]

I am very new to MS Access.
I am trying to join the results of 2 separate queries and I can't figure it out.

This is what I have so far. I made 2 queries and copied the SQL code from each and tried to make one Union Query.

Query1:
(SELECT [UMPR Dump].SSN, "N/A" AS Name, "Not in Data Base" AS Status
FROM [UMPR Dump] LEFT JOIN Data ON [UMPR Dump].SSN = Data.SSN
WHERE (((Data.SSN) Is Null)))

Query2:
(SELECT Data.SSN, [Last] & " " & [First] & " " & [Middle] AS Name, "Not in UMPR" AS Status
FROM Data LEFT JOIN [UMPR Dump] ON Data.SSN = [UMPR Dump].SSN
WHERE ((([UMPR Dump].SSN) Is Null) AND ((Data.Archived)=0)))


My horrible attempt at a Union Query:
SELECT Union.SSN, Union.Name, Union.Status
FROM
(SELECT [UMPR Dump].SSN, "N/A" AS Name, "Not in Data Base" AS Status
FROM [UMPR Dump] LEFT JOIN Data ON [UMPR Dump].SSN = Data.SSN
WHERE (((Data.SSN) Is Null)))
As Union INNER JOIN
(SELECT Data.SSN, [Last] & " " & [First] & " " & [Middle] AS Name, "Not in UMPR" AS Status
FROM Data LEFT JOIN [UMPR Dump] ON Data.SSN = [UMPR Dump].SSN
WHERE ((([UMPR Dump].SSN) Is Null) AND ((Data.Archived)=0)))



Thanks for any help I can get.

[orange]

What are you attempting to accomplish? What is the final result suppose to be?

What does the data represent?

[afslaughter]

Well I have 2 Tables "Data", "UMPR Dump"
The Data table has everything in my Dbase.
The UMPR is an outside report that I want to compare with the "Data Table".
I paste the SSN from the report to a table in my Dbase called "UMPR Dump".
The comparison I want to make is:

1: SSN on the UMPR that are not in Data
2: SSN in Data that are not on the UMPR

I was able to do this with 2 separate queries. They work great and produce a list with 3 columns SSN, Name, Status. Like This:

Query 1:
SSN - Name - Status
234 - N/A - Not in Data
267 - N/A - Not in Data

Query 2:
SSN - Name - Status
123 - Bob - Not in Umpr
122 - Rob - Not in Umpr
345 - John - Not in Umpr


I want to combine the 2 into 1 list like:

SSN - Name - Status
123 - Bob - Not in Umpr
234 - N/A - Not in Data
122 - Rob - Not in Umpr
345 - John - Not in Umpr
267 - N/A - Not in Data

[mejia.j88]

it should look something like this:

SELECT SSN, NAME, STATUS
FROM DATA
UNION ALL
SELECT SSN, NAME, STATUS
FROM UMPR;

that's assuming i have the correct fields from their appropriate table. let me know if this helps

[afslaughter]

That’s it, thanks so much. I knew it had to be something simple.
Here is my final code:


SELECT SSN, NAME, STATUS
FROM
(SELECT [UMPR Dump].SSN, "N/A" AS Name, "Not in Data Base" AS Status
FROM [UMPR Dump] LEFT JOIN Data ON [UMPR Dump].SSN = Data.SSN
WHERE (((Data.SSN) Is Null)))
UNION ALL
SELECT SSN, NAME, STATUS
FROM
(SELECT Data.SSN, [Last] & " " & [First] & " " & [Middle] AS Name, "Not in UMPR" AS Status
FROM Data LEFT JOIN [UMPR Dump] ON Data.SSN = [UMPR Dump].SSN
WHERE ((([UMPR Dump].SSN) Is Null) AND ((Data.Archived)=0)));