Results 1 to 4 of 4
  1. #1
    redmage21 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    2

    Excluding certain rows from a sum

    I am editing a query to check for overtime between two specified dates. I want it to ignore PTO and Holidays however it keeps calculation these into the sum no matter what I do. I'd rather not have manually write the query if possible just have it ignore the data if it is from dbo.categories.name then the actual name(PTO,Holidays). I am using the design view rather than the actual SQL view.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    change names to suit but you can use an iif within your sum

    e.g. TotalExclHol:sum(iif(categories<>"Holidays",Hours, 0)

  3. #3
    redmage21 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    2
    Can I type that in the design view under expressions somehow? Sorry I am a complete novice at access.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    just copy and paste the code I provided into the field row of the query grid and change categories and Hours to the names of the field in your table and Holidays to whatever the value is used in categories

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

Similar Threads

  1. Excluding Weekends
    By Thompyt in forum Programming
    Replies: 3
    Last Post: 03-11-2016, 06:25 PM
  2. Replies: 22
    Last Post: 12-20-2015, 02:46 AM
  3. Replies: 4
    Last Post: 02-25-2014, 01:09 AM
  4. Excluding data
    By Vic1980 in forum Queries
    Replies: 3
    Last Post: 03-14-2012, 06:34 AM
  5. Replies: 0
    Last Post: 02-09-2012, 05:43 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