Best Approach

[Samantha]

I currently have an HR database which contains as you would imagine employee information. It also contains training records, and most recently I am adding wages and raise history. My questions are more related to the attached pdf. We use this excel sheet internally for payroll and we use Quickbooks T-sheets to record payroll and download a csv file weekly from that system to record into what we have named the checkerboard. I would love to import the data from Tsheets into the database and push a button to have it calculate everything. Id like to also preface this with yes I know payroll systems have to conform to certain standards. This report I am trying to create is entered into our payroll system after upper management approves it. Strictly internal.

Lets say tblTsheetsRecords to import Tsheets data again linking on EmployeeID... Then I can easily import job names and I have current wages covered what I am debating is how to structure the tables when it comes to:
1.) Deductions (health, 401k, and other) One table: employee ID, deduction name, amount, effective date.
(^ seems real simple now that I write it out - I should create tblDeductions with FK EmployeeID
2.) Certified Payroll: some jobs may have certified payroll in which say my employee makes $22/hr well the "Painter" category pays $27/hr so now I need hours on that particular job to be overridden by the certified payroll rate while hours that the employee works anywhere else to remain at his/her current rate. I am thinking this has to be a separate table in which I can enter the job ID and category and corresponding rate since jobs will have multiple categories then build an ifthen to control which rate is implemented?
2a.) Additionally I need to be able to accurately calculate overtime. Say Monday - Thursday the employee is working the CP job then Friday/Saturday they are at their regular rate I need to calculate overtime on the regular OT rate and not CP OT. This is all manually controlled in the excel file. You have to move hours from one section to another. I am completely lost here on how I would go about achieving this?
3.) Lastly at the conclusion of the checkerboard completion I am then transferring the total hours worked minus PTO and any holiday hours to a separate spreadsheet to calculate PTO "paid time off". Most employees earn 1 hour of PTO for every 30 hours worked 40 hours max in a year, if you have 10 years of service you then earn 1 hour per 23.75 and max is 80 hours a year. I have everyone's start date so I should be able to calculate in the database which rate the employee should be earning at. However, say I have one employee is on the cusp of 10 years I wouldn't want that event to throw off the calculations so I am thinking this calculation needs to be completed in the table? Which I know is generally frowned upon and calculations should happen in queries which is my hesitation.

Any advice is appreciated.
Thanks in advance!

 

[plog]

Your post really sounds like your using this forum to think out loud to yourself. Which is fine. But the only advice I can give you is---divide and conquer.

Pick one, probably the simplest, of all those things and start tackling it. When you find yourself stuck, start a new thread hear and clearly explain the issue.

 

[GPGeorge]

Spoiler: etc.

I currently have an HR database which contains as you would imagine employee information. It also contains training records, and most recently I am adding wages and raise history. My questions are more related to the attached pdf. We use this excel sheet internally for payroll and we use Quickbooks T-sheets to record payroll and download a csv file weekly from that system to record into what we have named the checkerboard. I would love to import the data from Tsheets into the database and push a button to have it calculate everything. Id like to also preface this with yes I know payroll systems have to conform to certain standards. This report I am trying to create is entered into our payroll system after upper management approves it. Strictly internal.

Lets say tblTsheetsRecords to import Tsheets data again linking on EmployeeID... Then I can easily import job names and I have current wages covered what I am debating is how to structure the tables when it comes to:
1.) Deductions (health, 401k, and other) One table: employee ID, deduction name, amount, effective date.
(^ seems real simple now that I write it out - I should create tblDeductions with FK EmployeeID
2.) Certified Payroll: some jobs may have certified payroll in which say my employee makes $22/hr well the "Painter" category pays $27/hr so now I need hours on that particular job to be overridden by the certified payroll rate while hours that the employee works anywhere else to remain at his/her current rate. I am thinking this has to be a separate table in which I can enter the job ID and category and corresponding rate since jobs will have multiple categories then build an ifthen to control which rate is implemented?
2a.) Additionally I need to be able to accurately calculate overtime. Say Monday - Thursday the employee is working the CP job then Friday/Saturday they are at their regular rate I need to calculate overtime on the regular OT rate and not CP OT. This is all manually controlled in the excel file. You have to move hours from one section to another. I am completely lost here on how I would go about achieving this?
3.) Lastly at the conclusion of the checkerboard completion I am then transferring the total hours worked minus PTO and any holiday hours to a separate spreadsheet to calculate PTO "paid time off". Most employees earn 1 hour of PTO for every 30 hours worked 40 hours max in a year, if you have 10 years of service you then earn 1 hour per 23.75 and max is 80 hours a year. I have everyone's start date so I should be able to calculate in the database which rate the employee should be earning at. However, say I have one employee is on the cusp of 10 years I wouldn't want that event to throw off the calculations so I am thinking this calculation needs to be completed in the table? Which I know is generally frowned upon and calculations should happen in queries which is my hesitation.

Any advice is appreciated.
Thanks in advance!

This reads like a specifications document, more or less. And from that a consultant could spend several days working with you to pin down each of the elements mentioned. Since we don't have that kind of time to devote, perhaps you can focus on one, specific problem that has you puzzled.

 

[Samantha]

I suppose my first and second points Ive talked myself through. I just want to be reassured that I am not way off base. 2a I have the structure part covered and can proceed without fully thinking through the end result. As much as that is opposite to my personality I suppose I will live. #3 though - under these circumstances not wanting the information to change based on a date query would it be appropriate to run the calculation in the table? or is there a better method.

 

[Pat Hartman]

Usually when calculating labor for a job, you use a fully burdened rate based on the job category rather than the actual pay rate. You may pay someone $25 per hour and offer benefits (sick, PTO, vac, ins, etc) equal to $15 per hour but there is more that goes into that calculation than just those two figures. So, rather than try to calculate this on the fly, the bean counters come up with a rate of $40 per hour and use that for figuring the labor cost for a painter. The fully burdened rates are recalculated annually so they stay current. So, for payroll and OT calculation, you use the $25 rate, for taxes you use the $25 + $15 rate, for cost of goods sold you use the fully burdened rate.