Union Query question

[Guest]

Good morning,

I am a beginner at SQL and have been unable to create the union query to
meet my needs. I currently have two simple queries.

Qry 01
******
dwg number dwg issue design comment Customer date
internal date
grouped by, so no duplicate will appear


Qry 02
******
dwg number dwg issue review comment Customer date
internal date
grouped by, so no duplicate will appear


Note: the customer dates and internal dates from both queries do not come
from the same place although they have the same names..

I would like to know how to union these two queries to get the following
result

Qry 03
******
dwg number dwg issue design comment Customer date(design)
internal date(design) review comment Customer date(review)
internal date(review)

some dwg numbers and issue may appear in both queries and some only in one,
I need Qry 03 to combine them to include every record from both qry 01 and 02
and merge common dwg numbers and issues into one line.

Example:
Qry 01
******
dwg number dwg issue design comment Customer date
internal date
1000 A00 no much to say 2006/02/01 2006/03/01
5555 F00 all is good 2006/02/01 2006/02/01

Qry 02
******
dwg number dwg issue review comment Customer date
internal date
5555 F00 it won't work 2006/09/01 2006/12/25
6666 B00 this thing is great 2005/10/21 2006/01/07

Qry 03
******
dwg number dwg issue design comment Customer date(design)
internal date(design) review comment Customer date(review)
internal date(review)
1000 A00 no much to say 2006/02/01 2006/03/01
5555 F00 all is good 2006/02/01 2006/02/01 it won't work 2006/09/01
2006/12/25
6666 B00 this thing is great 2005/10/21 2006/01/07


Thank you for the help,

Daniel

 

[John Spencer]

I would try something like the following query.

SELECT A.[Dwg Number],
A.[Dwg Issue],
A.[Design Comment]
A.[Customer Date],
A.[Internal Date],
B.[Review Comment]
B.[Customer Date] as BCustomerDate
B.[Internal Date] as BInternalDate
FROM TableA as A LEFT JOIN TableB as B
ON A.[Dwg number] = B.[Dwg Number] And
A.[Dwg Issue] = B.[Dwg Issue]
UNION
SELECT A.[Dwg Number],
A.[Dwg Issue],
A.[Design Comment]
A.[Customer Date],
A.[Internal Date],
B.[Review Comment]
B.[Customer Date] as BCustomerDate
B.[Internal Date] as BInternalDate
FROM TableB as B LEFT JOIN TableA as A
ON B.[Dwg number] = A.[Dwg Number] And
B.[Dwg Issue] = A.[Dwg Issue]

The first query returns all records In A and the second returns all records
in B. Then the UNION should return distinct records from all the records
returned in A and B