Sum/Count in query

[bgwool]

I have a query that is grouped by a field called "Survey Code". I need to find the sum of the values of a currency field ("Bill") for each entry in the "survey code" field. My query works perfectly if I have it set to "average", but the count field either displays the total number of records or another number thousands higher than it should be. The sum field displays the average value multiplied by the incorrect count field. I can't figure out how to filter the count/sum function so that it only includes appropriate values (like the average function seems to be doing). Any help would be appreciated. I'm kind of a newb at this.

Thanks


Nick

[June7]

Post the sql statement for analysis.

Why are you multiplying average by count?

[bgwool]

I'm not, hopefully. I would like to just use the sum field, but when that didn't work I tried other methods. Average seemed to work, so I thought i could multiply that by count to get a makeshift sum, but count was off as well.

[June7]

So why would sum and count be off? Need to see SQL and know more about the data.

[bgwool]

Substitute the avg for sum, otherwise identical. Sorry for the poor query-naming practice. I'm trying to break the habit.

SELECT DISTINCTROW [Survey Codes].Code, Avg([Calculate Bill By Study 1].Bill) AS AvgOfBill, Avg([Calculate Bill By Study 2].Bill2) AS AvgOfBill2, Avg([Calculate Bill By Study 3].Bill3) AS AvgOfBill3, Avg([Calculate Bill By Study 4].Bill4) AS AvgOfBill4, Avg([Calculate Bill By Study 5].Bill5) AS AvgOfBill5, Avg([Calculate Bill By Study 6].Bill6) AS AvgOfBill6, Avg([Calculate Bill By Study 7].Bill7) AS AvgOfBill7, Avg([Calculate Bill By Study 8].Bill8) AS AvgOfBill8, Sum([Calculate Bill By Study 1].Bill) AS SumOfBill, Count([Calculate Bill By Study 1].Bill) AS CountOfBill
FROM Employees LEFT JOIN TimeCardEnter ON Employees.EmployeeID = TimeCardEnter.EmployeeID, ((((((([Survey Codes] LEFT JOIN [Calculate Bill By Study 1] ON [Survey Codes].Code = [Calculate Bill By Study 1].Line1Code) LEFT JOIN [Calculate Bill By Study 2] ON [Survey Codes].Code = [Calculate Bill By Study 2].Line2Code) LEFT JOIN [Calculate Bill By Study 3] ON [Survey Codes].Code = [Calculate Bill By Study 3].Line3Code) LEFT JOIN [Calculate Bill By Study 5] ON [Survey Codes].Code = [Calculate Bill By Study 5].Line5Code) LEFT JOIN [Calculate Bill By Study 6] ON [Survey Codes].Code = [Calculate Bill By Study 6].Line6Code) LEFT JOIN [Calculate Bill By Study 7] ON [Survey Codes].Code = [Calculate Bill By Study 7].Line7Code) LEFT JOIN [Calculate Bill By Study 8] ON [Survey Codes].Code = [Calculate Bill By Study 8].Line8Code) LEFT JOIN [Calculate Bill By Study 4] ON [Survey Codes].Code = [Calculate Bill By Study 4].Line4Code
GROUP BY [Survey Codes].Code;

[June7]

Looks like 9 tables/queries involved. If those Study tables/queries each have many records associated with each record in SurveyCodes then yes, this could cause the sum and count to be other than what you expect.

The associated tables/queries must be summarized to one record for each key value in SurveyCode. Then use those summation queries in the multi-tables/queries query to do the additional aggregate calcs.

Or build a report and use subreports.

[bgwool]

Thanks for the help, I'll give it a shot.