Design questions for tracking employee hrs & client billing

[JPearson]

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 are: 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]

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)

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 are: 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

 

[JPearson]

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]

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.

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.