Need help with query join (1 Viewer)

olxx

Registered User.
Local time
Today, 08:41
Joined
Oct 2, 2009
Messages
36
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

Registered User.
Local time
Today, 08:41
Joined
Oct 2, 2009
Messages
36
since my explanation sucks i posted an example here in excel so it will be more clear what i need.
thx

olxx
 

Attachments

  • sample.xls
    20 KB · Views: 59

namliam

The Mailman - AWF VIP
Local time
Today, 17:41
Joined
Aug 11, 2003
Messages
11,691
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.
 

Users who are viewing this thread

Top Bottom