how do set up many to many relationships in Access

[Guest]

I have two tables, one called Country with one field, country ID (e.g.
Philippines), the other called Province with several fields, but the primary
key is ProvinceID (e.g FIS which stands for South Philippines).
But a country can have two provinces, e.g. Philippines has FIS and also FIN
(Philippines North). And a Province may look after more than one country -
part of its own, then an additional country (e.g. FIS looks after South
Philippines but also Pakistan.
Is this a many to many relationship? I think so, Why, then, am I getting a
one-to one every time when I do thus:
I create a third link table with the two primary keys (CountryID and
Province ID, both text fields. I make them primary keys in a new table
called provcountry.
But when I drag provinceID to it counterpart in provcountry I get
one-to-one. Same on the other side for countryID. WHY?

 

[Jeff Boyce]

If I understand your situation, a "Country" can have one or more "Province",
and a "Province" can have one or more "Country". This is many-to-many, and
you need your third table to resolve these.

You didn't mention how you set the primary key of your third table. When I
create two m:m tables, then create a third, I can create a primary key for
this third table that spans both fields (one for each). When I set the
relationships, they come through correctly (each is 1:m). How are you
creating the primary key of the third table?

 

[Julian Fox via AccessMonster.com]

For the third table I took provinceID and countryID from the respective tables and made both of them primary keys in that third table.

 

[Jeff Boyce]

How? Are you saying you highlighted both fields in table design mode and
clicked the "Primary key" button? Do both fields show the primary key icon
in the left-most column?

Jeff Boyce
<Access MVP>

For the third table I took provinceID and countryID from the respective

tables and made both of them primary keys in that third table.

 

[Julian Fox via AccessMonster.com]

Yes, that's exactly what I did - highlighted them and clicked the PK button. Both show the 'key' in the LH column.

 

[Julian Fox via AccessMonster.com]

I'm beginning to think that the problem lies in the way I am conceiving the entities involved - perhaps it is not many-to-many after all, since Access is steadfastly refusing to recognise the relationship as such! I ought to 'listen' to what that is telling me. I can see that a Province can have several countries under its wing - the difficulty seems to be when a 'country' shares two or more Provinces. E.g. Italy in my scheme, has 8 provinces! And some of these provinces look after other countries, e.g. Albania.
It seems to me that I am doing everything correctly according to the normal Access way of relating two tables, but that maybe there is only one-to-many involved in the relationship between 'country' and 'province'. But this problem isn't new I'm sure. I wonder if others have tried to deal with something similar?

 

[Jeff Boyce]

I created a test db with three tables, as you described.
I created T1 with a primary key (T1_ID).

I created T2 with a primary key (T2_ID).

I created T3 (resolver) with a multiple-column primary key (using T1_ID and
T2_ID).

When I used the Relationships window, Access correctly related the two
"outside" tables as 1:m to the "resolver"/"junction" table.

If you've done the same as I just described, I don't understand why your
copy of Access didn't like it.

Have you tried creating a new database?

--
Good luck

Jeff Boyce
<Access MVP>

I'm beginning to think that the problem lies in the way I am conceiving

the entities involved - perhaps it is not many-to-many after all, since
Access is steadfastly refusing to recognise the relationship as such! I
ought to 'listen' to what that is telling me. I can see that a Province can
have several countries under its wing - the difficulty seems to be when a
'country' shares two or more Provinces. E.g. Italy in my scheme, has 8
provinces! And some of these provinces look after other countries, e.g.
Albania.

It seems to me that I am doing everything correctly according to the

normal Access way of relating two tables, but that maybe there is only
one-to-many involved in the relationship between 'country' and 'province'.
But this problem isn't new I'm sure. I wonder if others have tried to deal
with something similar?