Why does Access automatically consider a relationship to be one to

[Guest]

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,

 

[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

 

[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.

 

[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)

 

[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,

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]