Well, thankyou for that.

I will do as you suggest, but I can't see the point of a separate table for
subdepartment.

"mnature" wrote:

No, you're still not even close. Tables, when properly normalized, will
contain information that is related to each other. You will then link the
normalized tables in ways that will allow you to build queries, forms, and
reports. If you do the tables wrong, then you will always have problems with
everything else.

You need a table that is JUST employee information. This will be: Name,
address, phone, full-time, part-time, hourly wage, etc. Don't put anything
into this table unless it relates directly to the employee.

tbl_Employees
EmployeeID (PK)
EmployeeInfo
EmployeeStatusInfo
EmployeeRateInfo

You need a table that is JUST payroll information. This will be: Link to
employee being paid, link to subdepartment paying the employee, the week
during which the employee worked that they are getting paid for, and whatever
multiplier is needed for this particular amount (1.0 for straight pay, 1.5
for time-and-a-half, and 2.0 for double time). Don't put anything into this
table unless it relates directly to the payroll. You might have multiple
entries in this table for any particular employee during any particular week,
because they could work for several different departments during that week,
or they could work for one department but part is straight time and part is
overtime. This is where you relate an employee, a subdepartment (which then
relates to a department), and a payroll amount. You will end up with a whole
lot of entries in this table.

tbl_Payroll
PayrollID (PK)
EmployeeID (FK)
SubDepartmentID (FK)
WeekNumber
PayrollAmount
PayrollMultiplier

You need a table that is JUST for information about your departments. Don't
put anything into this table unless it relates directly to the main
departments.

tbl_Departments
DepartmentID (PK)
DepartmentInfo

You need a table that is JUST for information about your subdepartments.
This will include a link to the main department for a particular
subdepartment. Don't put anything into this table unless it relates directly
to the subdepartments. It is through the subdepartment that you get a link
to a department.

tbl_SubDepartments
SubDepartmentID (PK)
DepartmentID (FK)
SubDepartmentInfo