Table design for Timesheet Application

P

Philippe Pons

Hi,

I have to design a timesheet application.
Th epurpose is to store a worker, a project, an accounting code, a date and
a duration.
How would arrange the tables and their relations to do that?
TIA,
Philippe
 
A

Arvin Meyer

Philippe Pons said:
Hi,

I have to design a timesheet application.
Th epurpose is to store a worker, a project, an accounting code, a date and
a duration.
How would arrange the tables and their relations to do that?
TIA,
Philippe
Click to expand...


tblEmployees - a list of employees
EmployeeID - Long
LastName - Text
.... other fields

tblProjects - a list of Projects
ProjectID - Long
ProjectDescription - Text
.... other fields

tblAccountingCode - a list of your accounting codes and their meanings for
the various projects.
Code - Long
Description - Text

tblTimeSheet
ProjectID
Code
EmployeeID
WorkDate - Date\Time
WorkDuration - Double
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
P

Philippe Pons

Thank's Arvin...
Then tblTimeSheet stores the worktimes for an employee.
In my application, an employee can register several work duration for the
same day but for different accounting codes.
I then guess that the primary key of tblTimeSheet must be made by the
concatenation of ProjectID, EmployeeID, Code and Workdate.
Could you confirm this is the right way of doing?

Thanks a lot,

Philippe
 
N

Naresh Nichani MVP

Hi :

You can have a Worker table with a One To Many relationship with a Projects
table.

Table tblWorkers with Fields
WorkerID - AutoNumber or Number or some unique Indentifier
WorkerName - Text (255)
Add Other Worker fields like Address, City State Zip

Table tblProjects with Fields
ProjectID - AutoNumber or Number or some unique Indentifier
WorkerID - links to WorkerId in tblWorkers - same data type.
ProjectName
AccountingCode
ProjectDate
Duration

Hope this helps.

Regards,

Naresh Nichani
Microsoft Access MVP
 
A

Arvin Meyer

Instead of concatenating a value for the primary key, use a compound index.
If the 4 columns are contiguous in the table design, just select them all
and click on the key button. If not, open the index dialog and type an index
name, then add the 4 fields.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top