Queries Help

[jaggar]

I have two query. Each query records has an ID_Number, an ID_code and money amount (Sum UP). How do you compare records from both query. I am writing a report for example, I must compare the two query. Match and duplicate
won't do.

Query one Query two
Records 1 A 200 1 A 300
2 B 50 3 B 100

Report - Q 1 Q 2
1 1 (200-300) 100 diff
2 0 No Q2
0 3 No Q1

 

[dhookom]

I would first create a union query
--quniOneTwo---
SELECT ID_Number
FROM QueryOne
UNION
SELECT ID_Number
FROM QueryTwo;

Then place all three queries in a single query with Joins that select all records from the union query. You can then subtract the amount from one query from the amount in the other query. You will have to use the NZ([Amount],0) funtion to solve the issue where the amount from one or the other query might be null.

Select

Query one Query two
Records 1 A 200 1 A 300
2 B 50 3 B 100

Report - Q 1 Q 2
1 1 (200-300) 100 diff
2 0 No Q2
0 3 No Q1

Duane
MS Access MVP
Find out how to get great answers faq219-2884.