If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how do set up many to many relationships in Access
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? |
#2
|
|||
|
|||
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? -- Good luck Jeff Boyce Access MVP "Volpe" wrote in message ... 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? |
#3
|
|||
|
|||
For the third table I took provinceID and countryID from the respective tables and made both of them primary keys in that third table.
-- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
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 "Julian Fox via AccessMonster.com" wrote in message news:c9a123b18f954fba8da83030d10628c6@AccessMonste r.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. -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Yes, that's exactly what I did - highlighted them and clicked the PK button. Both show the 'key' in the LH column.
-- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
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? -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
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 "Julian Fox via AccessMonster.com" wrote in message news:75704e0184db4d65a7f5ab1f44a29aab@AccessMonste r.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? -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access Error Message when opening database | eah | General Discussion | 3 | January 26th, 2005 10:04 AM |
Access 2003 (Ready to throw in the trash) | Patrick Parks | General Discussion | 5 | January 5th, 2005 10:38 PM |
WORD XP mail-merge FAILS using ACCESS Query | SueMackay | Mailmerge | 1 | November 23rd, 2004 01:03 PM |
Upload Image | Jason MacKenzie | General Discussion | 1 | September 1st, 2004 04:38 AM |
Need help with Access decision | aualias | General Discussion | 23 | June 21st, 2004 02:04 AM |