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?
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?