Results 1 to 7 of 7
  1. #1
    bgwool is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2014
    Posts
    29

    Sum/Count in query

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Post the sql statement for analysis.

    Why are you multiplying average by count?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    bgwool is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2014
    Posts
    29
    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.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    So why would sum and count be off? Need to see SQL and know more about the data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    bgwool is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2014
    Posts
    29
    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;

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    bgwool is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2014
    Posts
    29
    Thanks for the help, I'll give it a shot.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-15-2014, 01:59 PM
  2. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  3. Replies: 7
    Last Post: 05-02-2012, 08:04 AM
  4. Max of Count Query
    By dssrun in forum Queries
    Replies: 4
    Last Post: 03-05-2012, 12:53 PM
  5. Query with max value and max count
    By Fabdav in forum Queries
    Replies: 1
    Last Post: 10-13-2011, 07:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums