If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Auto-populate Junction Table of Instructor Quals & Course Reqts

View Poll Results: How thorough was my description of my problem?
Very inadequate 0 0%
Some holes 0 0%
Quite thorough 0 0%
Very thorough 0 0%
Voters: 0. You may not vote on this poll

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2012
Posts: 5
Question Auto-populate Junction Table of Instructor Quals & Course Reqts

I am not a student, in fact I'm on the staff of a small college. I am trying to help out my dean by creating an Access database that will automate the process of showing which instructors are qualified to teach which courses.

Some courses require a Master's degree and 15 credit hours in the related subject (i.e. to teach ENG 101 you need a Master's degree in any subject but must have 15 hours of english on your transcript). Some courses require a Bachelor's and 4 years of related experience (i.e. to teach Java you need a Bachelor's in any subject but must have 4 years of programming experience).

Instructors have multiple degrees and multiple areas of expertise (i.e. Bachelor's in Math, Master's in Computer Science, 4 years of programming experience, 6 years of networking experience).

Based on my research on many to many relationships, such as the one between courses and instructors in my case, it appears I need a junction table to link them.

I am hoping there is either a) a semi-automated way to populate the junction table using the data already existing in the instructor and course tables or b) a way to generate a query or report that will show courses and the instructors that are qualified to teach them without creating a junction table.

The relevant structures currently are:
Instructor (ID, Name, Degree1, Degree2, Degree3, Experience1, Experience2)
Course (Number, Title, Required_Degree, Required_Experience)

Thanks in advance for any insight given!!

Bernie
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2012
Posts: 97
Hi Bernie,

1. If you wanted to simply 'see' which instructors are suitably qualified to deliver a particular course then you would be able to accomplish this using a query and display your results on an unbound form.

2. However if you wanted to 'allocate' suitably qualified instructors to particular courses then this would require a table in which you would only store information about the relationship between the instructor and the course (i.e. start date/end date etc.)

I'm not sure of the data types for your fields Required_Degree and Required_Experience i.e. are they boolean (yes/no) or are they text (Master's in Computer Science)

If solution 1 is what you are after then create a query based on your instructors table (Instructor (ID, Name, Degree1, Degree2, Degree3, Experience1, Experience2))

On your form you could have 2 combo boxes (if you want to provide the user with choices) or text boxes (if you want the user to be able to type freely). Call these two text boxes txt/cbodegree and txt/cboexperience

You can then base the parameters for the query on the values that the user selects in these controls.

Code:
SELECT tablename.*
FROM tablename
WHERE (((tablename.Degree)=[forms]![yourformname]![yourcontrolname] Or (tablename.Degree) Like [forms]![yourformname]![yourcontrolname] Is Null)) OR (((tablename.Experience)=[forms]![yourformname]![yourcontrolname] Or (tablename.Experience) Like [forms]![yourformname]![yourcontrolname] Is Null));
On your form you would also need to create a lisbox and then change the datasource for the listbox so that it displays data from this query.

If you then include code to requery the listbox in the AfterUpdate event of each control that the user might change the list box will always display the current data from the query

Code:
Forms!myformname!listboxname.Requery
__________________
Hope this helps,

Dave.

_____________

Access 2000 User
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2012
Posts: 5
Dave,

Thanks for the post and the information!

If I understand your code/description correctly, it asks the user to enter the degree and the experience required. If that is correct, I apologize for not being clearer. I am looking for a way to allow the user to enter the course ID and have Access produce a list of the qualified instructors who have the required level of degree (Master’s, Bachelor’s, Assoc’s), enough credit hours in field/related field, and enough experience in the related area.

Below is the structure I have created so far that maps courses to instructors based on experience and degree level (still working on the “credit hours in field” mapping). It feels more complicated than I think it needs to be, but it is what I have so far. Unless specified otherwise, all data types are text.

Mapping instructor experience to experience required by a specific course
Instructor (Instructor_ID (number), Inst_First_Name, Inst_Last_Name)
Instructor_Experience (Instructor_ID(number), Inst_Exp_Area)
– maps instructors to their area(s) of experience
Experience (Exp_Area)
– list consisting of experience areas (programming, networking, business, med, etc.)
Course_Required_Exp (Course Number(number), Course_Exp_Required)
– maps courses to their required areas of experience (i.e. if only requires basic computer knowledge, then this table will map to Computer, Networking, Programming, etc)
Course (Course_ID, Course_Title, Required_Degree, Required_Experience)
– I realize I’m storing the fact that a course needs a certain experience in this table and the Course_Required_Exp table

Mapping instructor degrees to degrees required by a specific course (Instructor and Course tables are the same tables from above):
Instructor (Instructor_ID (number), Inst_First_Name, Inst_Last_Name)
Instructor_Degrees (Instructor_ID(number), Inst_Degree)
– maps instructors to their degree/degrees
Degrees (Degree_Level)
– list consisting of Master’s , Bachelor’s, Associate’s, MBA
Course_Required_Degrees (Course Number(number), Degree_Level_Required)
– maps courses to their required degrees (i.e. if only requires Associate’s, then this table will map to Assoc, Bachelor’s & Master’s)
Course(Course_ID, Course_Title, Required_Degree, Required_Experience)

My specific questions are:
- Is this design using junction tables for the 3 relations between instructors and courses correct/the best/simplest way?
- Is creating multiple (3 or more) mappings via junction tables between two "main" tables considered "bad design"? Will it cause logic problems when conducting queries?
- I thought of two other methods of achieving my end goal (entering Course ID and getting back qualified instructors) but would have to research if they are harder than current path I've been working down
-- Link the two tables with a junction table by mapping every instructor to every course and then creating a query that compares multiple fields in course table to multiple fields in instructor table
-- Write VBA/macro that queries the course table for a course's requirements then takes the results to conduct a query on the instructor table


Thanks again for taking the time to read my original post and putting the time/effort into your excellent response!! It is truly appreciated.

If it is easier for you, I am willing to post my access file (it’s ~11MB). Not to have you fix the problem, but to help me explain where I’m at so far.

Thanks again!!! Bernie
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2012
Posts: 97
Hi Bernie,

Just glancing at your post it would look as though your feelings about unnecessary complexity are probably valid, repetition of data is normally a good indicator of inefficient table design.

Regards to design and relationships between fields; creating a valid relationship (i.e. one that reduces data replication) will always improve processing speed.

Regards to your problem (entering course ID and getting back suitably qualified instructors); this is achievable most efficiently as you suggested by retrieving course instructor requirements and matching them to the relevant fields association with the instructors table.

If you could post a copy of your DB here, when I get a chance I'll happily have a look and try to offer answers your questions in more detail.
__________________
Hope this helps,

Dave.

_____________

Access 2000 User
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Apr 2012
Posts: 5
Dave,

I agree about your comment regarding design. The repeated data is a result of my first attempt to link the course and instructor tables via a shorter route but due to there being several many-to-many relations that didn't seem possible. So I started creating the junction tables, which once I have working will go back and remove repeated data.

That said, if there is an easier way to achieve my goal without multiple junction tables, I'm open to learning the design principle involved which I will then implement.

Here's my database so far...admittedly not perfect!!!

______________
Thanks again, Bernie
Attached Files
File Type: zip Instructor DB.zip (469.8 KB, 4 views)
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2012
Posts: 97
Bernie,

I forgot to say that I'm running Access 2000 so am unable to open ACCDB files. If you could save your DB as an MDB file and then repost it that would be great.

My apologies as I realise this is time consuming.
__________________
Hope this helps,

Dave.

_____________

Access 2000 User
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2012
Posts: 5
Dave,

No worries! You're providing me some great help for free and faster than I was expecting!!!

Hopefully the attachment is in the format that works for you. If it doesn't work, please contact me at bernard.bossuyt@stevenshenager.edu and I will email it directly to you without zipping (it's only 2 MB) if that's ok with you.

Thanks again!!!!!!,
Bernie
___________________
Attached Files
File Type: zip Instructor DB.zip (138.0 KB, 2 views)
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2012
Posts: 97
Bernie,

I have had a look at your DB and you have, potentially, a fairly complex problem. I would propose the relationships attached in the example database.

One issue is that Courses and Instructors both have the potential for multiple degrees and instances of experience meaning this information cannot be stored in a single field/s and need to be stored in a table. If you could avoid this it would make things simpler as you could store all of the degrees and exp related to courses in the courses table and then all of the degrees and exp related to instructors in the instructors table.

For now I would suggest making the following changes to your system before you go any further. Once you have got table design right and you are happy with how your DB works then start designing the forms etc. Please feel free to completely ignore this advice if you wish...

1. Restructure the relationships between your tables to something similar to the copy of the file attached. Your table design is equivalent to the foundations of any building. (3 Normal Forms Database Tutorial)

2. When restructuring stick to Access naming conventions (Microsoft Access Naming Conventions) - including spaces and underscores in object or control names will more than likely lead to problems in the future. This seems picky but from experience Access is more so!

3. ONE THING TO KNOW ABOUT RELATIONSHIPS - they are what set a database apart from a spreadsheet: Where possible store all static data in tables (i.e. degree levels, fields of study) that is data that is unlikely to change. The premise for a relationship (link between two tables) is that the Primary Key for a record represents all of the data stored for that record in another table, therefore saving inputting the same data more than once. As well as not having to input the data more than once, relationships are also helpful for making changes to data once and only once (Implementing Referential Integrity and Cascading Actions)

Once you've managed to get to this point then you will find it much simpler to achieve your ultimate end goal (matching course instruction requirements and instructor qualifications).

This is website that I have and still find useful when using Access (Microsoft Access help, tutorials, examples).

As I've said feel free to ignore my advice, it is just how I would approach things, people will have different opinions. Apologies for not completely answering your question - I feel that you will benefit more from tweaking table design issues before progressing further.
Attached Files
File Type: zip DB001.zip (64.7 KB, 11 views)
__________________
Hope this helps,

Dave.

_____________

Access 2000 User
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2012
Posts: 5
Dave,

Thanks again for the outstanding feedback and especially the database design in your attachment!!

It will take me a bit of time to digest and incorporate it all but I do have a few (hopefully short) questions regarding the allocation table.

Is it's purpose to store the instances in which an instructor taught a class? Does it have a role in determining which courses an instructor can teach?
Am I correct that a macro could be written to populate the table with possible Instructor-Course pairs (based on instructor qualificatons & course reqts) that then the user could enter start/end dates, etc?

Thanks so very, very much!!!!

Bernie
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jan 2012
Posts: 97
Hi Bernie,

Your welcome, glad you found it useful.

The purpose of the allocation table would be solely to store instances where an instructor has been or is selected to teach a course. You don't need to 'store' instructors that could potentially teach courses, this information is already readily available in the database and can be retrieved using a query (see below).

This would eventually be achieved by querying the instructor information (tblinstructors, tblinstructordeg & tblinstructorexp) using the information about courses (tblcourses, tblcoursedeg & tblcourseexp) as criteria so that the query displays only instructors who have qualifications/experience that match the requirements for that particular course.

In terms of how this would look on a form. You would have a combo box that lists all of the course information (tblcourses, tblcoursedeg & tblcourseexp). Users would select a course here and the relevant criteria would then update a listbox of instructors who match that criteria. The user would then select one instructor from the list of potential instructors and you could then build in a command button (button that can run VBA code) that would allocate the instructor to the course. This button would create a record in tblAllocation retrieving the CourseNum from the combo box and the InstructorID from the listbox selection. The code could also prompt the user for a StartDate that it would also add to the record it creates in tblAllocation.

Regards to storing qualifications and experience about instructors/courses you have two potential solutions:

1. Storing the information in separate tables (as in the DB example I provided) - this would mean that for each course/instructor there may well be several related records in other tables. For example an instructor may have two related records in tblinstructordeg and 5 related records in tblinstructorexp. As this information is not stored in one record (i.e. under one PrimaryKey) you would need to loop through the tables using VBA to retrieve all of the relevant information (this is fairly complex).

2. Storing all information in the course/instructor tables. This means that each table would need to have fields for each potential degree/experience that a course/instructor may have. For example if you stored all of the instructors experience in the tblinstructors you would need fields experience1, experience2, experience3 and so on. This would make querying the data easier as all records would be stored under one PrimaryKey but less flexible (i.e. if you design the database to hold only 5 elements of experience for instructors and then decide that you want to be able to store 10 you have to change the whole table design rather than simply adding another record.

Method 1 is by far the most appropriate and efficient way to do things but method 2 is considerably easier. Your choice of method should be dictated by your end user needs. I.e. if you are the only one who will be using the system then you are the only person who needs to be happy with it.
__________________
Hope this helps,

Dave.

_____________

Access 2000 User
Reply With Quote
Reply

Tags
course requirements, instructor qualifications, junction table

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On