Relationship one-to-one or one-to-many

[mscertified]

I have two tables one with an autonumber key, the other with a 1-1
relationship with the autonumber key.

I was astonished when I set up the link in the relationship window. It set
up a -1-many relationship with the many side on the autonumber key !!! How
can this be and how does Access figure out whether to assign a 1-many link or
a 1-1 link?

 

[mnature]

It is based partly on whether the linking fields are both set up as having
unique numbers, and how you set the link in the relationships (using Join
Type . . .). It means that your tables are set up in such a way that you can
produce something other than a 1 to 1 relationship, such as two records in
one table that can both relate to the same record in the other table.

I'm sure you have a good reason for two tables with a 1 to 1 relationship?
Sometimes that is done for aesthetic reasons.

 

[John Vinson]

On Thu, 3 Nov 2005 08:04:02 -0800, "mscertified"

Please, if you post to more than one newsgroup, Crosspost (by putting
the two or three newsgroups in the Newsgroups line) rather than
multiposting (posting separate but similar messages to multiple
newsgroups). Multiposting wastes volunteer's time since we have no way
to know if the question has been answered elsewhere.

I have two tables one with an autonumber key, the other with a 1-1
relationship with the autonumber key.

An Autonumber can NEVER be used as a foreign key. You have absolutely
no control over its value. The "parent" table can have an Autonumber
primary key; the other table must use a Long Integer field for its
link.

I was astonished when I set up the link in the relationship window. It set
up a -1-many relationship with the many side on the autonumber key !!! How
can this be and how does Access figure out whether to assign a 1-many link or
a 1-1 link?

It will create a one to one if there is a unique Index (such as a
Primary Key) on the second table's linking field.

Note that one to one relationships are VERY rare. If you're
intentionally doing Subclassing, or Table Driven Field Level Security,
you may be ok; but if those terms aren't familiar, you may not
actually want a one to one relationship. Could you describe these
tables, and why you think a one to one is needed?

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

Remove the relationship and recreate it. This time, drag the join from the
other direction.