Intricate Relationship....

[Guest]

I am envisioning 3 tables - Table 1 containing names of all employees, Table
2 containing the employees with each of the jobs they are trained for, and
Table 3 that contains a list of documentation for each job.

I understand how to create a relationship between Table 1 and Table 2 as
well as the relationship between Table 2 and Table 3.

Each year the employees are required to review the documentation for each
and every job they are trained to perform. How can I maintain a history of
when they've reviewed the documents each year.

My only thought so far is that Table 1's structure would be something like

EmpName
JobName
Document
ReviewDate

Downside of this is Job #1 might have 17 documents associated w/it so I'd
have to re-enter the JobName 17 times for this employee. That's just one
job. Some employees are trained on dozens of jobs so my data entry
requirements would be a nightmare. The pupose of creeating a relationship
btw Table 2 and Table 3 is to eliminate that but I can't figure out how to
store a historical record of the dates each employee reviewed the required
documents.

Any thoughts? Or is this too complex for ACCESS?

Jeff

 

[tina]

i assume that the documents are directly related to the jobs, as in "one job
may have many documents". and each employee is required to review those
documents associated with each job s/he is trained for, once a year. you
don't mention if two jobs may have a document in common, or if each document
is unique to the job it's associated with - so i'll assume the latter.
based on the above, suggest the following tables:

tblEmployees
EmpID (primary key)
FirstName
LastName
(any other fields that describe an employee)

tblJobs
JobID (primary key)
JobName
(any other fields that describe a job)

tblJobDocuments
DocID (primary key)
JobID (foreign key from tblJobs)
DocName
(any other fields that describe a document)

tblEmpJobs
EmpJobID (primary key)
EmpID (foreign key from tblEmployees)
JobID (foreign key from tblJobs)

tblDocumentReview
ReviewID (primary key)
EmpID (foreign key from tblEmployees)
DocID (foreign key from tblJobDocuments)
ReviewDate

in tblDocumentReview, you would have a record for each document reviewed by
each employee on each specific date. you're correct, that is a fair amount
of data entry, especially if there are a number of documents per job. but
data entry is not a consideration when you create tables/relationships;
proper table normalization is your goal at that point. when you begin
building forms for the actual data entry, you can be very creative in
developing an interface to best support the way you need to enter the data,
with the minimum time/effort and maximum accuracy.

hth

 

[onedaywhen]

tblEmpJobs
EmpJobID (primary key)
EmpID (foreign key from tblEmployees)
JobID (foreign key from tblJobs)

I think you need to review your constraints e.g. you have nothing to
prevent:

INSERT INTO (EmpJobID, EmpID, JobID) VALUES (1,1,1);
INSERT INTO (EmpJobID, EmpID, JobID) VALUES (2,1,1);
INSERT INTO (EmpJobID, EmpID, JobID) VALUES (3,1,1);
....

Jamie.

--

 

[tina]

you're correct that i didn't include details on table design, such as unique
indexes, or alternate suggestions for primary keys, such as combination
primary key instead of surrogate primary key.

 

[onedaywhen]

you're correct that i didn't include details on table design, such as unique
indexes, or alternate suggestions for primary keys, such as combination
primary key instead of surrogate primary key.

So you omitted these and suggested a surrogate primary key because ...?

Jamie.

--

 

[tina]

Jeff's question was about basic table/relationship setup. i gave him an
answer that i felt might give him a starting point for setting up the basic
structure.

if you want to give him information about combination primary keys, unique
indexes, or anything else, i imagine he'll appreciate all the feedback he
can get; that's what we're all here for.

 

[onedaywhen]

Jeff's question was about basic table/relationship setup. i gave him an
answer that i felt might give him a starting point for setting up the basic
structure.

If you were aiming at the basic level, it would have been simpler to
omit the 'surrogate key' and use the existing compound key!

Jamie.

--