Results 1 to 7 of 7
  1. #1
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49

    Sum Column A only if value does not exist in Column B

    Hello,



    I am trying to Group some records by order number. Each order has a count, and I want to sum up the count column only if a certain value does not exist in another column. Here's my stab at it, but this query still adds rows that I don't want.

    SELECT Data.ShopOrder, Sum(IIf(DCOUNT("[Set]","Data","[Set] = 30"),Count,0)) AS Expr1
    FROM Data
    GROUP BY Data.ShopOrder;



    Test.accdb

    Thoughts?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont use DCOUNT in a query. The query does the counting.

    select count([set]) as countOfDate from data where [field] is null

  3. #3
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    OK, so I tried something like this:

    SELECT Data.ShopOrder, Sum(Data.Count) AS Total, (select count([set]) from data where data.[set]=30) AS [Count]
    FROM Data
    WHERE ((([Count])<1))
    GROUP BY Data.ShopOrder;

    The problem is that it is giving the same count to each line in the results instead of giving the count for each individual shop order.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Count is a reserved word. Don't use reserved words as names.

    Try:

    SELECT ShopOrder, Count(*) AS Total FROM Data GROUP BY ShopOrder HAVING Count(Set)=30;

    Provide example of raw data and desired output.
    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
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    Set Widgets Shop Order
    1 5 12
    30 5 12
    1 5 13
    1 5 13
    1 5 14
    1 5 14
    1 5 14

    I would like the sum of the "Widgets" for each shop order, unless the order contains a set = 30. In this case I only want to include the lines that do not equal 30.

    So the query should return orders 12, 13 and 14 only with a sum of 5, 10 and 15, respectively.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Appears to be a fairly simple aggregate query. Use query builder and select Total from the ribbon. Something like:

    SELECT ShopOrder, Sum(Widgets) AS Total FROM Data WHERE Set <> 30 GROUP BY ShopOrder;
    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
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    Thanks! I finally landed on the exact same answer. Don't know why I made it so complicated!

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

Similar Threads

  1. check if column exist
    By xopherira in forum Modules
    Replies: 5
    Last Post: 08-25-2015, 02:09 PM
  2. Replies: 14
    Last Post: 07-13-2015, 12:47 PM
  3. Replies: 4
    Last Post: 06-04-2014, 11:55 AM
  4. Replies: 3
    Last Post: 03-14-2012, 06:48 AM
  5. Replies: 1
    Last Post: 04-15-2010, 02:07 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