Desing question to make a new table???

[Guest]

I have been working on this project for several weeks and
have learned a lot. I am stuck again though. I have the
following tables with the following fields:
tblPers
PersSSN
PersLname
PersFName
PersRate (job specialty)
PersWC (work center)
PersStat (Active or Reserve)
Persbillet (secondary job specialty)

tblReqTrain
ReqCourseID (autonumber)
ReqCourseDisc (Official name of course)
ReqRefer (reference for training)
ReqDate (scheduled date)
ReqWC (Required by which Work Center)
ReqBillet (same as above by requirement)

tblCompTrain
ReqCourseID
ReqCourseDisc
ReqCourseRef
ReqDate
ReqWC
PersSSN
PersLName
PersFName
PersRate
PersBillet

relationships are One to Many from tblReqtrain to
tblCompTrain and One to many from tble Pers to tblReqTrain.

Orignially I normalized all the tables down to where I had
everything that had redundant information and lookup from
the two main tables, but I had issues later in queries
with displaying record numbers vs readable information. I
can break them down again later if I need to. Right now,
the issues I have are this.
We schedule training a year in advance for rate, billet
and Work Center requirements as well as Safety and some
other things that I can put into the WorkCenter Field
as "All". In all, there are over 1200 unique records in
the tblReqTrain table.
Once the information for completed training was put into
the tbleCompTrain, I was able tell who has HAD what
training if the date scheduled has passed and it was
held... But not who hasn't. (Which really is more
important) And using a form with a subform I can create a
roster of scheduled training with who needs to be there,
and that looks very nice. Now, what I would like to do
somehow is find a way to get THAT information into the
tblCompTrain without manually inputing it all over again,
and if I do that, using a field that has a check or Yes/No
type button, I could tell whether someone was present, and
thereby have a blank field that I know I could use to
query for absentee information and even be able to edit
the records.
I know how to build a join query that tells me what all
the training "would" be if every record were actually
filled out, and it turns out to be a pretty large number.
Is there a way to make that query a table that can be
later edited and make that the tbleCompTrain? etc.
The Future information becming editable for past data is
what is kicking me I guess.

I am a little fried, but am so close to having it all up
and running...Help!!!

 

[John Nurick]

1) You certainly should go back to having single linking fields (e.g.
ReqCourseID should be the only field from tblReqTrain that appears in
tblCompTrain).

2) SSN is not a 100% reliable way of identifying people (there's a
non-zero possibility of data entry errors, identity theft, and so on).
Better to use your own PersID (probably an autonumber) as the primary
key of tblPers.

3) If I understand the situation right, your structure needs to be
modified to something more like this:

tblPers
PersID - PK
name, SSN, other fields as now

tblCourses
CourseID - PK
CourseDisc
other information about the course

tblPersCourses (each record in this table
stores one person's "encounter" with one course)
PersID - foreign key into tblPers
CourseID - FK into tblCourses
WC - required by which work centre
(probably this should be FK into
a table of work centres)
ReqDate - date of requrest for
this person to be given this course
SchedDate - date assigned for this person
to do this course
ActualDate - date on which the person actually
did the course.

The general idea is:
1) When a training request is received, create a record in
tblPersCourses with the PersID, CourseID, WC and ReqDate.

2) When a date is scheduled for the course, update the record with
SchedDate.

3) When the course is completed, update the record with ActualDate.

Then, you can get a list of people who have missed scheduled training
with something like this:

SELECT tblPers.PersID, PersLName, other fields
FROM tblPersCourses
INNER JOIN tblPers on tblPersCourses.PersID = tblPers.PersID
WHERE SchedDate < Date() AND ActualDate Is Null
;

and people who haven't had a particular course with something like
SELECT PersID, other fields
FROM tblPers
WHERE PersID NOT IN (
SELECT PersID FROM tblPersCourses
WHERE CourseID = XXX)
;