View Single Post
  #20  
Old May 23rd, 2006, 05:01 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default I think I'm almost there...except for...

(A) I would rather use PayrollID as employee ID since that is unique

I'm not sure what you mean by this. EmployeeID is intended to uniquely
identify a single employee. PayrollID is intended to uniquely identify a
payroll entry. Each PayrollID is linked to an EmployeeID to identify which
employee is getting paid.

(B) I would like to distinguish between contracted hours and overtime.


Can you describe the difference between contracted hour and overtime hours?
If you can come up with a specific definition of the two, then it can be
programmed into the database. I was thinking that each payroll entry could
be designated by a PayrollType, which would have entries of either
"contracted" or "overtime." But each entry into your Payroll table would
have to be designated as one or the other. You could have the default entry
be whichever one occurs most frequently, and just change it for those times
that it should be the other one.

(C) I need to be sure that the hours entered relate to the correct department.


In your Payroll table, you have a foreign key of Subdepartment. Each
payroll entry should include a reference to the appropriate subdepartment,
which then defines the department. This means, though, that if a person
works for 20 hours in the business department, 10 hours in shipping, and then
10 hours in advertising, that you will have 3 entries in the payroll table
for that person for that week (week 1). So, if the tables look like this:

tbl_Employee
EmployeeID (PK)
Employee
Status
Rate

tbl_Payroll
Payroll (PK)
EmployeeID (FK) [this needs to match the corresponding ID in your Employee
table]
SubdepartmentID (FK) [this needs to match the corresponding ID in your
subdepartment table]
WeekID (this is probably a number to designate which week of the year)
Amount (I suppose this is the amount of time worked, so that you can
multiply this by the rate)

tbl_Subdepartment
DepartmentName (there will be duplicates here, but probably not very many)
SubdepartmentID (PK)

Your tables could have these entries:

Employee table
EmployeeID, Employee, Status, Rate
1, John Doe, Full-time, 7.50

Subdepartment table
DepartmentName, SubdepartmentID
Business, 1
Shipping, 2
Advertising, 3

Payroll table
Payroll, EmployeeID, SubdepartmentID, WeekID, Amount
1, 1, 1, 1, 20
2, 1, 2, 1, 10
3, 1, 3, 1, 10

You would then set up a query that would include the appropriate fields,
making a field to be passed on to the form to calculate the total pay, based
on rate times amount.