Want to create a one-to-many relationship, but not working 1

[striker73]

I need to create a one-to-many relationship where one client can have many contacts, but when I try to create the relationship, I can only create a one-to-one relationship. Any ideas?

 

[Rohdem]

How are your primary keys set up? If you are trying to set up the relationship between two primary keys, you can only have a one to one relationship because there cannot be duplicate values of a primary key in a table. Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[striker73]

In the client table, the primary key is Client_ID, which is an autonumber (long int) and I have that set up so there can't be any duplicate Client_IDs.

In the contacts table, the primary key is Contact_ID and I have another field which is named Client_ID and I have it set to a long int and it is indexed so that there can be duplicates in the table. I want this to be a foreign key, but I don't know if I have to set that somewhere.

Thanks!

 

[Rohdem]

First off, no you don't have to declare foreign keys anywhere.

Secondly, I built a quick test database based on the info you gave me and I had no problems setting up a one to many relationship.

If you have it (or can convert it to) Access 97 and you want to e-mail it to me (if its not too big), I'll be happy to take a look at it. I can also e-mail you the sample that I created if you like. Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[striker73]

I started over and created the tables from scratch and now it's working. Thanks for your help!!

-striker

 

[Mikethered]

I have the same problem as striker73, in Access 2000.

I am trying to create a one-to-many relationship between the 5-element primary key of table A and the corresponding 5 elements of the 7-element primary key of table B. Access makes this a one-to-one relationship.

I have done exactly the same thing in the same database on a different machine with no problem. I have now hit the problem on an import of that database. Both my experinece and that of striker suggest this is an Access bug.

Peculiarly, in the process of specifying the table B fields corresponding to the table A fields, Access starts off making the relationship one-to-many. However after a variable number of fields have been specified (between three and five) it alters this to one-to-one.

Apart from this particular problem, it seems bizarre that Access does not allow the user to choose explicitly the type of relationship.

Mike

 

[WildHare]

You might not be aware of it, but you can multi-click (Control+Click) fields in the ONE table to grab, in your case, all FIVE at once, and drag them to the MANY side, where you can drop them on the corresponding Five-field set.

I just tried your example and it worked fine.




78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Mikethered]

Thanks for your response, Hare. In fact I was already using multiple selection.

I have now successfully worked around the problem by deleting the table, re-importing exactly the same table, and creating the one-to-many relationship with no problem.

Of course, that is no guarantee that this work-around will succeed for anyone else - it may just be that the wind changed.

Mike