Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014
    Location
    Colorado
    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. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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];
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Location
    Colorado
    Posts
    2

    Talking Problem SOLVED!

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

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •