Why does Access automatically consider a relationship to be one to

M

Merlin

Why does Access automatically consider a relationship to be one to many? I
have several tables set up. When I move my primary key field from one table
and place it on the same name in the other table it automatically assigns
either a one to one or a one to many relationship. I need a one to one.
Some of the tables are one to one, some are one to many. What determines
this?

Thanks,
 
P

Paul Overway

An existing index on the key fields. For example,

Table A
PKID Yes No Duplicates

Table B
FKID Yes No Duplicates

Results in a one to one

Table A
PKID Yes No Duplicates

Table B
FKID Yes Duplicates OK

Results in a one to many
 
A

Alex White MCDBA MCSE

Because one to many is the mostly used type of relationship ( and it is
fifty fifty if it gets it the right way around), one to one relationships
are (in my view) only used to extend a table because it has too many fields,
(normally a bad design and needs fixing) or security e.g. confidential
information in one table and general information in the other with only
certain people allowed to view the confidential info.
 
V

Van T. Dinh

If you set the linking Field as PK in *both* Tables before creating the
relationship, Access will guess the relationship as One-to-One.

HTH
Van T. Dinh
MVP (Access)
 
J

John Vinson

Why does Access automatically consider a relationship to be one to many? I
have several tables set up. When I move my primary key field from one table
and place it on the same name in the other table it automatically assigns
either a one to one or a one to many relationship. I need a one to one.
Some of the tables are one to one, some are one to many. What determines
this?

Thanks,
Click to expand...

There's nothing arbitrary about it.

If the joining field has a unique index, that table is ipso facto on
the "one" side of a relationship. If both tables have unique indexes,
it's a one to one relationship.

Do note that one to one relationships are VERY rare in practice. Alex
suggested two circumstances which might call for them; the other is
"Subclassing", in which you have (for example) a table of PowerTools,
with one-to-one relationships to TableSaws, Drills, AirHammers and so
on. They're all tools (and have some fields in common), but a TableSaw
doesn't have a MaxAirPressure and an air hammer doesn't have a
TableWidth.

John W. Vinson[MVP]
 
Top