Union Query?

[turtle]

I need to get the results of two queries into one. I need all the data
from both queries (i guess this is a full join or outer join).
Can I do a union query.

Query1
Job Budget Function
1 10 Electrical Engineer
1 5 Mechanical Engineer
2 20 Elecrical Engineer

Query2
Job Actuals Function
1 8 Electrical Engineer
1 10 Mechanical Engineer
2 15 Mechanical Engineer

I want my results to be:
Job Budget Actuals Function
1 10 8 Electrical Engineer
1 5 10 Mechanical Engineer
2 20 0 Electrical Engineer
2 0 15 Mechanical Engineer

Thanks for the help!

[jimfortune@compumarc.com]

Re: Union Query?

turtle wrote:[color=blue]
> I need to get the results of two queries into one. I need all the[/color]
data[color=blue]
> from both queries (i guess this is a full join or outer join).
> Can I do a union query.[/color]

The easiest way I can think of to do this at the moment is
(superposition) :

q1:
SELECT Job, Budget, 0 As Actual, Function FROM Query1 UNION SELECT Job,
0 As Budget, Actual, Function From Query2;

q:
SELECT q1.Job, Sum(q1.Budget) AS Budget, Sum(q1.Actual) AS Actual,
q1.Function FROM q1 GROUP BY q1.Job, q1.Function;

Another method:

qryJobFunctionL ist:
SELECT Job, Function FROM Query1 UNION SELECT Job, Function FROM
Query2;

q:
SELECT Job, (SELECT Budget FROM Query1 WHERE Job = A.Job AND Function =
A.Function) AS Budget, (SELECT Actual FROM Query2 WHERE Job = A.Job And
Function = A.Function) AS Actual, Function FROM qryJobFunctionL ist As
A;

Another approach:

q:
SELECT A.Job, (SELECT Budget FROM Query1 WHERE ((Job = A.Job) AND
(Function = A.Function))) As Budget, (SELECT Actual FROM Query2 WHERE
((Job = A.Job) AND (Function = A.Function))) As Actual, A.Function FROM
Query1 AS A UNION SELECT B.Job, (SELECT Budget FROM Query1 WHERE ((Job
= B.Job) AND (Function = B.Function))) As Budget, (SELECT Actual FROM
Query2 WHERE ((Job = B.Job) AND (Function = B.Function))) As Actual,
B.Function FROM Query2 AS B;

Note: Your underlying tables should have a primary key. I don't
recommend the field name 'Function'. If I think of something really
elegant I'll post back.

James A. Fortune