I'm having trouble in creating a one-to-one relationship between two tables in MS Access - when I create the relationship, it defaults to "Many-to-one", and I don't see an option to change it. Is there a way to do this?
+2
A:
EDIT: Important update note from comment -
Access has no such concept. You can either conflate the two tables into one, or place unique indexes on both tables for the common field(s). Are there other benefits you would expect?
You could I suppose create FK relationships in both directions, but I'm not sure what it would gain you except maybe query hints.
EDIT: Important update note from comment -
I don't see the problem. I opened a random DB on my computer, duplicated a table, and opened the Relationships editor. Dragged one ID field to another to create a relationship. http://i43.tinypic.com/2v30x7c.png One-to-one is an option. – Brendan Kidwell
le dorfier
2009-04-08 22:49:08
I don't see the problem. I opened a random DB on my computer, duplicated a table, and opened the Relationships editor. Dragged one ID field to another to create a relationship. http://i43.tinypic.com/2v30x7c.png One-to-one is an option.
Brendan Kidwell
2009-04-09 18:47:19
Good info, thx - added comment above.
le dorfier
2009-04-09 18:54:00
What do you mean by "Access has no such concept?" Sure, Jet is the actual database engine, but nobody distinguishes the two. Jet certainly allows the creation of a 1:1 relationship as long as the joined fields both have unique indexes and are compatible data types.
David-W-Fenton
2009-04-09 21:57:23
BTW, there are perfectly valid reasons to partition a table into separate 1:1 tables, e.g., supertype/subtype relationships, or, to move complex data types (memos, BLOBs, OLE) into another table to protect the main table from any corruption of the pointers that are stored for those data types.
David-W-Fenton
2009-04-09 21:59:38
Yup, that's why I asked about what benefits were expected.
le dorfier
2009-04-09 22:03:19
Yes, I didn't have a unique index on one of the fields. Making it so caused it to have a one-to-one relationship type.
Smashery
2009-04-14 01:46:08