I need to create a solution that will track hours worked for an hourly employee and create a weekly pay roll pay amount as well as relate to a previous Jobs table and create weekly pay amounts for two independent contractors (three if you count me, I am on the same pay schedule).
So I need
1) An easy time card solution. This I am pretty sure I know how to do with one record being one day of work for one employee. I could even put the ability to have a split shift into this table, though at this time that is not needed. But I am open to thoughts on the best way to do this.
2) This part gets tricky for me - It then need to total by week and tell me how much to pay each employee (which at this time is one but will hopefully be more in the future). So one payroll record per week, per employee. This table would ideally run a script to email the employee at the time I mark that week as paid and send them a line item of the hours worked and the check amount.
3) And this is the part that is really confusing to me as to how to set up. The payroll table needs to also relate to a jobs table (that I can do) and look up record pertaining to that weeks time period and get data from three different fields and place those amounts and positions on each independent contractors payroll record. Let me explain this in a little more detail. The jobs table tracks work by position 1st shooter, 2nd shooter, photo editor. and the amount of that job that is paid to the contractor for whichever position they worked.
So on in that time period job 001 I may be the 1st shooter and get paid $500 while Robin may be the photo editor for that job and may be getting paid $125
Job 002 Robin may Be the 1st shooter and get paid $480 Spencer the Second shooter and get paid $185 and Robin may also be the editor and get paid $105
SO this section of the payroll database needs to give me three records for that week
Brendan with job 001 1st shooter $500 total $500
Robin Job 001 Photo Editor $125 job 002 1st shooter $480 Photo Editor $105 Total $710
Spencer Job 002 2nd Shooter $185 total $185
And then email each of us respectively, again when jobs are marked as paid, the line items for that weeks pay and the total.
And to complicate this all a little further, it is possible that at some point in the future an independent contractor could also have a day or two where they were doing office work and were payed hourly... So that would also need to be a line item on that weeks payroll record for them.
In the past I have been tracking all of this on multiple spreadsheets, and we are starting to get busy enough that figuring out a filmmaker solution is less of a headache then tracking this on spreadsheets and in some cases having the bank send a contractor multiple checks in one week...
SO, how do I get started?!?!?