check design / offer suggestions ? (1 Viewer)

cochese

Registered User.
Local time
Today, 08:11
Joined
Feb 5, 2008
Messages
54
I've been struggling to learn Access for awhile now, which usually forces me to run back into the arms of Excel where I'm secure and happy.

I've tried to create a little database to track job duties for someone and while I feel like I did it correctly, I'm just not so sure. Here is what the goal is and how I did it...

Goal: To track how long it took a person to do certain tasks, and to be able to create assignment sheets that list the tasks a person is meant to do.

Structure:
1) There is a list of employees in the department. Each employee is assigned a job description; however, an employee can have more than one job description.

2) Each task is assigned to a job description, and only to one job description. Each task also has a recommended time to complete.

3) Each task can be repeated several times in a day (i.e. have multiple start/finish times).

4) Even with the structure of 1 - 3, when actual records are made to see how long it took to complete a task and who did it, the record can be for any employee. In other words, if an employee has 2 job descriptions, cook and manager, but they can do a task that is not associated with either of those job descriptions.

Structure Summary: #1 to #3 are basically for creating assignment sheets and reporting. #4 is basically independent, in that it's just a record of who did what task, and how long they took to do it.

I might feel this is wrong because I don't understand exactly how the data is going to be put into the tables (via forms) yet. The data in each table now was hand entered.

================================

I've included my database as an attachment, but for anyone who doesn't want to bother with it, here is what I designed:

EMPLOYEES: Table
employeeID: PK
firstname

RESPONSIBILITIES: Table
responsibilityID: PK
responsibilityName

EMPLOYEES_RESPONSIBILITIES: Table
assignmentID: PK
employeeID: FK
responsibilityID: FK

FUNCTIONS: Table
functionID: PK
responsibilityID: FK
functionName

FUNCTIONSCHEDULE: Table
functionID: PK
StartTime: PK
EndTime

ACTIONED: Table
actionedID: PK
employeeID: FK
functionID: FK
StartTime
FinishTime



THANK YOU!!!
 

Attachments

  • Copy of Hot Foods Fixed Cost.mdb
    312 KB · Views: 151

speakers_86

Registered User.
Local time
Today, 08:11
Joined
May 17, 2007
Messages
1,919
It looks like your heading in the right direction. Can you explain what tblFunction is and why responsibilityID belongs there.
 

dcb

Normally Lost
Local time
Today, 14:11
Joined
Sep 15, 2009
Messages
529
Agreed - speakers
I think you are making a big loop here
They way I understand it is that any employee can be given any Function regardless of Responsibility - think you are going to get "requires an associated record in tblResponsibilities" error

I would use Full date and Time if you plan to make work schedules going forward. It gives you something to filter by.
 

boblarson

Smeghead
Local time
Today, 05:11
Joined
Jan 12, 2001
Messages
32,059
Yes, store DATE and TIME together in the same fields. It will make your life much easier when you go to do things with it. And, from what I see, responsibilityID should not be in the Functions table.
 

cochese

Registered User.
Local time
Today, 08:11
Joined
Feb 5, 2008
Messages
54
tblFunction lists all the functions (i.e. tasks) that need to be done in a day. Each function/task is assigned a job responsibility. There can be an infinite number of functions, but each one has to be assigned to one (and only one) job responsibility. That is why responsibilityID is in there.

The FUNCTIONSCHEDULE table exists because a function can be repeated several times a day. Therefore, function A is associated with responsibility Y, and this AY combination is done 3 times a day.

People can do any function/task, which is why I have the ACTIONED table, but it really is independent of all the other tables, because they exist to list the employees and functions/tasks BUT also to assign people to responsibilities and when a function should be done.
 

Users who are viewing this thread

Top Bottom