1-1 or 1-many relationship ?

[Guest]

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?

 

[Douglas J Steele]

Let make sure I understand your setup.

Table1 has an Autonumber field, set as its primary key.

Table2 has a Long Integer field, set as its primary key.

You've joined the Autonumber field in Table1 to the Long Integer field in
Table 2, and it's not showing up as a 1-1 relationship.

Anything other than that, and Access will not recognize it as a 1-1.

 

[Albert D.Kallal]

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?

Actually, in the 2nd table, you have a regular "long number" field used to
relate back to the main table. If you set a unique index on this field, then
ms-access knows this must be a one to one. If you don't set a unique index
on this long number field..then ms-access knows, and assumes this is a one
to many....

 

[Vincent Johns]

My guess is, that if you look carefully, you'll find that the "1" side
is on the Table with the Autonumber, and the "oo" (many) side is on the
other Table.

Actually, in the 2nd table, you have a regular "long number" field used to
relate back to the main table. If you set a unique index on this field, then
ms-access knows this must be a one to one. If you don't set a unique index
on this long number field..then ms-access knows, and assumes this is a one
to many....

Albert Kallal makes an excellent point here -- this is an assumption on
the part of Access. You are not REQUIRED to create "many" linked
records in the 2nd Table, or even "one" linked record. This
organization simply ALLOWS you to do that, if you wish.

All this begs the question of why you'd even want to set up a 1:1
relationship. If you're going to do that, perhaps you'd be better off
tossing all the fields into one Table. One argument in favor of a 1:1
link might be that you have a collection of fields which are used as a
group, and the group is often empty. Then the 2nd Table could contain
records only in those cases where some member of the group actually
contains a value. But I think you'd want to wait to do that until after
the database has been in use for a while and you have a pretty good idea
what the usage pattern is on the various fields.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.