Newbie: Junction Boxes

[zack]

I am trying to design a database from scratch to assist me at work, but
being fairly new Access I am struggling to get my head round Junction
boxes and Many to Many relationships.
Here's my issue.

Currently I have 4 tables.

Course Info: PK (Leaflet No)
Course Notes: PK (LeafletID) (one to one with Course Info table)
Site: PK (Autonumber)
College Employees: PK (Automnumber)

Relationships are:
One course can run at many sites
One site can run many courses
One employee can teach many courses
One Course can be taught by many employees

For the life of me I cannot work out how to join these table together.
I appreciate I probably need to junction table but despite numerous
tries have been unsucessful.
I would grateful for any assistance/advice on offer.
Many thanks

Zack

 

[George Nicholson]

I would think you need at least 1 more table: Class (i.e., CourseInstance)
(AutoNumPK, Course, Site, Employee)

While Courses, Sites and Employees would be unique in their own tables, they
each would appear multiple times in this table, but probably not in the same
combination more than once. You could probably use some combination of those
fields to create a unique index that would prevent entry of a record that
duplicated all fields of an existing record.

For instance, if a course is only offered once per site then you could
create a unique compound index on Course + Site. If a course is offered
multiple times at one site, but not by the same employee, a unique index
could be created for Course + Site + Employee.

Querying this table could then tell you: What courses are being taught at
what site, what courses are emps teaching, what courses aren't being taught
(non-matches), etc.

(BTW, From the info you have provided I'm not sure if Course and Course
Notes really need to be separate tables).

HTH,