Daily Diary

[duchessofireland]

I am trying to turn an actual paper diary and turn it into an electronic
diary. In this paper diary we write down every employees name and what they
did each day, where the location was and what the cost code is for each job
they do in a day. I need this to be "searchable" in the future. Maybe need
to see what "joe blow" did on Tuesday, May 6, 2008. Need to be able to
search by employee, by date or date range and possibly by location. Is this
achievable in Access? Sounds like it could be - not sure. Can you help me?
Any ideas of how to set this up? Pretty new to Access and need instruction.
Thanks so much.

 

[Klatuu]

First, you need to establish the business rules.
For example, it appears an employee can work on more than one job in a day,
so you need to answer the following questions:
Can one employee work on more than one job on one day?
Can an employee work on the same job for more than one day?
If an employee works on the same job for more than one day, will it be one
entry for the job, or one entry for each day? Do the days on that job have
to be consecutive to be counted as one day?
Can a job have more than one cost code for one employee for one day?
Can a job be in more than one location on one day?
Does a cost code relate to the job or to the location?
Do you need to record the time of day an employee started and ended on a
job/location and/or the time duration of the job/location?
If a job spans locations, how does that affect the time recording?
If a job spans locations, how does that affect the cost code recording?
If a job has more than one cost code, how does that affect the time recording?
If an employee is absent on a day that is a work day, how do you record that?
Can more than one employee work on the same job on the same day?
Can more that one employee work on the same job at different locations?
If more than one employee works on a job, how does that affect reporting?

The point is, before you decide what tables and fields you need, you first
need to fully describe the business rules. Once you do that, then you can
decide what tables and fields you need to store the data to support the
business rules.

 

[duchessofireland]

Basically - yes. An employee can work on several different jobs in one day
even jobs that will have the same cost code - just at a different location.
They can also work on the same job for several days. I just want to have a
daily diary. Say I can look up a particular day and find out what every
employee did that day. Or look up a location and see what day it was worked
on and who worked on it. I do not need to track how long a job took. Here
is what my handwritten diary looks like now.

"Date" - "Employee" - "Activity performed" - "Cost Code"

How do I set up my tables to be able to link all this information together?
Thanks for all your help

 

[Klatuu]

You need 4 tables.
tblDailyDiary
DiaryID - AutoNumber PK
DiaryDate - Date
EmployeeID - Long Integer - FK to tblEmployee
JobID - Long Integer - FK to tblJob
CostID - Long Integer - FK to tblCostCode
JobNotes - Memo

tblEmployee
EmployeeID - AutoNumber PK
EmployeeName - Text
(Any other fields you may want to have for an employee

tblCostCode
CostID - AutoNumber PK
CostDescription - Text

tblJob
JobID - AutoNumber PK
JobDescription - Text

The relationships should be
tblDailyDiary -> one to many -> tblEmployee
tblDailyDiary -> one to many -> tblCostCode
tblDailyDiary -> one to many -> tblJob