Calculating Payroll 1

[SMHSleepy]

Hello all,

I have a challenge that I'm hoping has an easy solution?
I've created a work schedule table, from which I'd like to extract information to create a payroll list every two weeks. I also need to keep monthly stats with the information. The complicated part is that a night shift is split into 2 days: Evening (4.00 hrs) and Night (7.25 hrs). In order to make data entry easier, I just want the user to enter "NS" for night shift on the date the shift begins.

I initially used code that checks for the "NS", then changes the "NS" to "E" (4.00 hrs)and then adds a new record with the next date and "N" (7.25 hrs). This works fine, but during my "dummy-proofing" check, I realized that if the user decides to delete or change one of the split records, it will leave the other part there and thus create inaccuracies in the payroll. Also, it looks messy when the nights are split and you're trying to look back at who worked which night.

I guess my question is: after all data has been entered, how can I take the data from my table, split all the night shifts into 2 parts, choose a date range, then save the information into a new table? Or is there a better way to do all this that I can't see?

Thank you in advance.

 

[lameid]

I don't understand the need to split the record...

Maybe the datediff function is what you are looking for (shows you the date or time difference between two values)?

 

[randy700]

Rather than trying to create new records, I'd get the "split" information in queries or code. For example, you may have different hourly pay rates on the N and E records. So, in code, I'd do something like this:

Select Case WorkShift
    Case "NS":
        EmpPay = (4 * ERate) + (7.25 * NRate)
    Case "E":
        EmpPay = [i]calculation here[/i]
    Case Other:
        EmpPay = [i]calculation here[/i]
End Select

Randy

 

[SMHSleepy]

I suspected my question was not too clear, sorry about that. The reason I want to "split" the night shift is because, for example, if someone works on a Friday night, then they will have 4.00 hrs on the Friday and 7.25 hrs on the Saturday. Our payroll is done from Sat - Fri so it is often the case that they will only get paid for the Fri (Eve = 4.00 hrs) portion on one pay check and the remaining Sat (Night = 7.25 hrs) on the next pay check. It's easy enough if I enter the work dates already split into the Eve and Night portions. Then I simply create a Report within a particular date range and get the payroll hours for each employee.

This is how I originally had it. The problem is, I need to make the data entry form as user friendly (dummy-proof) as possible as there will be several different end-users. I found that some users were entering the Eve and Night portions separately, as they should, but then deleting only one part if they needed to make a correction. This results in extra hours on payroll and general chaos.

So I changed it so that the user simply enters one code for a night shift (11.25 hrs) on the date the shift began. What I need now is a way to split these night shifts into the 2 components as above so that I can correctly generate a payroll report.

My latest thought on this was to make a form with a click event button that loops through each record, copies regular day shift records to a new table but splits the night shift records into 2 separate records. Any thoughts?

 

[randy700]

You might want to have the users enter the start and stop dates/times rather than the number of hours worked. You can then create queries based on those times.

With your current process, what happens if the number of hours entered is less than 11.25? For instance, say the person only worked 8 hours. Did he start later than usual or leave early (or both)? Where do you split the hours now?


Randy

 

[SMHSleepy]

The hours are fixed for a night shift (always 4.00 for evening and 7.25 for night). This doesn't ever change but, regardless, I do have the user enter the date and shift type, not the hours. I calculate the hours after data entry based on the shift type. Here are 2 examples: (2011-05-06, John Smith, DAY) would show on payroll as 7.50 hrs on May 6th and (2011-05-06, Jane Doe, NS) would show on payroll as 4.00 hrs on May 6th and 7.25 hrs on May 7th

 

[TheAceMan1]

How are ya SMHSleepy . . .

[blue]The hours are fixed for a night shift (always 4.00 for evening and 7.25 for night). [purple]This doesn't ever change[/purple] ...[/blue]

I don't see how this is possible. Espcially taking into account [blue]randy700's[/blue] post:

[blue]For instance, say the person only worked 8 hours. Did he start later than usual or leave early (or both)? Where do you split the hours now?[/blue]

Perhaps they got sick. The point is ... [purple]you'd intentionally overpay this person[/purple] if your quote above is true. Surely this can't be?

Either way, you need a [blue]unique key[/blue] that binds the two days together (say NSID as Long). This will be generated/incremented by code during the key assignment. Also ... I see a seperate unbound form with two lines of fields for accepting the data. The user fills in the 1st line and the second is generated. An [blue]accept[/blue] button allows the user to verify the data is good.[blue]NSID[/blue] allows you to easily delete the set with an Query or SQL.

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SMHSleepy]

Hey AceMan, I'm well thank you.

Okay, I agree the number of hours stuff is confusing but it's also a moot point. Just for peace of mind though, I'll try to better explain: We don't punch in and out with a time stamp. If we work a Day shift, we get paid 7.50 hrs. A night shift is paid a total of 11.25 hrs, split into evening and night. Even if the employee leaves early or stays late, the pay is the same (we just make up hrs or leave early some other day). In the event of sickness, the coding is different but the hours are the same.

Your suggestion sounds like the way to go, thanks! I think I was approaching this in the wrong way by trying to split the record and generate a separate table. As I said, I already had it set up so that the user enters the 1st line and the second is generated. By using a unique key that binds the two days together (thereby creating a set), I don't have to worry about having only one deleted by accident. I'll have to play around with some SQL code but I think I can make it work.

Any other suggestions are most welcome also in the meantime. Thanks again!