Results 1 to 10 of 10
  1. #1
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94

    I can get average etc. to show on TOTALS row but not SUM.

    Hi All, driving me crazy. I have a query based on a table where I want to show totals in the datasheet view, based on a range of dates. I've done it before but on a newer version of the database, I can get average, maximum etc. to work, but not Sum. I've looked back at the previous version which still works properly and I can't see a difference.
    The query has text boxes to ask for the date start/end range, there are records in that range and it finds them. The column that I want totalled is called 'Facttotal' so no naming problems, and the data type is Euro.
    As last year's version works and this year's is a copy but without the old data, just the new, I can't figure out what is wrong.
    Thanks for any help.


    I have to start a new table evry year for local tax authority reasons.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What has tax authority to do with how you store data? Why not just 1 table and filter records by year?

    No idea why Sum not available. Would have to analyze db.
    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
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    I did that so that members wouldn't tell me to only have one table. I'll have to spend some time including only the necessary data to zip.
    Thanks for your reply.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't understand first sentence in that answer. A member (me) has told you to have only one table.
    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
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I can get average, maximum etc. to work, but not Sum.
    What do you mean when you say you can't get sum to work? Are you getting an error message, or are you getting erroneous results or none at all? Are you able to put the required Sum(Facttotal) expression into the query without error?

    Answers to these will give us a better idea as to where the problem might be.

  6. #6
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Hello John, sorry for the delay in replying, I have work. I'm sorry about the 'doesn't work', I do know better !
    Your question about the query got me thinking so I added a group by and the query now works. It is a while since I've used Access and I was under the (wrong) impression that totals could be added to a query just as onto a table. So many thanks for that, much appreciated.
    Trevor.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by tbjmobile@gmail.com View Post
    I was under the (wrong) impression that totals could be added to a query just as onto a table.
    You were correct in the first place. The totals row can be used in tables, queries and in datasheet forms
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was under the (wrong) impression that totals could be added to a query
    I think you are under the wrong impression about being under the wrong impression.
    Totals can be added to a select query...
    Click Totals on ribbon and choose an aggregate function from the resulting drop down in the totals row.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Build a report based on raw data. Use report Sorting & Grouping features with aggregate calcs in textboxes. This will allow display of raw data as well summary calcs.
    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.

  10. #10
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Thank you all for your help. What happened was, I added a Totals line with the total button from the ribbon, to the select query. Then saved it and ran it. Then when I chose the Sum from the drop down total box for the columns I wanted totalled, the datasheet view produced what I wanted and the totals are calculated accurately.
    Again, thanks for the help.

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

Similar Threads

  1. find average of values in a totals query
    By tagteam in forum Access
    Replies: 4
    Last Post: 11-07-2017, 03:43 PM
  2. Replies: 1
    Last Post: 03-06-2016, 10:35 PM
  3. Replies: 2
    Last Post: 11-09-2015, 01:24 PM
  4. Replies: 2
    Last Post: 01-08-2015, 12:41 AM
  5. Query to show AVERAGE of all values in a field
    By taimysho0 in forum Queries
    Replies: 9
    Last Post: 01-09-2012, 11:18 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