Results 1 to 3 of 3
  1. #1
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121

    how to exclude rows with zero

    The example below is in excel versus access for demonstration purposes. I was able to get the formulas on the attached to work in access but when summarized I need to get $67.43 and not $80.19, how do I exclude the row with no OT when summarizing?



    E/N Base Reg OT Total Premium
    N $254.71 40 2.8 42.8 $8.33
    N $254.71 40 22.2 62.2 $45.45
    N $254.71 39.5 0 39.5 $-
    N $254.71 40 4.8 44.8 $13.65
    $67.43

    $1,018.84 159.50 29.80 189.30 $80.19
    Attached Files Attached Files

  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
    Not sure I get what you are trying to achieve, but if you want to exclude all the other values if the [OT]=0 then create a query based on your table and use expressions similar to this for every field other than OT:

    Base_OT:IIF([OT]=0,0,[Base])

    Now when you sum them you will get 0s for all rows where OT=0

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

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or in your query, something like

    select sum(base), sum(reghrs).....
    from mytable
    where OThrs<>0

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

Similar Threads

  1. Replies: 22
    Last Post: 12-20-2015, 02:46 AM
  2. Exclude when the current month is the month to exclude?
    By aellistechsupport in forum Queries
    Replies: 15
    Last Post: 05-16-2015, 09:49 PM
  3. Replies: 4
    Last Post: 02-25-2014, 01:09 AM
  4. Replies: 0
    Last Post: 02-09-2012, 05:43 PM
  5. Exclude rows to get to data/field names
    By The Stig in forum Access
    Replies: 1
    Last Post: 06-27-2010, 09:36 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