Possible Automation

[Samantha]

Hello,

I want to determine if Access can automate some of my Payroll woahs.

So my example table is what pulls out of a timekeeping app. The first column is an employee ID; so we are looking at one person. The last two rows from 12/01 was originally one line that I have manually split into two separate lines and then filled in either R or O to manually split the OT. This then gets uploaded to the payroll company but because all time is job costed this is how it has to be split.

Does automating this through access seem possible? I am currently doing this in excel but feel like if it can be done access is the place.

Thanks in advance!
Samantha

 

[June7]

Yes, should be possible. If you can define the decision rules, should be able to code. Open a recordset object to manipulate data.

Really need more details. Do you want to act on a single employee or a group of employees? For a single date or multiple dates? What criteria would determine record(s) pulled to recordset for manipulation? And what determines the 'split' - in example was OT originally 6 and it was split to 1 and 5 - why?

The example data you show is for one employee - 5 imported records is not much of a sample.

 

[The_Doc_Man]

I want to determine if Access can automate some of my Payroll woahs.

The correct answer is yes, maybe, and no. You have to have rules, know them, and be able to program them. I usually offer the "Old Programmer" rules.

1. If you cannot do it on paper, you cannot do it in Access.

Translation: Determine the rules that you apply when doing something manually. Can you define the rules that would apply in each case? If you can draw out a general diagram of your input data and the formulas and actions you need to process your data, and if you can draw a data flow diagram on paper, you can convert that to processes within Access. If you can't do everything on paper, you aren't READY to do it in Access.

2. Access won't tell you what you didn't explain to it first.

Access is dumber than a box of rocks when it comes to your problem. YOU are the subject-matter expert. Access knows NOTHING AT ALL about your payroll rules. Therefore, you have to think like you are teaching a precocious child and write out detailed processes and procedures to achieve your goals. As part of that, you must have an idea of what you wanted to get out of the project. (SEE RULE 1.) Then you might have to sometimes work your way backwards through your processes to assure that you have what you need for each computation. If you want to see XYZ in output, you might have to backtrack to where XYZ is input OR where X, Y, and Z are input, and then assure you have a formula to turn them into XYZ.

3. The tail never wags the dog.

When you develop your procedures it is possible you will come to a point where your app and your business rules disagree. When this happens, you must review both the rules and the program, because when reality and theory disagree, one of them is wrong, and it is advised to never bet against reality. So when your program seems to be diverting from the way the business has always been run, be triple-sure that your program isn't written incorrectly. With the side note that it CAN sometimes happen that you find an error in your business process, in which case you can correct it. But just remember, arguing with reality is a losing proposition long-term. If your code does something that the business rules DON'T do, then the tail is suddenly trying to wag the dog.

These are high-level rules, but they apply to various aspects of any project and are broad-brush ways to understand how to make your Access app conform to your business.

 

[tvanstiphout]

Yes, should be possible. If you can define the decision rules, should be able to code. Open a recordset object to manipulate data.

Really need more details. Do you want to act on a single employee or a group of employees? For a single date or multiple dates? What criteria would determine record(s) pulled to recordset for manipulation? And what determines the 'split' - in example was OT originally 6 and it was split to 1 and 5 - why?

The example data you show is for one employee - 5 imported records is not much of a sample.

> in example was OT originally 6 and it was split to 1 and 5 - why?
Because the 1 makes for 40 hours of regular time this week (in addition to the 9+10+10+10), and 5 is overtime.

 

[tvanstiphout]

Hello,

I want to determine if Access can automate some of my Payroll woahs.

So my example table is what pulls out of a timekeeping app. The first column is an employee ID; so we are looking at one person. The last two rows from 12/01 was originally one line that I have manually split into two separate lines and then filled in either R or O to manually split the OT. This then gets uploaded to the payroll company but because all time is job costed this is how it has to be split.

Does automating this through access seem possible? I am currently doing this in excel but feel like if it can be done access is the place.

View attachment 111548


Thanks in advance!
Samantha

It's not hard to add up weekly hours, and if sum > 40 the overage is overtime.
But what if the employee had Monday off and did not work the 9 hours. Would they still have overtime if exceeding 32 hours? This is where you really need to nail down the rules, and if necessary get buy-in from management before you implement.

 

[Samantha]

Thank you for all of the replies!

I am attaching a pdf to try and illustrate/further explain in hopes of direction.

Multiple employees, over weekly pay period Mon - Sun. I could create a feed to access through a link to an excel data sheet. I don't need access to remember/store the data so I could re-write the feed weekly.

The job that is identified as OT will be:

• within the pay period (data is pulled/ran Monday for the last 7 days)
• the last hours worked for any given employee
• that exceeds 40 and that is the record (Job Code) that needs to be split.

This needs to exclude PTO as it is not included in the OT calculation. The PTO is identified in the JobCode field as "|||||PTO|"

Tom - you hit the nail on the head "Because the 1 makes for 40 hours of regular time this week (in addition to the 9+10+10+10), and 5 is overtime."

Hope I did a better job explaining or talking in circles. Thanks for the guidance.

Happy Holiday's

 

[Pat Hartman]

In addition to data, you really need to provide the actual, legal rules for determining overtime. Coding by example is not a good way to present this since YOU are interpreting the rules and we have never seen them.

 

[MajP]

This can be done in either Access or Excel. I have done similar things with time assignments in both. It takes vba either way. If more comfortable in Excel you can go that route.

Either way you are reading the rows/records and simply adding additional rows/records based on the running totals and the rules. Then when you add a row/record the row/record you "split" you have to modify the existing values because you subtract some amount from an existing row/record and put that in the new row/s/Recor/s

I would do it in Access because I am just way better at it and it would be much simpler for me, but if you are better in Excel the logic is the same just the methods are different.

 

[CJ_London]

But what if the employee had Monday off and did not work the 9 hours. Would they still have overtime if exceeding 32 hours?

You need to answer this question as well - plus type of ‘off’ time - sick? Holiday? Training? Etc

and you say last 7 days but your example does not include the weekend? Does weekend working count for double time or similar? Or just overtime?

 

[Pat Hartman]

Rules, it is silly to try to write code without knowing all the rules. But in general, this is a sequential process. So, you would have a query that selects the data for the time period and orders it by employeeID and within that by date, time, and project. Your code reads the query and keeps a running total of each type of "time" for the employee so you have at least - regular, PTO, vacation, Sick (if separate from PTO), 1.5 OT, 2 OT, 3 OT (or whatever your rules call for). Because of how OT works, it can start mid-record. I think legally, OT starts after 8 hours in one day or 40 hours in one week. I don't know how shift work impacts any of this because shifts can span two days.

 

[LarryE]

Hello,

I want to determine if Access can automate some of my Payroll woahs.

So my example table is what pulls out of a timekeeping app. The first column is an employee ID; so we are looking at one person. The last two rows from 12/01 was originally one line that I have manually split into two separate lines and then filled in either R or O to manually split the OT. This then gets uploaded to the payroll company but because all time is job costed this is how it has to be split.

Does automating this through access seem possible? I am currently doing this in excel but feel like if it can be done access is the place.

View attachment 111548


Thanks in advance!
Samantha

If you design your application using the proper tables and create the proper relationships between those tables, then yes it is possible. If you don't design it properly, then no it is not possible. Further, if you attempt to make ACCESS perform like a spreadsheet, that will not work either.

Do you have a design in mind for us to see? Perhaps you can take a screenshot of the Relationships window and attach it. That will allow us to have some idea of how your business operates and how each table relates to one another. This will need to be done before you attempt to create any forms, reports or queries. Then, you can also create the "rules" for calculating any payroll amounts.

 

[Samantha]

Hello,

Thanks for all the replies! Hope everyone enjoyed the holidays! I had to take some time off and not check my emails. I am going to try and effectively respond.

Love programmers! You're all so detailed oriented and want to get into the nitty gritty.

The legal OT rules in my state is anything over 40 hours. It is not dependent on exceeding 8 hours in a day/or any other more complicated processes. The last hours worked in the work week are what needs to be marked as OT. Pretty straight forward here. Holiday's and PTO would be excluded from the equation altogether/not included in the calculation to determine the actual OT.
The work week is Monday-Sunday and payroll is ran on a weekly basis Monday/Tuesday. So for 12/25 - 12/31 it was completed 1/02.

So the csv that I download and the table I would create in access would be identical to each other. Lets call it tblTimekeeping with the following fields: Payroll_ID, fname, group, local_date, local_day, job_code, earning_code, hours

I wouldn't need to create a relationship to anything else in the DB however the payroll_id is my FK and I suppose I would just use an autoID for the PK. This db holds training records, employee addresses, DOB's stuff of that nature.

So I would then build my qry based on the pay period to get my data isolated...lets call it qryPayperiod...then I don't know where to go.
@Pat Hartman - you have an example of how to accomplish this? or point me in the right direction of what functions to use> I am by no means a pro but comfortable enough that I will do the leg work. Anything to stop this monotonous time suck

Your code reads the query and keeps a running total of each type of "time" for the employee so you have at least - regular, PTO, vacation, Sick (if separate from PTO), 1.5 OT, 2 OT, 3 OT (or whatever your rules call for). Because of how OT works, it can start mid-record.

PTO & Holiday would be identified in the table in the jobcode field as HOL or PTO then the earnings code has to match.

The end result I just need to export back to a csv to upload to the payroll provider. Once that is complete they spit back out job costing reports with $$$ included which is then entered into the bookkeeping software and that is the basis of me not wanting to keep the data. It is going from one system into two others and would just be redundant.

Hopefully that clears up some of the questions, thanks for reading!

 

[MajP]

Assume
tblData tblData

PayRoll_ID	LocalDate	JobCode	Hours
EA26	12/11/2023​	1234	9.5​
EA26	12/12/2023​	1234	9.75​
EA26	12/13/2023​	1234	10​
EA26	12/14/2023​	1234	9.75​
EA26	12/15/2023​	1234	9.25​
EA63	12/10/2023​	1234	44​
EA26	12/18/2023​	1234	22​
EA63	12/11/2023​	4567	10​
EA63	12/12/2023​	4567	10.5​
EA63	12/13/2023​	4567	10.5​
EA63	12/14/2023​	4567	10​
EA63	12/15/2023​	4567	10​

Something like this

Public Sub UpdateData()
  Const DateStart = #12/11/2023#
  Const dateEnd = #12/17/2023#

  Dim rsOld As DAO.Recordset
  Dim rsNew As DAO.Recordset
  Dim strSql As String
  Dim cumHours As Double
  Dim OldID As String
  Dim Payroll_ID As String
  Dim ECO As Double
  Dim JobCode As String
  Dim LocalDate As Date
  Dim EarningCode As String
  Dim R_Hours As Double
  Dim O_Hours As Double
  Dim Hours As Double
  strSql = "Select * from tblData where localDate between #" & DateStart & "# AND #" & dateEnd & "# order by Payroll_ID, localDate"
  Set rsOld = CurrentDb.OpenRecordset(strSql)
  Set rsNew = CurrentDb.OpenRecordset("tblNewData")

  Do While Not rsOld.EOF
   Payroll_ID = rsOld!Payroll_ID
  Hours = rsOld!Hours
   LocalDate = rsOld!LocalDate
   JobCode = rsOld!JobCode
   Hours = rsOld!Hours
   O_Hours = 0
   R_Hours = 0
   If OldID <> Payroll_ID Then
     OldID = Payroll_ID
     cumHours = 0
   End If
   'If all new hours are overtime
   If cumHours > 40 Then
     O_Hours = Hours
     cumHours = cumHours + Hours
   Else
     cumHours = cumHours + Hours
    If cumHours > 40 Then
      O_Hours = cumHours - 40
      R_Hours = Hours - O_Hours
    Else
      R_Hours = Hours
    End If
   End If

   If R_Hours > 0 Then
     rsNew.AddNew
       rsNew!Payroll_ID = Payroll_ID
       rsNew!LocalDate = LocalDate
       rsNew!JobCode = JobCode
       rsNew!Hours = R_Hours
       rsNew!EarningCode = "R"
       rsNew!cumulativeHours = cumHours
     rsNew.Update
   End If

 

   If O_Hours > 0 Then
     rsNew.AddNew
       rsNew!Payroll_ID = Payroll_ID
       rsNew!LocalDate = LocalDate
       rsNew!JobCode = JobCode
       rsNew!Hours = O_Hours
       rsNew!EarningCode = "O"
       rsNew!cumulativeHours = cumHours
     rsNew.Update
   End If
 

   rsOld.MoveNext
  Loop
End Sub

Output
tblNewData tblNewData

PayRoll_ID	LocalDate	JobCode	Hours	EarningCode	CumulativeHours
EA26	12/11/2023​	1234	9.5​	R	9.5​
EA26	12/12/2023​	1234	9.75​	R	19.25​
EA26	12/13/2023​	1234	10​	R	29.25​
EA26	12/14/2023​	1234	9.75​	R	39​
EA26	12/15/2023​	1234	1​	R	48.25​
EA26	12/15/2023​	1234	8.25​	O	48.25​
EA63	12/11/2023​	4567	10​	R	10​
EA63	12/12/2023​	4567	10.5​	R	20.5​
EA63	12/13/2023​	4567	10.5​	R	31​
EA63	12/14/2023​	4567	9​	R	41​
EA63	12/14/2023​	4567	1​	O	41​
EA63	12/15/2023​	4567	10​	O	51​

However more likely you will have a form to select the start and end dates for the period.

To do this you need to base the recordsets on a correctly sorted query since you are reading row by row. I also am working a pay period at a time where the user would provide the start and end dates. Your data appears to have a unique payroll_ID per user per week. So in theory it would work without the date filter, if that is true.

The only rule applied is >40 hours. I did not check holidays, weekends, or PTO. The idea is the same just add more If checks.

 

[Pat Hartman]

The legal OT rules in my state is anything over 40 hours. It is not dependent on exceeding 8 hours in a day/or any other more complicated processes.

So a part time worker could work 12 hours in one day and have it all paid as straight time? How about a FT worker who had 1 day of 12 hours but took 8 hours of PTO leaving him with 36 regular hours. Does he get 4 hours of OT?

I've never seen a payroll system where it was as simple as accumulating to 40 and then going to OT.

Are these union or non-union workers?
What does your input file look like?

Sorry, I don't have any code examples. All my payroll experience was with COBOL on an IBM mainframe. Not Access.

 

[pbaldy]

I've never seen a payroll system where it was as simple as accumulating to 40 and then going to OT.

You must never have seen a system that followed federal (and most states) law. Employers can and do adopt something more restrictive, but that is the federal law's requirements.

 

[Pat Hartman]

I'm guessing that unions were always involved.

 

[Samantha]

Assume
tblData tblData

PayRoll_ID	LocalDate	JobCode	Hours
EA26	12/11/2023​	1234	9.5​
EA26	12/12/2023​	1234	9.75​
EA26	12/13/2023​	1234	10​
EA26	12/14/2023​	1234	9.75​
EA26	12/15/2023​	1234	9.25​
EA63	12/10/2023​	1234	44​
EA26	12/18/2023​	1234	22​
EA63	12/11/2023​	4567	10​
EA63	12/12/2023​	4567	10.5​
EA63	12/13/2023​	4567	10.5​
EA63	12/14/2023​	4567	10​
EA63	12/15/2023​	4567	10​

Something like this

Public Sub UpdateData()
  Const DateStart = #12/11/2023#
  Const dateEnd = #12/17/2023#

  Dim rsOld As DAO.Recordset
  Dim rsNew As DAO.Recordset
  Dim strSql As String
  Dim cumHours As Double
  Dim OldID As String
  Dim Payroll_ID As String
  Dim ECO As Double
  Dim JobCode As String
  Dim LocalDate As Date
  Dim EarningCode As String
  Dim R_Hours As Double
  Dim O_Hours As Double
  Dim Hours As Double
  strSql = "Select * from tblData where localDate between #" & DateStart & "# AND #" & dateEnd & "# order by Payroll_ID, localDate"
  Set rsOld = CurrentDb.OpenRecordset(strSql)
  Set rsNew = CurrentDb.OpenRecordset("tblNewData")

  Do While Not rsOld.EOF
   Payroll_ID = rsOld!Payroll_ID
  Hours = rsOld!Hours
   LocalDate = rsOld!LocalDate
   JobCode = rsOld!JobCode
   Hours = rsOld!Hours
   O_Hours = 0
   R_Hours = 0
   If OldID <> Payroll_ID Then
     OldID = Payroll_ID
     cumHours = 0
   End If
   'If all new hours are overtime
   If cumHours > 40 Then
     O_Hours = Hours
     cumHours = cumHours + Hours
   Else
     cumHours = cumHours + Hours
    If cumHours > 40 Then
      O_Hours = cumHours - 40
      R_Hours = Hours - O_Hours
    Else
      R_Hours = Hours
    End If
   End If

   If R_Hours > 0 Then
     rsNew.AddNew
       rsNew!Payroll_ID = Payroll_ID
       rsNew!LocalDate = LocalDate
       rsNew!JobCode = JobCode
       rsNew!Hours = R_Hours
       rsNew!EarningCode = "R"
       rsNew!cumulativeHours = cumHours
     rsNew.Update
   End If



   If O_Hours > 0 Then
     rsNew.AddNew
       rsNew!Payroll_ID = Payroll_ID
       rsNew!LocalDate = LocalDate
       rsNew!JobCode = JobCode
       rsNew!Hours = O_Hours
       rsNew!EarningCode = "O"
       rsNew!cumulativeHours = cumHours
     rsNew.Update
   End If


   rsOld.MoveNext
  Loop
End Sub

Output
tblNewData tblNewData

PayRoll_ID	LocalDate	JobCode	Hours	EarningCode	CumulativeHours
EA26	12/11/2023​	1234	9.5​	R	9.5​
EA26	12/12/2023​	1234	9.75​	R	19.25​
EA26	12/13/2023​	1234	10​	R	29.25​
EA26	12/14/2023​	1234	9.75​	R	39​
EA26	12/15/2023​	1234	1​	R	48.25​
EA26	12/15/2023​	1234	8.25​	O	48.25​
EA63	12/11/2023​	4567	10​	R	10​
EA63	12/12/2023​	4567	10.5​	R	20.5​
EA63	12/13/2023​	4567	10.5​	R	31​
EA63	12/14/2023​	4567	9​	R	41​
EA63	12/14/2023​	4567	1​	O	41​
EA63	12/15/2023​	4567	10​	O	51​

However more likely you will have a form to select the start and end dates for the period.

To do this you need to base the recordsets on a correctly sorted query since you are reading row by row. I also am working a pay period at a time where the user would provide the start and end dates. Your data appears to have a unique payroll_ID per user per week. So in theory it would work without the date filter, if that is true.

The only rule applied is >40 hours. I did not check holidays, weekends, or PTO. The idea is the same just add more If checks.

Thanks for your time and direction, I will try and dig into this!

 

[Samantha]

So a part time worker could work 12 hours in one day and have it all paid as straight time? How about a FT worker who had 1 day of 12 hours but took 8 hours of PTO leaving him with 36 regular hours. Does he get 4 hours of OT?

I've never seen a payroll system where it was as simple as accumulating to 40 and then going to OT.

Are these union or non-union workers?
What does your input file look like?

Sorry, I don't have any code examples. All my payroll experience was with COBOL on an IBM mainframe. Not Access.

Pat - sorry to throw you for a loop. PTO is not included in the calcs but otherwise its simply anything over 40. We are a federal contractor, comply with Beacon Davis (certified payroll) private corp- non-union in Maryland.
Tell me your in California? They are their own country with lots of rules.
I posted a data sample in post #6.
I'm going to mark this as solved for now since I have somewhere to start.
Take Care. I'm sure I will be posting again very shortly for help.

 

[Pat Hartman]

No, Connecticut, but my clients were large companies with multiple unions to deal with and operations in multiple states. I worked on probably some of the last private payroll systems ever developed. Payroll has gotten so stupid that most companies rely on third parties to keep up with the laws rather than building in-house applications these days.