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
|
|||
|
|||
Design questions for tracking employee hrs & client billing
I need to design a database system that will allow employees to enter their work hours and the project number. I also want to be able to query the employee info to build reports showing hrs worked on each project over a period of time. We have less than 20 employees & about 100 active projects. I already have a table of projects. In two weeks time each employee may have upto 50 or more entries on a time sheet. My questions a do I create a table for each employee? Is it possible for the input form to send info to multiple tables? Please help. Thank you
|
#2
|
|||
|
|||
Design questions for tracking employee hrs & client billing
Hi Jeff,
You'll need probably just three tables. You certainly don't need one table per employee or one per project. : tblEmployees (one record per employee) EmployeeID LastName FirstName perhaps other information about the employee tblProjects (one record per project) ProjectNumber ProjectName perhaps other information about the project tblHoursWorked EmployeeID (foreign key into tblEmployees) ProjectNumber (foreign key into tblProjects) DateWorked (date/time, the day the work was done) TimeWorked (a number(single) or date/time field to store the number of hours that employee worked on that project that day) On Sun, 23 May 2004 13:11:02 -0700, "JPearson" wrote: I need to design a database system that will allow employees to enter their work hours and the project number. I also want to be able to query the employee info to build reports showing hrs worked on each project over a period of time. We have less than 20 employees & about 100 active projects. I already have a table of projects. In two weeks time each employee may have upto 50 or more entries on a time sheet. My questions a do I create a table for each employee? Is it possible for the input form to send info to multiple tables? Please help. Thank you -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#3
|
|||
|
|||
Design questions for tracking employee hrs & client billing
John, thanks for the reply. We have that accomplished. We have created a query from these tables and have a report showing 1 days work, total hrs for payroll, not client billing. Can you give me an idea on how to create a 2 week, time sheet summary? The summary sheet should show vacation, sick, total billable hrs, staff mtgs, etc, by day. This report will be printed, given to bookkeeping and used as a timecard for payroll (entered into quickbooks by clerical staff). I am thinking that querying each day by each work category (vacation, sick, bill, nonbill etc) and inserting into a single report summarizing a 2 week pay period is to complicated for my brain. ??? Thanks again for the help.
|
#4
|
|||
|
|||
Design questions for tracking employee hrs & client billing
Hi Jeff,
The general idea for all "timecard" type reports is to start with a query that returns the data you need to collate, and then use a crosstab query based on that to assemble the data into a "grid" (e.g. with job codes down the side, dates across the top, and total hours at the intersections. Finally base a report on the crosstab query. On Sun, 23 May 2004 14:26:03 -0700, "JPearson" wrote: John, thanks for the reply. We have that accomplished. We have created a query from these tables and have a report showing 1 days work, total hrs for payroll, not client billing. Can you give me an idea on how to create a 2 week, time sheet summary? The summary sheet should show vacation, sick, total billable hrs, staff mtgs, etc, by day. This report will be printed, given to bookkeeping and used as a timecard for payroll (entered into quickbooks by clerical staff). I am thinking that querying each day by each work category (vacation, sick, bill, nonbill etc) and inserting into a single report summarizing a 2 week pay period is to complicated for my brain. ??? Thanks again for the help. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|