Can't create a one to many relation

[lzfy]

Hello
I'm facing a problem with Access which I just can't seem to solve. I'm trying to create a one to many relation between the tables "Klanten" and "Huisgenoten" but Access only allows me to create such a relation between "Huisgenoten" and "Klanten". See the attachment.

So this is what I'm trying to create
klanten 1---- ∞ huisgenoten

But Access only allows this
klanten ∞----1 huisgenoten

Please help?

 

[wiklendt]

could you please translate the two words?

access might not let you create the relationship you intend because perhaps i can't be done that way. e.g., tblMuseums and tblMaterpieces - you can have one museum with many masterpieces, but not one masterpiece in many museums (discounting replicas, frauds, reprints...etc)

it's based in how you have your primary and foreign keys set up - i.e., tblMasterpieces will have MasterpieceID (PK) and MuseumID (FK), while tblMuseum will have only MuseumID (PK). the relationship is geared towards the PK-FK being 1-many. (1 museum, many masterpieces).

 

[wiklendt]

just a tip - your PK and FK should have the same name in all tables they appear in

i.e., HuisgenotenID (PK) = HuisgenotenID (FK)

NOT

HuisgenotenID (PK) = HuisgID (FK)

based on PERSONAL EXPERIENCE, naming your PK/FK the same will save you MUCH FRUSTRATION as your db project grows and expands.

 

[lzfy]

could you please translate the two words?

access might not let you create the relationship you intend because perhaps i can't be done that way. e.g., tblMuseums and tblMaterpieces - you can have one museum with many masterpieces, but not one masterpiece in many museums (discounting replicas, frauds, reprints...etc)

it's based in how you have your primary and foreign keys set up - i.e., tblMasterpieces will have MasterpieceID (PK) and MuseumID (FK), while tblMuseum will have only MuseumID (PK). the relationship is geared towards the PK-FK being 1-many. (1 museum, many masterpieces).

Thank you for the reply. "Klanten" means customers and "Huisgenoten" means room mates. Changing the name doesn't help unfortunately. Table customers has one PK and 4 FK. Creating the fifth FK for room mates causes the problem. I also tried the same name for the PK and the FK in both tables with no succes.

 

[dkinley]

Do you have records in the tables?

If not, it is probably because the data types might not be same (if using autonumber pk's ensure your fk's are long).

If so, you can only probably create it one way because records exist in one table and not the other (the way you want it). For instance ... Table A has at least one corresponding record (pk/fk IDs) to Table B, so Table B can create the link. So if Table B has some excess records (IDs) that are not in a foreign key in Table A, the link cannot be created the other way.

Answering while wiklendt is asleep.

-dK

 

[lzfy]

I cleared both tables just to be sure that that wasn't causing the problem. PK of of tbl room mates is set to autonumber and the FK room mates in tbl customers is set to number (long integer). I uploaded the database, I would appreciate it if someone could take a look at it. Here's the file.

Thanks

 

[dkinley]

Hey lzfy ... I think your problem is you are trying to establish a primary key as the many side of a relationship. To my knowledge this can't be done - only a non-primary key can participate on the many side of a relationship.

I would think that your design structure may contain a flaw or you might be looking to create a many-to-many relationship through a junction of sorts.

HTH,
-dK

 

[lzfy]

Thank you dkinley

I fixed the problem by removing room mates FK from tbl customers and adding a customers FK in tbl room mates. I have a lot to learn

 

[dkinley]

If you aren't learning then you aren't growing ... there is always something new or better.

I am glad you figured it out and posting your solution.

Good luck!

-dK

 

[wiklendt]

Thank you for the reply. "Klanten" means customers and "Huisgenoten" means room mates. Changing the name doesn't help unfortunately. Table customers has one PK and 4 FK. Creating the fifth FK for room mates causes the problem. I also tried the same name for the PK and the FK in both tables with no succes.

sorry, i didn't mean that you change this in your database, just that i wanted to know what these were so that i could make a better judgement on how to arrange your tables.

glad you figured it out, though.