Basic layout?

[NewShoes]

Hey all,

I'd like to create a database that stores college courses and associated modules. I have the basic layout of a Courses table and a Modules table but here is the issue....I'd like to be able to create a predefined list of Modules that I can then "attach" to a particular Course. Similarly, I'd like to have the same functionalisty for Courses.

For example, I have Course "Business IT". I then would like to attach the modules "COMP101", "NETWORK102" etc to "Business IT".

I'm sue this is very basic but I just cant seem to get my head around it.

Thanks in advance for any help!
-NS

 

[Mr. B]

This really sounds like another "many-to-many" relationship to me.

If one course can have many modules, will need a table that will link the courses with the moduless. This is called a "junction" table.

Although you may find that you need and want more fields in your junction table, you would only be required have to have two fields in this junction table. One field would be CourseID, which would hold the value from your ID field in your courses table and a field ModuleID which would hold the value from the ID field in the module table.

With this junction table in place, you can now have multiple modules linked to the same course and one course can be related to multiple course.

 

[NewShoes]

Many thanks Mr B. I'll have a go with your suggestion.
-NS

 

[NewShoes]

Hi, I've tried the junction table and it seems to work.

I would now like to build on this and enable the users to "attach" modules to each course. I already have populated courses and modules tables with data. What I'd like is a combobox within a subform that will let me select a module code, and will autocomplete the rest of the module info (what semester it runs in etc).

I think this is something to do with Lookups but I'm a little stuck!

Thanks,
-NS

 

[Mr. B]

NS,

I you would have the other fields that you need to complete also in the Junction table and use that junction table as the row source for your form.

You would need a combo box that would have the list of courses where the user could select the course. You would need another combo box where user can select the course and then other controls on the form so the record can be completed.

There would of course need to be a Save and a Cancel button and perhaps even a "New Record" button.

That's the birds eye, high level overview.