This should be simple (marge 2 queries)

[DAW]

I have one query that returns a list of all debits in a given year/month/cost element: e.g.

Year Month CE Debit
2007 6 111 100.00
2007 6 153 150.00
2007 6 157
2007 6 294 75.00

and another that returns a list of cost element budgets: e.g.

Year Month CE Budget
2007 6 111 500.00
2007 6 120 75.00
2007 6 153 200.00
2007 6 157 120.00

So you can see there could be budgets with no debits against them, and there could be debits with no budget (either could be zero or null). What i want is to merge the two so that I return ALL possibilities: e.g.

Year Month CE Debits Budget
2007 6 111 100.00 500.00
2007 6 120 75.00
2007 6 153 150.00 200.00
2007 6 157 120.00
2007 6 294 75.00

For the lif of me I can't get this to work - I can get all budgets, or all debits, but not a merge of both. Can someone please suggest a solution or point me in the right direction?

 

[Jon K]

You can first combine the two queries in a Union Query and then build a Totals Query based on the Union Query.

Assuming your queries are named "Query1" and "Query2",
qryUnion:-
SELECT [Year], [Month], [CE], [Debit], Null as [Budget]
FROM [Query1]
UNION ALL
SELECT [Year], [Month], [CE], Null, [Budget]
FROM [Query2];

qryTotals:-
SELECT [Year], [Month], [CE], Sum(qryUnion.Debit) AS [Debit], Sum(qryUnion.Budget) AS [Budget]
FROM [qryUnion]
GROUP BY [Year], [Month], [CE];

Run qryTotals.
.

 

[DAW]

Jon,
A million thanks - you pointed me in the right direction and I can feel my sanity return! Actually the SQL didn't like the square brackets, and using union I was able to directly query the tables. Simple when you know how but I needed to know what to look for.

SELECT Year, Month, Dept, CESE, Budget, Null as Debits from tbl_budgets ;
UNION ALL
SELECT Year, Month, Dept, CESE, Null, Debits from tbl_Data;