payroll table design
Posted on 2004-11-03
I need to develop a payroll database using MSAccess but without VBA.
The database should let me enter employee details and time sheet info, and then calculate the amount due for each employee for a range of working days. The payroll should store the payment details, issue payment slips and also generate reports on the total amount of wages paid for a range of working days.
So far I managed to create two tables
EMPLOYEE(EmpID, Name, Position, Rate)
TIME(TimeID, EmpID, Date, TimeIn, TimeOut)
and generated queries to calculate the amount due for each employee given the first and last working day(entered using parameters). However, when i tried to add another table to store payment details I got stuck. I added a new table
PAYMENT(PaymentID, EmpID, TimeID, Payment Date, Date From, Date To, Hours Worked, Amount Paid, Payment Type, Cheque No)
and used the following onetomany and manytoone relationship
Employee ---------------------------<- Payment ->-------------------Time
Somehow i can't manage to calucate the hours worked using the expression builder, even though i managed to calculate it before using a query. I'm not that certain either of the table design... can someone help please :]