help on writing query

[Superduper]

I have 3 tables: category,budget,transaction
The budget and transaction tables have categoryId and amount fields. They both have a one to many relationship from the category table. I need to return CategoryID,Budget.Amt,Transaction.Amt. Each row is a different CategoryId with the sum of the budget amts for that CategoryId and the sum of the transaction amounts for that CategoryId.

How do I write this query?

Thanks in advance

 

[vjrauch]

Break this into three queries. The first two, will each do one table and summarize the amounts by Category. Then the third query will combine the first two, by Category using the Category table as the "driver." It is important to use the LEFT JOIN from the Category table to each of the summary queries in order to get all the categories, even if there is no budget or no transactions for any Category. If you want to exclude all Categories with no budget and no transactions, do that in the Final Query.
I have included the SQL for each of the three queries:

1. Query named: "BudgetSums"
SELECT Budget.CategoryID, Sum(Budget.Amount) AS Amount FROM Budget GROUP BY Budget.CategoryID;

2. Query named: "TransactionSums"
SELECT Transaction.CategoryID, Sum(Transaction.Amount) AS Amount FROM [Transaction] GROUP BY Transaction.CategoryID;

3. Final query:
SELECT Category.CategoryID, Category.CategoryName, BudgetSums.Amount, TransactionSums.Amount FROM (Category LEFT JOIN TransactionSums ON Category.CategoryID = TransactionSums.CategoryID) LEFT JOIN BudgetSums ON Category.CategoryID = BudgetSums.CategoryID;