One-to-many with Autonumbers involved

[Guest]

I am trying to normalize a flat database.

The records in the flat table are uniquely identified by an Autonumber as
the primary key. My new "many" table has an Autonumber primary key as
well...the redudant information is of course removed.

In my newly created "one" tables, is it possible to use the "many" table
Autonumber to refer to the records in the "one" table? I think my problem
stems from the fact that I don't "understand" the Autonumber -- it's not part
of my data and it means nothing to me, plus Access automatically creates
them, which makes it harder to understand.

 

[Steve Schapel]

Rich,

I am not sure what you mean about Access automatically creating
Autonumber fields... I have never experienced that.

But anyway, it seems to me that you would not use the "many" table
primary key field, Autonumber or otherwise, to refer to the related
"one" table record. The primary key field of the "many" table is
supposed to uniquely identify each record in that table. However, you
do need a field in the "many" table, which will have the same value as
the "one" table's primary key field, which is then the basis of the
relationship between the two tables. If the "one" table's primary key
field is an Autonumber, then the foreign key field in the "many" table
should be a Number (Long Integer) data type.

If you need more explicit help with this, maybe you could post back with
more details of the table(s), with examples.

 

[John Vinson]

I am trying to normalize a flat database.

The records in the flat table are uniquely identified by an Autonumber as
the primary key. My new "many" table has an Autonumber primary key as
well...the redudant information is of course removed.

In my newly created "one" tables, is it possible to use the "many" table
Autonumber to refer to the records in the "one" table? I think my problem
stems from the fact that I don't "understand" the Autonumber -- it's not part
of my data and it means nothing to me, plus Access automatically creates
them, which makes it harder to understand.

An Autonumber is NOT essential. If you have a valid Primary Key - a
field (or combination of up to ten fields) which is A) Unique; B)
Reasonably stable; and C) preferably short, then THAT field can and
should be your Primary Key.

Access uses Autonumbers as a convenience, to give you a field which is
guaranteed to meet these three criteria. Program bugs excepted,
Autonumbers are indeed unique, stable (they can't be edited), and
short. As such they make a good candidate Primary Key, and in
practice, they make a convenient and easy to use one.

You can use the Autonumber as the Primary Key field to link *FROM* -
it's the linking field in the "one" side table of a one to many
relationship. However, you CANNOT use it as the "foreign key" in the
"many" side table, the table you're linking *TO*. The autonumber value
is *totally arbitrary* - a foreign key is absoulutely NOT arbitrary,
it contains the very specific information "This record is related to
THAT record over there, some specific chosen record, not any other".
As such, the Foreign Key field in a "many" side table must be a Long
Integer if it is to link to an autonumber Primary Key.


John W. Vinson[MVP]

 

[Guest]

Rich,

I am not sure what you mean about Access automatically creating
Autonumber fields... I have never experienced that.

Access most certainly can automatically create Autonumber fields. If you try
to save a new table without a primary key defined, Access will offer to
create one. If you agree, a new field will be added to your table, named ID
and of type Autonumber.
-TedMi

 

[Steve Schapel]

Thanks, Ted. I forgot about that. I see what you mean, although I
personally wouldn't call that "automatically", but maybe that was what
Rich was referring to.