Results 1 to 10 of 10
  1. #1
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53

    Sum all amounts for each ID

    I have a query pulling data from multiple tables. So each ID has multiple amounts, and I'd like the sum of the amounts for each ID.


    Example of what my query results look like:

    ID Amount
    --- --------------
    1 5
    1 15
    1 2
    2 6
    2 6
    3 12
    3 4
    3 7

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Write a query with groups [id] and sum[amount]?


    Sent from my iPhone using Tapatalk

  3. #3
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Quote Originally Posted by andy49 View Post
    Write a query with groups [id] and sum[amount]?
    Query was this:
    SELECT IvcTbl.IvcID, IvcTbl.IvcDt, IvcTbl.CustId, IvcTbl.OrdId, IvcTbl.Amt, IvcTbl.SlsTax, CustTbl.Customer, CustTbl.Addr1, CustTbl.City, CustTbl.State, OrdTbl.ShpVia
    FROM (CustTbl INNER JOIN OrdTbl ON CustTbl.[CustId] = OrdTbl.[CustId]) INNER JOIN IvcTbl ON OrdTbl.[CustId] = IvcTbl.[CustId]
    WHERE (((IvcTbl.IvcDt) Between #2/1/2013# And #2/28/2013#) AND ((IvcTbl.CustId)="stuff") AND ((CustTbl.State)="__"))
    ORDER BY IvcTbl.IvcDt;

    And is now:

    SELECT IvcTbl.IvcID, IvcTbl.IvcDt, IvcTbl.CustId, IvcTbl.OrdId, SUM(IvcTbl.Amt) AS TotalAmount, IvcTbl.SlsTax, CustTbl.Customer, CustTbl.Addr1, CustTbl.City, CustTbl.State, OrdTbl.ShpVia
    FROM (CustTbl INNER JOIN OrdTbl ON CustTbl.[CustId] = OrdTbl.[CustId]) INNER JOIN IvcTbl ON OrdTbl.[CustId] = IvcTbl.[CustId]
    WHERE (((IvcTbl.IvcDt) Between #2/1/2013# And #2/28/2013#) AND ((IvcTbl.CustId)="stuff") AND ((CustTbl.State)="__"))
    ORDER BY IvcTbl.IvcDt,
    GROUP BY IvcTbl.IvcID;



    Getting a syntax error on the last line. I've tried shuffling things around, and the error isn't going away.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Remove comma at end of ORDER BY Line?

  5. #5
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Quote Originally Posted by Bulzie View Post
    Remove comma at end of ORDER BY Line?
    That was one of the things I tried. Still getting a syntax error: Missing operator in query expression 'IvcTbl.IvcDt GROUP BY IvcTbl.IvcID'

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You cannot do this manually, you must let Access do it for you. Take your original query, go into Design View, click on the Totals icon at the top and see what happens.

  7. #7
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Quote Originally Posted by aytee111 View Post
    You cannot do this manually, you must let Access do it for you. Take your original query, go into Design View, click on the Totals icon at the top and see what happens.
    What if I only want to group the IDs together? A pop says "Your query does not include the specified expression 'column_name' as part of an aggregate function" for every single column that I leave blank in the Total row.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your first post says all that you want is the ID and the amount. A grouped query requires all fields to have something in the total line.

  9. #9
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Quote Originally Posted by aytee111 View Post
    Your first post says all that you want is the ID and the amount. A grouped query requires all fields to have something in the total line.
    Ahh okay. The person who wants this info needs other columns to be shown as well, but I was just showing the relationship between the ID and amount in my first post. Sorry for confusion!

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You will probably need two queries. One will contain all the info in your SQL statement, join in the second query which just contains the amount totaled.

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

Similar Threads

  1. Replies: 12
    Last Post: 01-19-2016, 08:07 AM
  2. Ask a process amounts
    By azhar2006 in forum Queries
    Replies: 4
    Last Post: 01-14-2014, 01:06 PM
  3. Aggregate Amounts and group ID together
    By shoro in forum Queries
    Replies: 1
    Last Post: 06-07-2013, 10:59 AM
  4. Help adding amounts from 3 tables
    By mrfixit1170 in forum Queries
    Replies: 10
    Last Post: 11-09-2011, 02:26 PM
  5. Quarterly Amounts
    By Brian62 in forum Queries
    Replies: 9
    Last Post: 10-16-2009, 02:18 PM

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