Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    mjdizenzo is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14

    Combine Sums of fields with same first column

    Please refer to picture.

    I am trying to create a query where I can combine the information of each store.

    I need one line for store with the values of each merchant # added together.

    Then I need to exclude one of the merchants.

    Anyone?

    Click image for larger version. 

Name:	image003.jpg 
Views:	16 
Size:	94.6 KB 
ID:	7994

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Either take Merchant out of the query or change the Totals row from Group By to Where.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    mjdizenzo is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14
    Thanks! Now I have 2 Queries. One Query pulls the sum of all the fields and all the merchant #s.
    The other Query just pulls the sums of one specific merchant #.

    How can I take the 2nd Query and subtract it from the first Query.

    Basically I am saying I want all the data EXCEPT a certain merchant #

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not sure you need the second query. Can't you just exclude the merchant from the first query? The criteria would look like

    <>12345
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    mjdizenzo is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14
    But then that would give me separate rows for each merchant per store. I removed merchant to get one line per store

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm back to my first answer; change Group By to Where for the Merchant field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    mjdizenzo is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14
    Ok I tried doing the <>900016 for the criteria under MERCHANT when its set as Where, however it's still adding it to the sum of everything.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post the db, or at least the SQL of that query?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    mjdizenzo is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14
    Code:
    SELECT STREG.DIV, [USSS POS Daily].STORE, Sum([USSS POS Daily].[WTD REDEEM AMOUNT]) AS [SumOfWTD REDEEM], Sum([USSS POS Daily].[WTD TOTAL CREDIT]) AS [SumOfWTD TOTAL CREDIT], Sum([USSS POS Daily].[WTD NET AMOUNT]) AS [SVS NET], Sum([USSS POS Daily].[WTD RELOAD AMOUNT]) AS [SVS RELOAD], Sum([USSS POS Daily].[WTD ISSUE AMOUNT]) AS [SVS ISSUED]
    FROM STREG INNER JOIN [USSS POS Daily] ON STREG.STR = [USSS POS Daily].STORE
    WHERE ((([USSS POS Daily].MERCHANT)<>900016) AND (([USSS POS Daily].[Report Date])=[Enter Report Date]))
    GROUP BY STREG.DIV, [USSS POS Daily].STORE
    ORDER BY [USSS POS Daily].STORE;

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That looks okay offhand, though perhaps something in the join is causing the problem. Can you test without the other table involved?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    mjdizenzo is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14
    Wow, I feel dumb. It was working, sorry, I was just looking at the wrong line.

    Thanks for all your help!

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, glad we got it sorted out. Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    mjdizenzo is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14
    Thanks! This looks like a great site.

    One more question regarding this Query. Is there anyway to exclude a row where all the values = 0? For instance, look at the picture at store 5608. What can I do to not have 5608 in my query results.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    A criteria of <>0 on each field should work, on different lines. In SQL view, that would translate to "OR", where if you put them on the same line it becomes "AND", which you don't want (I assume). Also in SQL view, it may make it a HAVING instead of a WHERE. In your case, I think either would work. WHERE is applied before aggregation, HAVING after.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    mjdizenzo is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14
    I am working in the same database and have another question. My query calls to a table "STREG" that has a list of every store. Then that is linked to another table with that has the "merchant" numbers and stores. I am trying to run a query that pulls all the stores out with a specific merchant #. However, some of those stores don't have that merchant # but I want them to come up anyways. How can I make it so that ALL the stores show up and if that merchant number doesnt exist for that store it just fills in zeros for the rest of the columns?

    Here is my SQL for the query.

    SELECT STREG.DIV, STREG.STR, Sum([USSS POS Daily].[WTD REDEEM AMOUNT]) AS [SumOfWTD REDEEM], Sum([USSS POS Daily].[WTD TOTAL CREDIT]) AS [SumOfWTD TOTAL CREDIT], Sum([USSS POS Daily].[WTD NET AMOUNT]) AS [SVS NETgamer], Sum([USSS POS Daily].[WTD RELOAD AMOUNT]) AS [SVS RELOAD], Sum([USSS POS Daily].[WTD ISSUE AMOUNT]) AS [SVS ISSUED]
    FROM STREG LEFT JOIN [USSS POS Daily] ON STREG.STR = [USSS POS Daily].STORE
    WHERE ((([USSS POS Daily].[Report Date])=[Enter Report Date]))
    GROUP BY STREG.DIV, STREG.STR, [USSS POS Daily].MERCHANT
    HAVING ((([USSS POS Daily].MERCHANT)=66771));

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sums in First Column of Crosstab
    By JRINC in forum Queries
    Replies: 1
    Last Post: 10-06-2011, 02:47 PM
  2. Replies: 1
    Last Post: 07-10-2011, 11:20 PM
  3. Replies: 2
    Last Post: 05-05-2011, 12:41 PM
  4. combine two fields into one
    By kbremner in forum Programming
    Replies: 1
    Last Post: 10-29-2010, 07:40 AM
  5. Combine two fields, Null fields involved
    By yes sir in forum Access
    Replies: 9
    Last Post: 10-03-2010, 09:20 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