Form for Many-to-Many 1

[multipleintell]

How can I design a form to populate the middle table in a many-to-many relationship?

Any advice is welcome.
I have a few uses for this skill. I seem to keep running into this brick wall and I need to learn the solution. I work for a school district and am currently having trouble with a curriculum database I am working on.

Thanks,
Melissa

 

[jgoodman00]

Could you explain what you mean by middle table a little better please, & what the 'left' & 'right' tables would be??

Also, how these relate, & why you are doing it this way...


James Goodman

 

[MortenB]

Let's call the two end tables DRIVER and CAR and the middle table DRIVINGRECORD. I must fill in the driving record for each driver at the end of the day. I will therefore create a main-subform where the main part consists of DRIVER and the subform consists of DRIVINGRECORD. CAR will appear as a combobox in the subform.

I hope this helps.

 

[jgoodman00]

Does any one driver drive a single car, or can they all drive all cars?? James Goodman

 

[multipleintell]

One Table has the fields:
CONCEPT and CONCEPT_CODE

The Other Table has the fields:
STANDARD and STANDARD_CODE

The middle table links the primary key fields from the other table:
STANDARD_CODE and CONCEPT_CODE

It is a many-to-many relationship

QUESTION:
How do I create a form that will allow the user to see all of the information from the two main tables (CONCEPT.tbl and STANDARD.tbl) without having to look things up in a drop down list. I have hundreds of STANDARDS and hundreds of CONCEPTS and I don't want my teachers to have to scroll down so much. I wanted a subform or something for each of the main tables. The main form I guess would be linked to the middle table. I want them to be able to select one thing from each subform to populate the middle table.

Thanks,
Melissa

 

[930driver]

Use a subform. Base the recordsource for the main form on one of main tables (which ever one is appropriate). Base the recordsource of the subform on your link table. Link the main and subforms on the common field in the recordsources. Then bring the other main table in by using it as the recordsource for a combo on the subform. Bind the combo to the link table.

For instance: the recordsource for the main form might include CONCEPT and CONCEPT_CODE. Then your subform recordscource would include STANDARD_CODE and CONCEPT_CODE from your link table. The subform would be linked to the main form on CONCEPT_CODE. The subform would have one control, a combo, who's recordsource includes STANDARD_CODE and STANDARD. It would be bound to STANDARD_CODE.

That way you can have many Concepts, and each Concept can have any number of Standards.

Hope that makes sense.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[MortenB]

Reply to jgoodman00: Any car of course. Otherwise it wouldn't be many to many.

 

[Guest_imported]

Create 3 tables as you orginally intended - your design is actually good and valid. Your middle table will act to create a relationship that will allow a driver to use many different cars, and a car that can be used by many different drivers. Then select all of the fields you want displayed in the design wizard - from all of the tables you are interested in. Make the INDEX (middle table with the id codes of driver and car -which joins these 2 main tables) your main selection... i.e. when the database wizard asks you 'how do you want to view your data?' - do it by the INDEX table (joining table). You probably already know this - make both the DRIVER ID and the CAR ID primary keys in the INDEX table.
Thats it... you will be able to view the data from both on the same form.
Good Luck