Query seem not to summarize the way I want

[mraccess101]

I have some issues with summarizing in a query

My intention is to summarize income and expenses over periods

This is how it should look like.

Periods_____Total income___Total expenses
01-2013_____234__________435
02-2013_____533__________132
03-2013_____345__________853
etc

I made the following query to get the result I wanted.

Unfortunately, the result seems to be multiplied instead of summarized.

 

[CJ_London]

you need to do a left join between your period table and your income and expenses tables on your period value.

Move you period table to the left of the window (not really necessary, but makes it easier to explain)

Click and hold on the period field in the period table and drag to the period field in the expenses table and release. This should have drawn a line between the two tables.

Next doubleclick on the line and select the 2nd option - Include all records from period and only those records from expenses where the joined fields are equal. Then click OK - this is a left join - the line should now have an arrow head pointing to the expenses table.

Now do the same again, from the period table, but this time to the Income table and again, make it a left join.

That should be all that is required.

 

[mraccess101]

You need two separate queries that you can union together. You cannot join Expenses and Income because the individual rows don't have anything to do with each other. That's why "duplication" occurs. You are actually ending up with a Cartesian Product.

Create qry1 to join Period to Expenses with a left join and then qry2 to join Period to Income with a left join. Then qry3 will union the other two queries. In the Union, you'll probably want to include a dummy column so you can retain the identity as an expense or income item.
Select "Expense" As AmtType, qry1.* From qry1
Union Select "Income" as AmtType, qry2.* From qry2;

It is for this reason that I always use a single table for income/expense items with a transaction type. If you keep them in separate tables, you end up needing union queries if you want them back together again

This solved my problem. Thank you again.