New Database

[Guest]

I am building a new database for employees and their trianing requirements
for each job.

Okay, there are procedures that is required for each job. These procedures
appear to be requirements for many jobs but at the same time there are other
procedures that do not cross jobs.

I originally created one table for each job procedure requirements but am
coming across a headache. There are about 40 jobs with different required
procedures.

Can someone guide me in setting it up a little more efficiently?
Thank youfor your help.
Judy

 

[Jeff Boyce]

Judy

One of the sometimes-confusing parts of setting up/using a relational
database (e.g., Access) is the new mind-set required.

From your description, you have Employees, Procedures, and Jobs (or any
other category titles you'd care to use). It sounds like any given job can
have multiple procedures, and any given procedure could (not must) have
multiple jobs to which it applies. This would be a many-to-many
relationship, for which you'd need an additional table in Access.

Also (but not really mentioned in your post), I'm guessing that an Employee
can hold one Job only at a time, but that you'd want to know, over time,
what Jobs a given Employee has held. This implies another many-to-many
relationship, plus extra table.

I'm imagining the following table structure, based on how I'm understanding
your description:

tblEmployee
EmployeeID
FirstName
LastName
DOB
... any other person/employee-only data

tblJob
JobID
JobTitle
JobDescription
... (any other job-...)

tblProcedure
ProcedureID
ProcedureTitle
ProcedureDescription
... (any other ...)

trelJobProcedure
JobProcedureID
JobID (this is a foreign key field, pointing back to JobID in
tblJob)
ProcedureID (this is a foreign key ...)

?trelEmployeeJob
EmployeeJobID
EmployeeID
JobID
StartDate
EndDate

I hope I haven't read too much into your post...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Okay Jeff, Thank you and great advice.

I have my setup as such:

tblProcedures
Procedure_ID Primkey
Procedure Title
Procedure Description

tblJob_Titles
ID-Primkey
16 names
Mixed Waste Engineer
Dosimeter TEchnician, etc.

tblEmployees
Employee_ID
First_Name
Last_Name
Supervisor

tbl_Job_Procedure
ID-PrimKey
Job_Procedure_ID
Job_ID-Combo Box-looking back at tblJob_Titles
Procedure_ID

tbl_Employee_Job
ID-PrimKey
Employee_Job_ID
Employee_ID
Job_ID-one to one on tbl_Job_Procedure Job_ID

Now with these setup do I want to put all the procedures in the
tbleprocedures as a combo box and they can pick the procedure per job that
they need?

In addition, do i want the forms to be nested for ease of use or do I want
them seperate to click a command button?

I also need help for the procedures...There is a knowledge requirement that
they have to choose from: 1-Unable to perform, unaware or deficient;
2-Familiar, has understanding or can perform but needs help; 3-adequate, can
consistently perform, proficient; 4-fluent, outstanding understanding, can
teach

I certainly do appreciate all the feedback on this issue. You are greatly
appreciated.

 

[Guest]

Jeff,

I am not understanding what you mean by pointing back to a particular field
in a table, do I want to do a lookup field or just do the relationship thing?