If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need Design Help for payroll tracker for about 150 employees
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 |
#2
|
|||
|
|||
Need Design Help for payroll tracker for about 150 employees
On Wed, 5 Nov 2008 12:16:04 -0800, Trevor W
wrote: 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/acc...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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|