Payroll Database

[kattz]

I have an employee database already set up with their personal details, work details, training details which i am currently trying to normalise

What i need is a form where i enter start time and end times for each day of the week - then it needs to calculate different things like overtime, meal money etc. i have the formulas for these calculations already as there are a lot of variables. The variables depend on what dept/div they are in and on what rate and grade they are

I currently have a time card table which has fields called Emp Id, Mon Start, Mon End, Tue Start, Tue End, Mon Base, Tue Base, Mon timehalf, Tue timehalf, mon double, tue double etc.

i know this isn't normalised but trying to get it to work normalised and end up with a form which is simple to use is working out to be quite the problem. this has actually worked up until now - but the query for the form is now to complex because i added another formual.

does anyone know of a similar database sample that i could use

someone has suggested using a cross tab query but i don't understand how they work and can't even figure it out from the help either.

i can post any of the sql i have but it probably won't be any good as my tables aren't normalised yet.

thanks

 

[AccessGuruCarl]

I have built a similiar database.

Lets start with, what is the Format for each field.
How are you storing them? Date/Time, Integers ect..

What are these fields MonBase, Mon timehalf, mon double ...
How are you storing them?

Have you thought about this...

EmpId, TC_Date, StartTime, EndTime, EmpBasePay?, TC_TimeHalf, TC_Double

If MonBase is Mondays Base Pay & TueBase ect.., then EmpBasePay wouldn't be included in this table.

New Table...

tbl_EmpDailyPay
EDP_ID AutoNumber - PK
EmpID Number - FK
MonBase Currency
TueBase Currency
...
...

This will get you started in normalizing the db

I hope you do realize, you'll be recreating almost every query you have. Most likely...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[willir]

kattz

I am going to assume you have the main part normalized -- employee master file, paygroups perhaps, etc.

I am also goint to assume that this database is for tracking hours worked and will not be used for printing pay cheques or making bank deposits. (You already know that there are applications out there plus many banks are willing to do this job for you ... and the rules for payroll deductions and calculations are very complex ... and regardless of country, the federal government and employees can come down pretty hard on an employer who does not follow the law.)

Okay, instead of making a table which includes each possible combination of pay days and pay rates, you should break this down to a master table and detail table.

tblPayMaster
PayMasterID - primary key, can be autonumber
EmployeeID - foreign key to employee table
WeekEndingDate - date field
PayGroup - text - weekly, biweekly, monthly type of thing
Committed - yes / no

Discussion:
Committed would be a field that is used to "lock" the record to prevent changes to the record after processing.

tblPayDetail
PayDetailID - primary key
PayMasterID - foreign key to Pay masetr table
DayWorked - date field - or text with Mon / Tue / Wed ...
HoursWorked - interger
PayType - text - Regular, Time&Half, doubletime

Discussion:
Note: One entry for one day at one pay type - one record.

DayWorked - you can use the date field to capture the specific date, or you can use a text field and capture MON, TUE, WED, etc. Probably the best solution is to use the DATE, and format it as "DDD" or "DDDD".

PayType is where you capture the pay scale used - regular pay, time and a half, etc. Note that if an employee is paid for regular pay and overtime, there needs to be two entries.

PayType - You may want to consider how you track absences. You can track it here and use a code for absences. However, this will only give your hours and days abesent, but will not give you incidences - sick for 3 days only counts for one incident. You can also use this same field to capture vacation.

...Moving on
With this design, you have a couple of options in how you control data entry, and user friendly options...
- The data entry enters each record. Tedious, and the end user has to know which date to enter for the Monday, Tuesday...
- Have the end user enter the EndDate for the pay and then the end user clicks on command button, and some VBA coding generates the required pay entries (5 days at 8 hrs, or 3 or 4 days for 12 hr shift). The end user then just has to enter the horus and paytype or rate for each day worked. (missing or null entries are assumed to be no hours worked)
- You can add another command button that populates the detail records with default hours for the pay group.

This type of approach, which I have used elsewhere, is much more effecient than creating a large table used to catch all info which leaves many fields empty, and requires incredibile long and complex queries to find the required info.

Richard