Beginner: Many-to-many relationship

[danb]

Hi,

I have two tables, one contains a list of universities - it has two columns: 'Id' (the primary key) and 'UniversityName'; the other has a list of degree qualifications (e.g. MSc physics, BSc geology, MA fine art) - it has two columns: 'Id' (the primary key) and 'QualificatoinName'.

I want to link these two bacause each university can offer different qualifications, and each qualificaiton can be offered at several universities.

I've created another 'junction' table with two columns: one UniversityNameId and one QualificationId. These have both been set as primary keys in design mode.

I've clicked the 'relationships' button in Access and dragged the Id from the university table into the junction table and likewise with the qualification table.

It seems though that the relationship is backwards. I was hoping to open the junction table and choose a value for it's UniversityNameId by selecting from a drop-down box, and likewise with the QualificationId column. Instead, the drop-downs appear in the non-junction tables???

What am I doing wrong?

Does anyone have a simple example mdb file of how to do this?

Thanks!

 

[Mile-O]

What about awards?

MSc Physics
BSc Physics

 

[danb]

Only one type of award is being used for each subject at the moment. It's going to be for a very specific range of qualifications actually, so this isn't an issue at the moment. I'm really just wondering how to link two tables together via a junction table to enable many to many relationships between qualifications and universities, and be able to open the junction table and select values for university and qualification via a drop-down list.

I could do this manually without any joins at all but I feel it would jeapordise data/relationship integrity in the future.

 

[danb]

This database is being used solely on the web - including adding new records, so I guess the tables don't actually have to be linked in Access since I'm going to have to join them with SQL code in the script running the web pages anyhow.

I ended up looking-up all of the records and inserting them into the junction table manually - just thought there would be a way to use access to select values in the two junction table columns from a list comprising data from the two source tables.

 

[danb]

But if I want to select data from more than one table I have to use a JOIN in the script, regardless of whether the tables are related in access, yes?

I think I've managed to get all the data into the table correctly now anyhow.

 

[Mile-O]

Only one type of award is being used for each subject at the moment. It's going to be for a very specific range of qualifications actually, so this isn't an issue at the moment.

At the moment.

This obviously implies that it may be in the future. You can save time by designing the database t be prepared for this rather than having to delve into redesigning in the future.

 

[danb]

Apologies for my poor wording. The site will never to my knowledge require this functionality - else I would have incorporated it into the database design.