Need help with query join

[olxx]

I have two querys with same number of fields and same field names. [BudgetNumber] txt
[CostCategoryCode] number
[TotalSum] number

I need a query that calculates balance between these two querys. It should look like this, BudgetNumber (match with both querys), CostCategoryCode- all codes that are present in either of querys. TotalSum of both querys (two fields) where BudgetNumber and CostCategoryCode matches. If in one query the CostCategoryCode doesn´t exist, the field should be displayed anyway and the TotalSum field of that query will be just Null. Crazy explanation but i home someone can help me.

olxx

 

[olxx]

since my explanation sucks i posted an example here in excel so it will be more clear what i need.
thx

olxx

 

[namliam]

Simply make a new query where you add both query1 and query2 to the query.
Then click and drag "Costcategorycode" of query2 to "Costcategorycode" of query1.
Then double click the created line and select option 2

add the fields from both query1 and 2 then as an extra column simply calculate the total
Balance: NZ(Q1.TS,0) - NZ(Q2.TS ,0)

The NZ are there to handle any Null or Non-existing values

Save this query as Query3, Keep the query open and double click the line again...
Choose option 3, add in "is null" as criteria for Q1.CostCatCode
Save as Query4

Now make a new query and enter this as the SQL:
Select * from Q3
UNION ALL
Select * from Q4

That will give you your end result.