I think I need a union query but not sure

Randy

Registered User.
Local time
Today, 17:09
Joined
Aug 2, 2002
Messages
94
table 1 model, arrivals, shipments
table 2 models, arrivals, shipments

some models are in table 1 but not in table 2, and some models are in table2 but not table 1

I need this output
model(either tbl1 or 2), tbl1arrival, tbl1shipments, tbl2arrival, tbl2shipments.

i.e. I need one line for each model and the corresponding arrival and shipments, regardless of the table the value is stored in.

I wrote this
SELECT model, arrivals, shipments
From tbl1
UNION
SELECT model,ARRIVAL, shipments
FROM tbl2

this gives me
tbl1model,tbl1arrival, tbl1shipments
tbl2model, tbl2arrival, tbl2shipments

etc

any help is appreciated. thanks
 
For this you need a Union query but not in the way you have imagined. You need a set of 3 queries basically (you can use subqueries directly but it is harder to describe to you.

First we create a Union query with JUST the Models.

Select Model From tbl1
Union
Select Model From tbl2


then we create a second query

Select model, arrivals, shipments
From tbl1

and a third

Select model, arrivals, shipments
From tbl2

and then a fourth where we put them all together and from the first one you link model to the model of the second (but using an OUTER JOIN) and then a link from the first one to the third query model using an OUTER JOIN as well. The arrow from the OUTER JOIN needs to be pointing from the first query (with just the models) to the second and third queries.
 
from a previous post I tried this
SELECT [tbl1].model, [tbl1].[Sum of Inbound], [tbl1].[Sum of Outbound],
Nz([tbl2].arrival,0) AS IFSARRIVAL, Nz([tbl2].OESHIP,0) AS IFSOESHIP
From tbl1
LEFT JOIN [tbl2] ON [tbl1].model = [tbl2].model

UNION SELECT [tbl2].model, Nz([tbl1].[sum of inbound], 0), Nz([tbl1].[sum of outbound],0),
[tbl2].arrival, [tbl2].oeship
from [tbl1]
RIGHT JOIN [tbl2] ON [tbl1].model = [tbl2].model;

but as shown below it brought in two lines (see 19PFL3505D/F7) when the model was in both tables. I will try the suggestion above.

model Sum of Inbound Sum of Outbound IFSARRIVAL IFSOESHIP
19HFL3340T/F7 0 0
19PFL3505D/F7 0 3
19PFL3505D/F7 0 0 3
19PFL3505D/F7B 0 0
19PFL3505D/F7E 0 0
19PFL3505D/F7S 0 0
19PFL4505D/F7 0 1
19PFL4505D/F7 0 0 1
19PFL4505D/F7B 1 0 7
19PFL4505D/F7B 0 1 7
19PFL4505D/F7E 0 0
19PFL4505D/F7E 0 0
19PFL4505D/F7S 0 0
19PFL4506D/F7E 0 0
22AV2084/F7 492 0 416
22AV2084/F7 0 492 416
22AV8573/F7 134 0 129
22AV8573/F7 0 134 129
 
thanks it worked perfect. exactly what I needed. And I understand what you did. In escence the first union query created a "unique" set of models. Then the 4th query combined the three queries, using that unique set. I can use this again and again thanks.
 

Users who are viewing this thread

Back
Top Bottom