many > many relationship join table woes!

[feeel]

As a newbie with access im in need of some direction here!

I have assigned myself the task of creating a very simple database to hold the phone numbers of my friends etc...

This is an excercise to learn relationships, therefore my aim is to create a database that will allow different people to hold the same phone number (ie 2 flatmates will have the same house number) whilst also recording unique numbers (moblie/cell numbers).

To do this i have created 2 data tables, and a join table.

The two data tables are as follows... ContactDetails Table(FName,LName,ContactID{Primary Key}) and NumberTable(consiting of only the one field "Number" which is the Primary key).

My join table consists of "ContactID" & "Number" fileds.

These are all displayed in the Relationships window and have one to many relationshipos set up with integrity applied!

I have then created a form & sub form containing all the fields, viewed by "Contact Details" .

When i try to enter a new contact detail with a number that has already been used it will not allow it!

what can i do or what am i doing wrong??

any help is appreciated!

 

[Norie]

For a many-to-many you need another table and you need to add another field to NumberTable (NumberID).

The new table should be something like HasNumber with the following fields

HasID (Primary Key, Autonumber)
ContactID (Foreign Key from ContactDetails table)
NumberID (Foreign Key from NumberTable)

Then set up a one to many relationship from ContactTable to HasNumber and a one to many relationship from NumberTable to HasNumber