Very complicated relationship! 1

[AceJupiter]

Hi guys,

This relationship is more confusing than the DNA Paternity Test special on the Jerry Springer Show! (errr...don't have me shot for that lame joke hehe)

My application tracks, among a bazzillion other things, training for various members of my company.

After much research I've discovered that the training a member must get is tied to his/her TITLE. So each TITLE has a list of classes that each member must complete to be qualified for his TITLE (for example, a forklift operator must have the licenses for various types of forklifts, must have safety training etc). I think it is danged complicated, but it gets worse.

So each TITLE has a list of classes that must be completed, as we've discussed. I must intelligently tell Access which TRAINING each TITLE must accomplish. I think I need a many-many between tblTITLE and tblTRAINING. So far so good. Now, each MEMBER must be tracked according to his progress going through said classes.

Say there is member named Bob who is a DRIVER. I have a tblMember that is keyed to tblTITLE through titleID.

What I need to tell Access is "Okay Access, Bob is a DRIVER. Please look up which TRAINING DRIVERs require. Display those on a subform of our data entry form. We need to record which classes Bob has taken, enter the dates etc."

Can anyone help? I have this application humming as far as the classes all employees have to take. I am replacing a CRAZY Excel application which is so darned delicate...what the creator did was hand-type all the classes each member takes. So, if the curriculum changes, we'd have to go through this mile-long spreadsheet and hope to gosh we didn't mess it up. It's one of those deals with hard-coded formulae -- it's just a nightmare to maintain.

Many thanks for any assistance.!! I'm more than desperate enough to put up a file somewhere if anyone needs to see my table structure in order to help. It beggars my ability to describe clearly.

--ACE

 

[Golom]

Yes you do need a many-to-many table between TITLE and TRAINING. If you have that of the form
[tt]tblTitleTrain
Title Course
Driver Safety
Driver Backing Up
Driver Shifting Gears
Driver StartingEngine
Secretary Typing
[/tt]
Then your query about who needs what training is fairly simple
[tt]
Select E.Name, C.Course, C.CourseDescription

From ((tblEmployee E INNER JOIN tblTitle T ON E.Title = T.Title)
INNER JOIN tblTitleTrain TT ON TT.Title = T.Title)
INNER JOIN tblCourses C ON TT.Course = C.Course

Where E.Name = "Bob"
[/tt]
Here I have assumed that the employee's title is stored in a "TITLE" field in the employee record and that there is another table called "tblTITLE" containing information about titles. I've also assumed that you have a "tblCourses" that contains course descriptions. It would probably have a "tblSchedule" foreign keyed to it with times and locations when the course is given.

The thing that can really cause problems in this sort of thing is pre-requisites. For example, if a "Driver" in the above table must take "StartEngine" before he can take "ShiftGears" then there's another layer of complexity.

If, in additionn you had a "tblCoursesTaken" table with EmployeeID and CourseID then
[tt]
Select E.Name, C.CourseID, C.CourseDescription

From ((tblEmployee E INNER JOIN tblTitle T ON E.Title = T.Title)
INNER JOIN tblTitleTrain TT ON TT.Title = T.Title)
INNER JOIN tblCourses C ON TT.CourseID = C.CourseID

Where E.Name = "Bob"
AND NOT EXISTS
(Select 1 From tblCoursesTaken X
Where X.CourseID = C.CourseID
AND X.EmpID = E.EmpID)
[/tt]
Returns the courses that Bob needs to take but he has not already taken.

Hope this gives you some ideas about where to start.

 

[AceJupiter]

Golom,

I'm impressed by your ability to render that so clearly. Let me punch in some of your ideas and see how far I get!

--ACE

 

[BSman]

The many-to-many relationship disappears by using a "third" table. You have a jobs table and a classes (subjects) table. Create another table, I'll call it JobClasses, in which each record will contain the jobID and the classID (references to the records in each table). This defines the classes needed for each job, but has a many-to-one relationship with the classes table and with the jobs table.

Assuming each person has only one job, then you can easily determine what training is required for that person's job. If you have another table (I'll call it TrainingReceived) that contains the classID, employeeID, and other data that shows when each class (subject) was taken and if it was completed, then you can create a query that uses the query that lists all classes required (and returns all records from that query) linked to TrainingReceived by the classID AND the employeeID (assuming you include employeeID in the first query). Any record where the date completed (from TrainingReceived) is null is training that is still needed. If there are none, then the person has received all training needed for his/her job.

Another thing you can do if you set it up this way is to expand the database to also set up a table for certifications and skills, with a table that lists the cert/skillID with classIDs required. You can then identify, based on the training received by an employee which skills and certifications he has completed all training for, regardless of requirements for his job.