Many to Many Relationship

[tarek_ta2ta2]

hope everyone doing well

i have two tables

one for Models ( Camry - Avalon - Corolla )

and one for SubModels (XL - GL - Sadaan)

How to make a query between these tables

i can make one to many --- but how to make many to many

for example The camry has the subnodel XL and the Avalon to has the sub Model XL

As brief every model has more than one Submodel

and every Submodel can be selected by more then one model

any ideas Please ....

 

[krester]

Hi tarek,
You create a new table: SubModelsToModels
The structure of this table is:
model_cod (the Models table key)
subModel_cod (the SubModels table key)
These two fields are the new table key.
Hope I helped…
​

 

[tarek_ta2ta2]

Joining Table

ok what i get is to create what they called joining table or associative table

...

so i create the table as u told me with this fields

one field for the models table Key
one field for the Submodels Key

so how they become the new table key ( you mean make both of them the primary key ) and if this is correct so what next

when but a combo box in the a form and select from one combo box the models how the other combo box will reflect the submodels for this model

 

[tarek_ta2ta2]

This what i do until now ...

( database 2003 version)

i create the table which contains

ID primary Key

the Models key ( i insert all the models id)

the submodels key ( i insert all the submodels id which met the models)

so i put in the criteria for the ModelsId

[forms]![my form name]![cboModel] which is the combo box for the models

so what should happen when select model from the models combobox

the submodel combobox should load the submodles for this models

but this not happening ..... so what i miss here ? please help

 

[neileg]

Your form works fine as far as the combos are concerned. The form is not bound to anything so nothing gets saved.
What is the problem?

 

[tarek_ta2ta2]

???

the problem is that when i select the model

the submodel combo box not load the data

so do the following

select toyota from the make

the model combo box will load all toyota models

so select model from the model combo box like corolla

then the grade combo box should load the submodels for the corrola

which not happening

hope you get it

 

[tarek_ta2ta2]

i get it but just one last thing

ok ok

i make the grade combo box reflect the submodels for the model combo box

but it load the id for the grade not the name

please someone check it

i'm so close

 

[tarek_ta2ta2]

how to replace the id by the name

look to this please it works well

but in the grade combo box i want the name for the grade to appear not the grade id

note:

there's table for the grade
and tabel for the models

and i made table as joining table between them called tblModelsGrades
because it's many to many relationship

 

[neileg]

Add tblGrades to the query.

 

[tarek_ta2ta2]

i add the tblgrades to the query and once i add it it create relation

between the tblModelsGrades tabel ( idgrade field ) and the tblGrades table (grade field)

but it's not working at all

 

[neileg]

Have you added the grade field to the combo box?

 

[tarek_ta2ta2]

yes

kindly look to the database and tell me what i miss

and there's error appears when i try to select value from the grade combo box before doing any other thing

 

[tarek_ta2ta2]

try to test it with the toyota make

because it's the one i finish

 

[krester]

Hi tarek,
attached here the fixed file.
have a nice day.

 

[tarek_ta2ta2]

It's Really Nice Morning

Hi Krester

i really don't know what to say

i just open my pc and check the form .... download the file

and it's really nice morning .... thanks alot but don't think it's the end

i will study what u do and if i didn't get any think i will come back

it's great there's people help other like that

thanks again and again