Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Rwathen10 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2015
    Posts
    13

    Lightbulb SumIF in Access? Sum Field Values only if another field's values meet criteria

    Hello again, shout out to Orange for helping me out with my last question. Now, I am in a pickle. I need for Access only to sum the “hours worked” of my payroll data, only if another field, "pay code" meets a certain criteria.

    In Payroll, some codes are used to designate actual hours worked like REG (Regular Hours) and like OTS (Overtime Straight) while other codes are added as “premiums” to these hours and do not mean that you actually worked those hours, like OTP and HOD (Overtime Premium & Holiday Premium). The hours are only added as a reference to the actual hours worked they are attached to. Their units should not be counted as hours worked.



    I want Access to 1) Group By Cost Center, 2) Sum “Hours Paid” that are tied to a REG or OTS (only), but 3)Sum the Earnings for all pay types.

    Basically, (in excel-like terms: Hours Paid Column= SUMIF of Paycode = REG OR OTS. But Earnings Amount Column= Sum of all Paycodes)
    Cost Center Job Type Hours Paid Pay Code Earnings Amount
    20530 Security Guard II 7.5 REG 190.37
    20530 Security Guard II 4.29 HOD 163.17
    20530 Security Guard II 3.21 HOD 122.38
    20530 Security Guard II 1.08 OTL 14.5
    20530 Security Guard II 4.92 OTL 65.8
    20530 Security Guard II 1.14 OTS 29.01
    20530 Security Guard II 4.86 OTS 123.28
    20530 Security Guard II 17.14 RED 435.12
    80856 Plant Operator 4.285735 HOD 290.35
    80856 Plant Operator 3.214265 HO2 217.76
    80856 Plant Operator 4.285668 OTL 131.73
    80856 Plant Operator 3.214332 OTL 98.8
    80856 Plant Operator 4.285809 OTS 193.57
    80856 Plant Operator 3.214191 OTS 145.17
    80856 Plant Operator 17.142903 REG 774.27
    80856 Plant Operator 50.357097 REG 2,274.41


  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,863
    I would create a Calculated field for "Actual Hours", i.e.
    ActualHours: IIF(([Pay Code]="REG") OR ([Pay Code]="OTS"),[Hours Paid],0)

    Then do an Aggregate (Totals) Query to Group By "Cost Center", and sum this "ActualHours" calculated field and the "Earnings Amount" field.
    Note that those are the only three fields you should have in the Aggregate (Totals) Query.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    Rwathen10 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2015
    Posts
    13
    Can I somehow write the query so that only the HOD and OTL are excluded from the Sum? I say this because we have about 50 other payroll codes I would have to include into this IIF function with “REG” and “OTS” where Hours paid would have to be included, whereas we only have 3 or 4 codes where we exclude the hours.
    Would this work? IIF (([Pay Code] <> “OTL”) AND ([Pay Code] <> “HOD”, [Hours Paid], 0)

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,863
    Would this work? IIF (([Pay Code] <> “OTL”) AND ([Pay Code] <> “HOD”, [Hours Paid], 0)
    It should. Try it and find out!
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,930
    Just saw Joe's post.Sounds good to me.

    Ooops I see he posted again while I was typing???
    Hmm?

  6. #6
    Rwathen10 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2015
    Posts
    13
    I love this site. It is such an amazing concept. It has helped it out so much in just 2 post. I just started using access so I am going to be asking a lot more than replying to posts for now! Thanks, Joe (I don't have access on this PC, so I will try it at my office)

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,930
    This line seems to have Typo
    20530 Security Guard II 17.14 RED
    435.12
    and this one
    80856, Plant Operator, 3.214265 , HO2, 217.76

    Do you really have OTS and OTL or is that a typo also?

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,930
    I used this query (similar to Joe's suggestion) to get actualHours
    Code:
    SELECT costcenter
    , IIF(([PayCode]="REG") OR ([PayCode]="OTS"),[HrsPaid],0) AS ActualHours
    , Paycode
    , EarningsAmount
    FROM Costcenter2;
    which gives this as data
    Code:
    costcenter ActualHours Paycode EarningsAmount
    20530 7.5 REG 190.37
    20530 0 HOD 163.17
    20530 0 HOD 122.38
    20530 0 OTL 14.5
    20530 0 OTL 65.8
    20530 1.14 OTS 29.01
    20530 4.86 OTS 123.28
    20530 17.14 REG 435.12
    80856 0 HOD 290.35
    80856 0 HOD 217.76
    80856 0 OTL 131.73
    80856 0 OTL 98.8
    80856 4.285809 OTS 193.57
    80856 3.214191 OTS 145.17
    80856 17.142903 REG 774.27
    80856 50.357097 REG 2
    Then this query, using the above query, to do sums and grouping.

    Code:
    SELECT costcenter_frst.costcenter
    , Sum(costcenter_frst.ActualHours) AS SumOfActualHours
    , Sum(costcenter_frst.EarningsAmount) AS SumOfEarningsAmount
    FROM costcenter_frst
    GROUP BY 
    costcenter_frst.costcenter;
    To get this data

    Code:
    costcenter SumOfActualHours SumOfEarningsAmount
    20530 30.64 1143.63
    80856 75 1853.65
    Hope it is useful. (and that any assumptions and data mods are in line with your needs)

  9. #9
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,863
    I love this site. It is such an amazing concept. It has helped it out so much in just 2 post. I just started using access so I am going to be asking a lot more than replying to posts for now! Thanks, Joe (I don't have access on this PC, so I will try it at my office)
    You are most welcome! Glad to help out!
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  10. #10
    Rwathen10 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2015
    Posts
    13
    Hi Orange, yes- those were typos. I had to change the codes and remove weird information associated to the codes that was just going to confuse you guys (plus, I don't want to reveal my employers financial details. )

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,930
    Understood. But you can see how important details are for those who are trying to help. We shouldn't have to fix typos to import some data. Good luck with your project.

  12. #12
    Rwathen10 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2015
    Posts
    13
    Guys, what am I doing wrong here? I get an error saying that "this expression has entered an invalid string" when I try to create an "ActualHours" field
    Please Note: these are the actual pay codes I want to exclude from adding into "Hours Paid".
    On my example on my original post, I simplified the pay roll codes.

    Also, please let me know if I can you use an "Is Like" statement nested with the IIF here to disqualify all pay codes that are like HOD* or OTL*. The 3 character prefix of the excluded pay codes does not change, but the description does change so if I could get my IIF statement to exclude pay codes that start with HOD, I should not have to nest all the additional descriptions that come after it and may change.

    ActualHours: IIF(([Pay Code] <> "HOD-Holiday Worked 0.5”) OR ([Pay Code] <> “HOD-Working Holiday”) OR ([Pay Code] <> “OTL-Overtime Diff T&L OT 0.5”) OR ([Pay Code] <> “OTL-Overtime Premium”) OR ([Pay Code] <> “GOT-Retro Overtime -- PSoft Calc”) OR ([Pay Code] <> “HO2-Wkg Hod Vac Ded”) OR ([Pay Code] <> “HO2-Wkg Hol Vac Ded”) OR ([Pay Code] <> “HO2-Holiday Worked 1.5”), [hours paid],0)

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,930
    See the link and approach suggested at https://www.accessforums.net/queries...tml#post268541

    Also it pays to draw a flow/logic chart to get the best understanding of what you are trying to do.

  14. #14
    Rwathen10 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2015
    Posts
    13
    Hi Orange,

    that approach may not work for me as I already have another field in my Aggregate Query that contains a “Where” Statement, so I will probably mess up the SQL if I were to replace it. Also, can “Is Like*” be used in conjunction with an IIF Statement. For your reference, the SQL is below:

    SELECT [ref_Charge CC by Bus Unit].[Bus Unit], [2015 Payroll Data].Charge, [2015 Payroll Data].Account, Sum([2015 Payroll Data].[Hours Paid]) AS [SumOfHours Paid], Sum([2015 Payroll Data].[Earnings Amount]) AS [SumOfEarnings Amount]
    FROM [ref_Charge CC by Bus Unit] INNER JOIN [2015 Payroll Data] ON [ref_Charge CC by Bus Unit].[Charge CC] = [2015 Payroll Data].Charge
    WHERE ((([2015 Payroll Data].[Pay Period Number])="03" Or ([2015 Payroll Data].[Pay Period Number])="04"))
    GROUP BY [ref_Charge CC by Bus Unit].[Bus Unit], [2015 Payroll Data].Charge, [2015 Payroll Data].Account
    HAVING ((([2015 Payroll Data].Account)="00013" Or ([2015 Payroll Data].Account)="00014" Or ([2015 Payroll Data].Account)="00019" Or ([2015 Payroll Data].Account)="00021" Or ([2015 Payroll Data].Account)="00024"))
    ORDER BY [ref_Charge CC by Bus Unit].[Bus Unit], [2015 Payroll Data].Charge, [2015 Payroll Data].Account;

  15. #15
    Rwathen10 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2015
    Posts
    13
    Joe, Help! Orange's answers usually involve SQL and I am not there yet!

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

Similar Threads

  1. Replies: 5
    Last Post: 07-29-2014, 11:20 AM
  2. Replies: 1
    Last Post: 02-19-2014, 11:07 AM
  3. Replies: 9
    Last Post: 01-03-2013, 04:04 PM
  4. Replacing many values at once in a multiple values checkbox field.
    By ConfusedDatabaseDesigner in forum Forms
    Replies: 2
    Last Post: 07-20-2012, 08:58 AM
  5. MS Access sum values of one field in all records of one table
    By logamuthu in forum Import/Export Data
    Replies: 2
    Last Post: 11-22-2011, 12:39 PM

Tags for this Thread

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 - Senior Forums