Results 1 to 3 of 3
  1. #1
    messier is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    18

    Dsum help

    ID :Item :Quantity
    001 :Apple :2
    002 :Apple :2
    001 :Pear :4
    001 :Kiwi :3
    003 :Apple :2
    003 :Apple :1


    001 :Apple :2
    002 :Apple :5


    What i need:
    ID : Quantity (of Apple and Pear)
    001 : 8
    002 : 7


    How do i get 8 and 7 for respective IDs?
    I used DSUM where my criteria is <> KIWI how do i criteria for 001 for row 1 and 002 for row 2?

    but for all IDs, its giving me the sum of entire column. mean the that the quantity (of apples and pears) are all the same.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You need to group by ID and use in another column with Expression in the Totals row: TotalByIDSum("*","tblYourTable",("[ID] = '" & [ID] & "' AND [Item] <> 'Kiwi'")

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Where are you using this DSum expression? Why use DSum instead of Sum in an aggegate query? Are these the only fruits?

    SELECT ID, Sum(Quantity) AS SumApplesPears FROM table WHERE Item <> "Kiwi" GROUP BY ID;

    or

    SELECT ID, Sum(Quantity) AS SumApplesPears FROM table WHERE Item IN("Apple","Pear") GROUP BY ID;
    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.

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

Similar Threads

  1. DSum
    By GuyfromDet in forum Access
    Replies: 1
    Last Post: 02-24-2017, 11:43 AM
  2. Dsum
    By doddiah in forum Access
    Replies: 5
    Last Post: 07-20-2015, 02:56 PM
  3. DSum
    By balajigade in forum Queries
    Replies: 1
    Last Post: 04-26-2015, 10:26 AM
  4. Dsum Help?
    By Ragin_roider in forum Queries
    Replies: 5
    Last Post: 03-19-2012, 03:10 PM
  5. How do I use the DSum
    By Ironclaw in forum Access
    Replies: 1
    Last Post: 08-25-2010, 07:35 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