one-to-one versus one-to-many relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a new user trying to create a one-to-many relationship between two tables. When I use the relationship diagram window to define the relationship it is always defined as a one-to-one relationship. Ho can I make the relationship one-to-many?
I greatly appreciate your help with this question!

(the fields involved are an auto-number field and a long integer number field.)
 
Mark said:
I am a new user trying to create a one-to-many relationship between
two tables. When I use the relationship diagram window to define the
relationship it is always defined as a one-to-one relationship. Ho
can I make the relationship one-to-many? I greatly appreciate your
help with this question!

(the fields involved are an auto-number field and a long integer
number field.)
Click to expand...

Hopefully it's the auto number that you want as the "One" and the other long
integer as the many.
If there is no data in either table, just make sure the field that you want
to be the "one" is the primary key in it's tabel, and that the "many" field
is not a primary key (in fact there most likely won't be a primary key in
the "many" table).
When you initiate the join by dragging one field to the other, a dialog
should pop up detailing the nature of the join and giving you the option to
enforce referential integrity. If you take this option then you will have
created a one-to-many relationship. If there is data in either of the tables
which would conflict with the referential integrity rules that you are
attempting to create, Access won't allow you to create them. You'll need to
fix the data first.

HTH

Damo
 
open the table with the long integer number field, in design view. in the
field's Field Properties on the General tab, look at the Indexed property.
it's probably set to Yes (No Duplicates). change it to No or to Yes
(Duplicates OK) and then try creating the relationship again.

hth


Mark Howard said:
I am a new user trying to create a one-to-many relationship between two
Click to expand...
tables. When I use the relationship diagram window to define the
relationship it is always defined as a one-to-one relationship. Ho can I
make the relationship one-to-many?
 
Just an additional note -- it's always a good practice to have a primary key
in every table. The primary key can be a surrogate key in your child table
(such as another autonumber field), or it can be a composite primary key
consisting of the foreign key (the one that you're linking to the parent
table's primary key field) and another field in the child table. If you
don't have a primary key, then ACCESS will have great difficulty (in fact,
it may be impossible) being able to update records that are in the child
table when you use queries as the source of the updates -- this is because
ACCESS must be able to uniquely identify a record in a table in order for it
to update that record.
 
Back
Top