Many to many to many relationship?

[NOAADude]

Because I am the most technically oriented member in the office I have been tasked with developing a Training database and have found myself a bit over my head. The idea is to have have a list of staff and track both their completed training(with dates of refresher requirements monitored) as well as future training requests/mandates. I envision a table with staff members and a table with courses and the ability to add to both as needed. Also I think it should be able to add course/date offerings to courses. I see one staff member can have many courses and one course can be attended by many staff. Also one course can have many dates (both offered and completed) and one course can have many locations). Is this beyond the realm of a "simple" database? Thank you for any help you might provide.

 

[WildHare]

This is NOT beyond the realm of a simple database, and is such a generic kind of thing that I bet if you looked around the web a bit, you could probably find something about 92% already done for you....

Courses -- Classes -- Students - Grades

Gee, sounds like my Intro to Relational DB design class in 1981.

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[NOAADude]

I see that relationship clearly in regards to tracking historical data ie. what staff has already completed. My confusion lies in how to input/display future training opportunities, particularly ones where the same course may be held in many dates in several locations. There is also issue of how we track future training needs. We have 4 levels of priority with an additional category of "completed but needs refresher training in x years." I understand that to an experienced DB person, this is probably quite straightforward, but I haven't had any database exposure since college in '91.

 

[WildHare]

The correct combination of fields in the courses and classes table SHOULD be able to handle your first two concerns. And the priority feature could probably be associated with your "complete" or "grade" table.

Sample table/layout:

Courses: "T101" , "Typing 101", "3 days", $150,
Classes: "T101", "Chicago, IL", "3/15/2003"
Students: "E3302", "D200", ...
Completes: "E3302", "T101", "3/17/03", "Needs Refresher"


The "course" record is the basic COURSE description. The "Class" record is for each occurance of a COURSE being taught/offered/whatever. Thus, ONE COURSE - MANY CLASSES.

"Students" are .. well, just a list of your potential students and whatever other stuff you care about that is STUDENT specific (Department, SSN, etc etc etc)

"Completes" is the record of a completed CLASS by a STUDENT, with appropriate ancillary info.

It should all tie together quite easily.

Does this give you a bit of a start?

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[NOAADude]

That will help nicely. It is quite similar to where I was going already so it at least comforts me that I might actually get this thing to work the way folks want it to. Now I just have to work out the interface. Thanks for the help
W

 

[WildHare]

No prob. The interface will be much easier to build if the underlying design is correct.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm