One approach would be to have a main table to store the fact that a payment
was made to an employee on a date, and a related table for each line item in
the payment. The 'overtime' would be a separate line item.
tblPay
PayID AutoNumber
PayDate Date/Time (date of the payment)
EmployeeID foreign key to tblEmployee.EmployeeID
tblPayDetail:
PayID foreign key to tblPay.PayID
PayTypeID lookup values 'Regular', 'Overtime', ...
Hours number (Double)
PayRate Currency (dollars per hour)
You may also need an EndDate or date range in tblPayDetail, e.g. if you need
to pay ahead for 'Vacation' pay.
Then it is a matter of programmatically looping through all the timesheet
records that have not already been paid, up to the PayDate, creating records
in these 2 tables, and flagging each timesheet entry as paid. This process
would typically need a batch number, so you can undo the last batch, fix the
timesheets, and have another shot at it if necessary.
Your interface should lock the timesheet entries that have already been
'paid' so they can't be modified. If a correction is needed after the pays
have been administered, it should be by way of a reversal in the next pay
rather than allowing edits of the timesheets. (There will be some extra code
to get this right, e.g. if the timesheets recorded that the guy worked 42
hour week and so you paid him 40 hours regular + 2 hours overtime, and then
it was discovered that a timesheet was duplicated and he worked only 37
hours, you need 2 reversal entries: one removing 2 hours overtime, and
another removing 3 hours regular.)
Hope that at least takes you in a useful direction.

Signature
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
> Hey,
>
[quoted text clipped - 29 lines]
>
> Ernst
Ernst Guckel - 16 Jul 2005 13:39 GMT
Thanks for the info. This approach is very helpful but I am concerned about
the data entry aspect of each weeks payroll... Unfortunatley I need to create
a spreadsheet like data entry form with employees down the left and ALL
available hour types going along the top... Just not sure how to set this up
being that the table design is setup with a paytype as a seperate record
line...
Ernst.
> tblPay
> PayID AutoNumber
[quoted text clipped - 60 lines]
> >
> > Ernst
Allen Browne - 16 Jul 2005 15:09 GMT
I don't think I would approach the table design like that.
"All available hour types across the top" means what? That it is a graphical
layout where if the person works from 9am to 5pm, you draw a line from 9am
to 5pm? Somehow I can't imagine that kind of structure coping adequately
with all the possible scenarios that could arise, e.g. where someone has to
leave at 3:25pm for some emergency, and then returns at 4:40 to resume the
last part of the day, or where someone works past midnight into the next
day.
Even if it is possible to create an interface that works as you describe,
I'm not sure that should dictate the data structure of the tables.

Signature
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
> Thanks for the info. This approach is very helpful but I am concerned
> about
[quoted text clipped - 81 lines]
>> > have
>> > advice on which way to go?
Ernst Guckel - 16 Jul 2005 15:56 GMT
No. Not like that... Each week we punch in TOTAL hours in the appropriate
catagories:
Name PayRate Hours OT Vacation Sick
John 8.00 40 2.5 0.00 0.00
This is the data entry aspect of what we are looking for. I understand that
the table design is first. I know how it is supposed to be done but should
the Customer's request for form interaction play some part as well? I just
don't see how I can make the data entry form work with your recomended table
design. Maybe I am just missing something...
I see your design recomendation as the right way to go jus tstuck on how to
implement such a design in the user interface aspect of the project.
Ernst.
> I don't think I would approach the table design like that.
>
[quoted text clipped - 94 lines]
> >> > have
> >> > advice on which way to go?
Craig Alexander Morrison - 16 Jul 2005 18:38 GMT
Ernst the solution, well one solution that I have used is to create a table
or tables to accept the Data Entry in a simple format.
Then following the completion of the data entry the screen has a update
button or you tie it into the close function. This function would process
the data in the temporary table(s) via a series of queries to turn the
flat-wide data in the entry table(s) to the correctly structured deep-thin
data in the database along the lines already discussed.
You would need to ensure that the data entry screen had the necessary rules
in the AfterUpdate of fields to ensure that correct data for that field is
entered to avoid/reduce issues arising when the data is absorbed into the
relational database. These temporary tables maybe best implemented as a
separate MDB on the network to keep them from being confused as part of the
central relational database.
This approach is not a trivial undertaking but it can be a very effective
solution.

Signature
Slainte
Craig Alexander Morrison
> No. Not like that... Each week we punch in TOTAL hours in the appropriate
> catagories:
[quoted text clipped - 137 lines]
>> >> > have
>> >> > advice on which way to go?
Ernst Guckel - 16 Jul 2005 16:38 GMT
Come to find out 'Batch Payroll' Is what I am trying to do. Didn't know thee
was a term for it :)
Ernst.
> I don't think I would approach the table design like that.
>
[quoted text clipped - 94 lines]
> >> > have
> >> > advice on which way to go?
Allen Browne - 17 Jul 2005 06:16 GMT
Does Craig's suggestion help?
That approach makes sense if you are forced to go that way.

Signature
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
> Come to find out 'Batch Payroll' Is what I am trying to do. Didn't know
> thee
[quoted text clipped - 120 lines]
>> >> > have
>> >> > advice on which way to go?