Timesheet Application


G

Guest

I’m struggling designing a time log database.

Each employee currently makes a separate Excel spreadsheet for each 2-week
period, manually filling in the name and number of each project on a separate
row. The dates appear as columns, each being calculated as an offset from
the first.

Its format is quite easy to use for the employees, however, a secretary is
spending a great deal of time collecting the total project hours per employee
manually, applying their different billing rates to them, and producing a
report that the principal uses to decide what the client’s monthly invoice is
to be.

Part of the current mess is that the timesheet is on a two-week cycle while
the billing occurs monthly, so that on timesheets that overlap the month,
some of the hours have been billed, and others not.

The following normalized table structure seemed a good Access solution.
Using queries, I could present the total hours per employee per project
outstanding, and a total dollar amount based on their billing rates. He
could then decide on the invoice amount (printed now from Word, although
likely to be incorporated down-the-road) and press a button that would toggle
the BilledStatus to Yes.

Employee
EmployeeID AutoNumber (PK)
FName Text
LName Text
BillingRate Currency
…others

TimeLog
TimeLogID AutoNumber (PK)
EmployeeID Integer (FK to Employee)
ProjectID Integer (FK to Projects)
TimeLogDate Date/Time
HoursWorked Integer
BilledStatus Yes/No

A main form based on Employee and a subform based on TimeLog is the most
straightforward, but such an arrangement loses the convenience of the current
method where the employee enters each project once, and the hours into a grid.

Does anyone have any ideas? Could a crosstab query or a pivot table be
used?
 
Ad

Advertisements

G

Guest

I am not the expert and most people in this NEWSGROUP are much smarter than I
however I would suggest that you try using the Microsoft Date function that
counts weeks. It categories things as 52 weeks a year and not a monthly or
BI Weekly function then you can query on Weeks by the two week period.
Another method I have used is to use a field that uses a Pay Period Ending
date with 26 pay periods. These end on the end of the month for the PAY
PERIOD when it ends during the middle of the week.

Either metod workds well with queries and brings back the right information.

GOOD LUCK....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top