Need net results from (2) queries

[Marc S]

I have one query that displays a job number and corresponding expenses for
the previous month. I have another query that displays a job number with
corresponding labor hours for the previous month. I would like a result that
provides all job numbers that had either labor hours OR expenses the previous
month. Some job numbers will be in both query results and some will be in
one or the other.

Query 1: JobsWithHoursLastMonth
Results:
Job 1 25 hrs
Job 2 20 hrs
Job 5 10 hrs
Job 6 15 hrs

Query 2: JobsWithExpensesLastMonth
Results:
Job 1 $250
Job 3 $100
Job 4 $150
Job 6 $200

What I need is:
Query 3: JobsWithExpensesOrHoursLastMonth
Job 1 25 hrs $250
Job 2 20 hrs $0
Job 3 0 hrs $100
Job 4 0 hrs $150
Job 5 10 hrs $0
Job 6 15 hrs $200

Thanks in advance for any help

Marc

 

[Van T. Dinh]

You need to simulate a "full outer join" in Access with a Union Query like:

****
SELECT Q1.JobNo, Q1.JobHrs, CCur(Nz(Q2.JobExpenses)) AS JobExp
FROM Query1 AS Q1 LEFT JOIN Query2 AS Q2
ON Q1.JobNo = Q2.JobNo

UNION

SELECT Q2A.JobNo, Nz(Q1A.JobHrs, 0), Q2A.JobExpenses
FROM Query1 AS Q1A RIGHT JOIN Query2 AS Q2A
ON Q1A.JobNo = Q2A.JobNo
****

 

[Marc S]

Vin,

Thanks for the reply - I will give it a try.

In the second part of the UNION statement, does the "A" you included after
Q1 and Q2 mean anything? I assume I simply use the query name in place of
both Q1, Q2, Q1A and Q2A.

Thanks again,

Marc

 

[Van T. Dinh]

Mirc (g)

I use aliases a lot to shorten the SQL Strings on newsgroup posting. In
this case, I just use the "A" to indicate that this is _another_ copy of the
Query for clarity. You can even use the same Q1 & Q2 in the second part of
the Union since the keyword "UNION" indicate a different datasource so JET
won't get confused if you use the same aliases as the first part.