Why do I need to sum two tables before joining them and linking by Group ID to get the Aggregate Total

[Galapagos15]

I have two tables which contain a Receipt total for ID 0001.

Table 1 has a "Receipt Total" row of data for this ID of $10 and then another row with a total of $11.

Table 2 has a "Premium Receipt Total" row of data for ID 0001 for $5 and another row with a total of $6.

In my mind I should be able to run one Select query and match these two tables by the ID# and then pull in the ID (group it) and pull in the "Receipt Total of Table 1" (sum it) and pull in the "Premium Receipt Total of Table 2" (sum it) and should get the following results.

ID 0001
Receipt Total= $21 (instead I get $42)
Premium Receipt Total-$11 (instead I get $22)

It seems to me I shouldn't have to sum each of these tables in a separate query and then create a 3rd query that pulls in these two summed tabled queries to get the correct totals.

Thanks in advance!

 

[Joe4]

Because you are creating a many-to-many relationship by joining these two tables together like this (your Join field has duplicate values on both sides).
That is not the way you want to go about this. You want a single long list of all your records. You can accomplish this with a UNION Query like this:

SELECT Table1.ID, Table1.[Receipt Total],0 as [Premium Receipt Total]
FROM Table1
UNION
SELECT Table2.ID,0 as [Receipt Total],Table2.[Premium Receipt Total]
FROM Table2;

Note that we place a zero in for the place holder, since each Total field only exists in one table, and we want to return the both of them (we want our final query to have three fields, not two).
See the following link for more on UNION Queries. You need to to create these in SQL View (cannot do them in the Query Builder Tool).
https://support.office.com/en-us/ar...on-query-3856f16c-0a22-43f2-8c23-29ec44acbc05

Then, we can use an Aggregate Query on this Union Query to get the totals you want. You can either do this is a separate query based on the Union Query we just wrote, or it can be done in a single query simply by nesting the Union Query in a Subquery like this:

SELECT ID, Sum([Receipt Total]) as SumOfReceiptTotal, Sum([Premium Receipt Total]) as SumOfPremiumReceiptTotal
FROM
(SELECT Table1.ID, Table1.[Receipt Total],0 as [Premium Receipt Total]
FROM Table1
UNION
SELECT Table2.ID,0 as [Receipt Total],Table2.[Premium Receipt Total]
FROM Table2)
GROUP BY ID;

 

[Galapagos15]

Great, thank you for the tips. It's been about a year since I worked in Access so I'm a little rusty!

 

[Joe4]

You are welcome.

Things always get a little trickier and are more work than they have to be when you are dealing with a data structure that is not normalized.

The other way to go about it is if you had a separate table of all your IDs, and they were unique (exactly one record per ID).
Then you could create a query with left joins from this ID table to your other two tables, and do an Aggregate Query on that.