How to change or indciate the type of relation in Relation Diagram

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

Guest

Hi, How can I indicate the relation to be a 1to1 or 1-to-many or many-to-1 on
my relationship diagram. It seems to have a mind of its own on deciding what
it should be. The field in the edit relation dialog is Read-Only and I can't
edit it.

Thanks, Alpha
 
Access determines the cardinality of the relationship, based on primary and
foreign keys.

Regards

Jeff Boyce
<Access MVP>
 
Hi, How can I indicate the relation to be a 1to1 or 1-to-many or many-to-1 on
my relationship diagram. It seems to have a mind of its own on deciding what
it should be. The field in the edit relation dialog is Read-Only and I can't
edit it.

Thanks, Alpha
Click to expand...

A relationship's one-to-manyness depends purely and simply on the
nature of the fields being related.

If a join field in one table has a unique Index, such as a Primary
Key, that is - and must be - on the "One" side of the relationship. By
definition, it's unique - you can only have one record with that value
in the entire table - so it is ipso facto on the "one" side of the
relationship.

If both tables' join fields have unique indexes, Access will - again,
of necessity - make it a one to one relationship.

John W. Vinson[MVP]
 
Thanks. So, is there a way to do one-to-one? And how would I do that?
Thanks.
Click to expand...

Open the "many" side table in design view; select the joining field;
and create a unique index on that field.

Note that one to one relationships are VERY uncommon. If you're not
familiar with the terms 'subclassing' or 'table-driven security' maybe
you don't really want to do this; rather than a one to one
relationship, just put all the fields in a single table!

John W. Vinson[MVP]
 
John said:
Note that one to one relationships are VERY uncommon.
Click to expand...

Actually, one to one relationships are quite common, it's just they are
usually modelled in the same table :)
 
Thank you all so very much for all your input. It makes a lot of sense now.
I'll just put the 2nd table, it's small anyway, into the 1st table. Than
will work out better and makes more sense. Thanks again.

Alpha
 
Hi everyone

I'm trying to create a database in Access 2003. I thought I understood the
whole relationships things but even after checking here and re-reading my
Inside Out book, I just can't figure out where I'm going wrong.

I have successfully created lots of one to many relationships already but
then all of a sudden access wants to create a one-to-one realtionship where I
want a one-to-many. I've checked the primary key/foreign key thing but even
though the foreign key is not a unique identifier, it still won't allow me to
create one-to-many relationship. (it's actually happening with two or three
relationships I want to create, with different tables involved).

I thought it might have something to do with the other relationships, I'd
already created, so I deleted all the other relationships and tried starting
out with the problematic ones but still one-to-one is what access wants to do.

I'm obviously missing something fairly obvious but what could it be?
Thanks in advance
Rebecca
 
Open the related table in design view.
Select the foreign key field.
In the lower pane, set is Indexed property to No.

You get a one-to-one relation if you have a unique index on the foreign key
field.

You don't want to index your foreign keys at all, since Access creates a
hidden index when you create the relationship with referential integrity
enforced.
 
Ok, typing out the issue must have lead to a rush of blood to the head
because I think I figured it out.

When creating the tables, I had a lot of them open and copied and pasted the
duplicate fields between tables. I think this lead to some of them having
indexes with "no duplicates" indicated. I think this might have made them
seem like a primary key to access. Or maybe access just does this anyway
with certain field-types because I didn't fiddle about with this part myself.

I haven't gotten to the Indexing part of the Inside Out book, so the
solution was probably in the next few pages but I was getting so fed up, I
probably wouldn't have gotten that far.
 
Thanks Allen :)

Allen Browne said:
Open the related table in design view.
Select the foreign key field.
In the lower pane, set is Indexed property to No.

You get a one-to-one relation if you have a unique index on the foreign key
field.

You don't want to index your foreign keys at all, since Access creates a
hidden index when you create the relationship with referential integrity
enforced.
Click to expand...
 
Back
Top