Sum error on select query

[Keith]

Hi

I have created a number of queries which calculate the volume sold per
account for specific products; I am now trying to collate these into one
summary table with a row for each account and the products as column headings.

When I add the first column the output is correct, however, as I add more
columns the figures in preceding columns multiply so that e.g. the figures in
the "CA" column end up 6x higher than they should be. I think this is
something to do with the repeated "Sum" function but I'm not sure how to
amend the query design to avoid this problem and produce the desired output.

I have attached the SQL code below - any help would be much appreciated.



SELECT [Seller info].AccountNumber, Sum([CA M1.SumOfCA]) AS [CA Sales],
Sum([Savings M1.Savings]) AS [Savings Sales], Sum(IIf([Fees M1.Product
Name]<>"Investments",0,[Fees M1.SumOfFees]))) AS [Investments], Sum(IIf([Fees
M1.Product Name]<>"Other",0,[Fees M1.SumOfFees]))) AS [Other]
FROM (([Seller info] INNER JOIN [CA M1] ON [Seller info].AccountNumber = [CA
M1].AccountNumber) INNER JOIN [Savings M1] ON [CA M1].AccountNumber =
[Savings M1].AccountNumber) INNER JOIN [Fees M1] ON [Savings
M1].AccountNumber = [Fees M1].AccountNumber
GROUP BY [Seller info].AccountNumber

 

[[MVP] S.Clark]

I'm going to blame all the IIF()'s, as that's a poor implementation of
normalized tables, IMHO.
--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html