Combine Results of Two Queries with Same Fields Except Totals

[boraz]

I have to queries set up that have the same fields except for the totals. I am wanting to combine these queries so that supplies purchased and supplies used are side by side. For example, I the first record below, all the fields match, so Supplies Purchased = 10,000 and Supplies Used = 5,000 would be side by side. I have tried doing joins on the queries and I have tried doing a union on all the fields except the sums and then joining then left/right joining the queries on the Union results to no avail. It just led to a lot of duplication and high figures.

Query 1


Year-Month-Code1-Code2-Code3-Code4-Sum of Supplies Purchased
2003-01-123-123-123-123-10,000
2003-02-124-123-123-123-20,000


Query 2


Year-Month-Code1-Code2-Code3-Code4-Sum of Supplies Used
2003-01-123-123-123-123-5,000
2003-02-124-123-123-123-6,000

[June7]

A UNION certainly would not accomplish that, unless you then use the UNION as source for a CROSSTAB.

Really looks like a compound join on 6 fields.

[Gicu]

And I would make all those joins left outer joins just in case some supplies were "purchased" but not "used".

Cheers,
Vlad