Time and Attendance Coding database (1 Viewer)

[bradles]

Hi All,

I am setting up a database to help me prepare codes for employees timesheets in order to upload them into our payroll software. The table structure below is just my preliminary thoughts and current ideas and I guess I'm looking for ideas on how to work with my codes.

EMPLOYEE TABLE
EmpID (PK) - Employee ID # [Autonumber]
Surname - Employee's surname [Text]
Firstname - Employee's first name [Text]

ATTENDANCE TABLE
ShiftID (PK) - Shift ID# [Autonumber]
EmpID (SK) - Employee ID# [Foreign Key]
Date - Date of shift [Date/Time]
Start - Start time of shift [Time]
Finish - Finish time of shift [Time]
CostCtr - Cost centre being billed for shift. [Integer]

When employees work they are entitled to the following:
* Ordinary hours (code 001) for all hours worked.
* 10% penalty (code 006) for all hours worked when shift finishes after 18:00
* 12% penalty (code 007) for all hours when shift crosses midnight
* 50% penalty (code 008) for hours worked on a saturday
* 100% penalty (code 009) for hours worked on a sunday

The following shows data that in my Attendance table for an employee who worked shifts on the 16th (Mon), 17th (Tue), 20th (Fri), and 22nd (Sun).

ShiftID		EmpID	Date		Start	Finish	CostCtr
1		84434	16/10/06	10:00	19:00	
2		84434	17/10/06	10:00	19:00	3002
3		84434	20/10/06	22:00	06:00	3001
4		84434	22/10/06	14:00	22:00

From the above data I believe I will need to make another table that contains the entitlement codes generate from each shift.

For the first shift on Monday 16/10/06 I need to collect the following codes for the total calculation:
Code	Hours	CostCtr
001	9.00	
006	9.00

For the second shift on Tuesday 17/10/06 I need to collect the following codes for the total calculation:
Code	Hours	CostCtr
001	9.00	3002	
006	9.00	3002

For the third shift on Friday 20/10/06 I need to collect the following codes for the total calculation:
Code	Hours	CostCtr
001	8.00	3001 'Ordinary hours worked	
007	8.00	3001 '12% penalty as shift crossed midnight hour
008	6.00	3001 'Only worked 6 actual hours on the Saturday as 2 hours were on Friday night.

For the fourth shift on Sunday 22/10/06 I need to collect the following codes for the total calculation:
Code	Hours	CostCtr
001	8.00		'Ordinary hours worked	
009	8.00		'Hours worked on the Sunday

From that information the only data I really need to store in a table would be the totals grouped by code and cost centre. Eg.,

Code	Hours	CostCtr
001	17.00	
001	 8.00	3001
001	 9.00	3002
006	 9.00
006	 9.00	3002
007	 8.00	3001
008	 6.00	3001
009	 8.00

Does anyone know the best way to go about this? Should I generate a new table that links these codes to an employee? Should I make a function to calculate the codes for each day and store them in a table or make the function only sum the code totals for the week and store them in a table?

Brad

 

[grnzbra]

I think that I would create a sub that takes in the start date and time, the end date and time, the employee number and the cost center. Have this sub determine the hours and codes and load them into the a table. You will have to make provisions for this to load multiple entries for a single day, since you could have a shift crossing into midnight into saturday. You will have to figure out the logic to give you the proper codes, but once you have these into a table, summing them by code and cost center should be easy. The same applies to calculating the amount of pay for each employee.