Need Design Help for payroll tracker for about 150 employees

[Trevor W]

Hi there,

I have been self teaching myself Access for the last 5 years. I have been
good except that I have to rebuild my Access 2003 database each year. I have
try clearing the data and copy to a new file name, but each year something
happens. I would like to design it correctly and be able to have multi year
data in the system.

Here is the design:
We have employees about 150 people for 2 months of the year. Most only work
a week to a month with us doing one project. Payment right now is at the end
of the project, so my table for each project method has worked. Now the
owner would like to have the ability to pay either monthly or bi-weekly.
This would help with advances that are given sometimes and are not entered
into the system correctly.

I have 4 projects:
Hourly (Main workers, like supervisors and delivery person)
Rhubarb (Also Hourly, but owner would like separate to keep track of labor
hours)
Strawberry (Paid by the box)
Blueberry (Paid by the lb or bucket, same method each year, one year might
be by the lb, the next by the bucket)

Each employee will have an employee Number assigned to them and the year
that they worked. This table would also have their Name and address info,
Hired and Laid Off date. Another Table would have their phone number and
Date of Birth to have in a central table to move from year to year.

I would like to have the end user enter the data for all the employees that
are working on the project as a daily sheet. For example: Strawberry:
Date Picked 8-July-2009 Basic Rate $0.35/box
then a record with each employee assigned to the project for that year,
including the absent employees as this will effect their bonus at the end of
the project.

So if I get this right:
I should have:
Employee Roster Table
Employee Phone and Date of Birth Table
Project Table
Project Entry Table

Thank-you for your co-operation in advance,
Trevor Wenham
Moncton NB

 

[John W. Vinson]

Hi there,

I have been self teaching myself Access for the last 5 years. I have been
good except that I have to rebuild my Access 2003 database each year. I have
try clearing the data and copy to a new file name, but each year something
happens. I would like to design it correctly and be able to have multi year
data in the system.

Here is the design:
We have employees about 150 people for 2 months of the year. Most only work
a week to a month with us doing one project. Payment right now is at the end
of the project, so my table for each project method has worked.

OW. That's simply *wrong design*. You would have a different spreadsheet page
for each project in Excel, but storing data (a project) in a tablename is
simply incorrect design for a relational database! Instead you would have a
table of Projects with one row per project, with a ProjectID as a primary key;
payments would all be in one table, with a field for the ProjectID.

Now the
owner would like to have the ability to pay either monthly or bi-weekly.
This would help with advances that are given sometimes and are not entered
into the system correctly.

I have 4 projects:
Hourly (Main workers, like supervisors and delivery person)
Rhubarb (Also Hourly, but owner would like separate to keep track of labor
hours)
Strawberry (Paid by the box)
Blueberry (Paid by the lb or bucket, same method each year, one year might
be by the lb, the next by the bucket)

Each employee will have an employee Number assigned to them and the year
that they worked. This table would also have their Name and address info,
Hired and Laid Off date. Another Table would have their phone number and
Date of Birth to have in a central table to move from year to year.

Why a separate table? Each person has a name; they have an address; they have
a phone number; they have a date of birth. These should all be in one table.
What needs to be in a *different* table (related one to many to this table) is
the hiredate and layoffdate; if an employee is hired repeatedly, you'ld leave
their employee record alone (except for changes of address and phone, say) and
add a new record to the hiring table.

I would like to have the end user enter the data for all the employees that
are working on the project as a daily sheet. For example: Strawberry:
Date Picked 8-July-2009 Basic Rate $0.35/box
then a record with each employee assigned to the project for that year,
including the absent employees as this will effect their bonus at the end of
the project.

That's a report or a query, NOT a table.

So if I get this right:
I should have:
Employee Roster Table
Employee Phone and Date of Birth Table
Project Table
Project Entry Table

WorkSession table
Payment table

probably other tables as well.

See the tutorials at

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

particularly the last two.