Thread: Multiple Counting of Values Problem

1. Registered User
Join Date
Apr 2014
Location
Posts
2

Unanswered: Multiple Counting of Values Problem

Hello! I'm a novice Access user (just enough to be dangerous) and am having a query issue where certain values are multiplied by the number of entries in a joined table. The query pulls data from three tables, joined by common IDs. Here is what I'm trying to do:

1) Pull contact information from one table [Contact Information]
2) Match with information from a second table [Donation]
2a) Sum the Donation amount for each contact (multiple donations from a single individual)
3) Match with information from a third table [Investment]
3a) Sum the Investment amount for each contact (multiple investments from a single individual)

After adding the third table [Investment], the query started to multiple the Donation amount by the number of identical Investment results (two investments would double the Sum of Donations). When I try to Sum the Investment Amounts, it sums then multiplies the amount of Investments by the number of donations (e.g., if someone made 18 donations, regardless of amount, the Sum of Investment Amount is multiplied by 18!).

Here is the SQL:

SELECT DISTINCTROW [Contact Information].[Last Name], [Contact Information].[Mailing Preference], Sum(Donation.Amount) AS SumOfAmount, [Contact Information].[Donor Category], [Contact Information].[# of Founding Members], Investment.[Total Amount], [Contact Information].[Notes for Reports]
FROM Investment RIGHT JOIN ([Contact Information] RIGHT JOIN Donation ON [Contact Information].ID = Donation.[Donation ID]) ON Investment.[Investment ID] = [Contact Information].ID
GROUP BY [Contact Information].[Last Name], [Contact Information].[Mailing Preference], [Contact Information].[Donor Category], [Contact Information].[# of Founding Members], Investment.[Total Amount], [Contact Information].[Notes for Reports]
ORDER BY Sum(Donation.Amount) DESC , [Contact Information].[Donor Category];

The simple workaround was to create a new column in the Investment table, manually summing the Investment amounts and having the query pull data from the Total Amount column. This solves the problem for Contacts with a single investment, but for those with more than one investment (made at different times, so multiple records with that amount), the query is still multiplying the Donation amount by the number of investments (three Investments, Donations multiplied by 3).

Here is the SQL for the workaround:

SELECT DISTINCTROW [Contact Information].[Last Name], [Contact Information].[Mailing Preference], Sum(Donation.Amount) AS SumOfAmount, [Contact Information].[Donor Category], [Contact Information].[# of Founding Members], Investment.[Total Amount], [Contact Information].[Notes for Reports]
FROM Investment RIGHT JOIN ([Contact Information] RIGHT JOIN Donation ON [Contact Information].ID = Donation.[Donation ID]) ON Investment.[Investment ID] = [Contact Information].ID
GROUP BY [Contact Information].[Last Name], [Contact Information].[Mailing Preference], [Contact Information].[Donor Category], [Contact Information].[# of Founding Members], Investment.[Total Amount], [Contact Information].[Notes for Reports]
ORDER BY Sum(Donation.Amount) DESC , [Contact Information].[Donor Category];

Any help would be MUCH appreciated! I've spent hours trying to figure out the problem and keep running into walls. I found a few similar problems on the forum and tried proposed solutions but nothing seems to work. Thought putting the SQL up might help identify the problem and hopefully a solution!

2. Moderator
Join Date
Mar 2009
Posts
5,442
You must use subqueries. It works like a charm with SQL Server:
Code:
```SELECT *
FROM
(SELECT [ID]
,[Last Name]
,[Mailing Preference]
,[Donor Category]
,[# of Founding Members]
,[Notes for Reports]
FROM [Contact Information]
) AS c
LEFT JOIN
(SELECT [Donation ID],
Sum(Amount) AS SumOfAmount
FROM Donation
GROUP BY [Donation ID]
) AS d
ON c.ID = d.[Donation ID]
LEFT JOIN
(SELECT Investment.[Investment ID],
Sum(Investment.[Total Amount]) AS [SumOfTotal Amount]
FROM Investment
GROUP BY Investment.[Investment ID]
) AS i
ON c.ID = i.[Investment ID]
ORDER BY d.SumOfAmount DESC ,
c.[Donor Category];```
Unfortunately Access cannot process this kind of queries (when I tried I got an error message saying that there was a syntax error in the UNION query ???).
So, you must create 2 queries: one (Sum_Donation) for Donation.SumOfAmount and one (Sum_Investment) for Investment.[SumOfTotal Amount]), then create a query joining the table [Contact Information] with those queries:

1. Sum_Donation:
Code:
```SELECT Donation.[Donation ID],
Sum(Donation.Amount) AS SumOfAmount
FROM Donation
GROUP BY Donation.[Donation ID];```
2. Sum_Investment:
Code:
```SELECT Investment.[Investment ID],
Sum(Investment.[Total Amount]) AS [SumOfTotal Amount]
FROM Investment
GROUP BY Investment.[Investment ID];```
3. The final query:
Code:
```SELECT [Contact Information].[Last Name],
[Contact Information].[Mailing Preference],
Sum_Donation.SumOfAmount,
[Contact Information].[Donor Category],
[Contact Information].[# of Founding Members],
Sum_Investment.[SumOfTotal Amount],
[Contact Information].[Notes for Reports]
FROM ( [Contact Information] LEFT JOIN
Sum_Donation ON [Contact Information].ID = Sum_Donation.[Donation ID]
) LEFT JOIN
Sum_Investment ON [Contact Information].ID = Sum_Investment.[Investment ID]
ORDER BY Sum_Donation.SumOfAmount DESC ,
[Contact Information].[Donor Category];```

3. Registered User
Join Date
Apr 2014
Location
Posts
2

Problem SOLVED!

Thank you, Sinndho! The subqueries are the way to go. Have a great day!

4. Moderator
Join Date
Mar 2009
Posts
5,442