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

[Alpha]

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

 

[Jeff Boyce]

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

Regards

Jeff Boyce
<Access MVP>

 

[John Vinson]

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

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]

 

[Alpha]

Thanks. So, is there a way to do one-to-one? And how would I do that?
Thanks.

 

[John Vinson]

Thanks. So, is there a way to do one-to-one? And how would I do that?
Thanks.

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]

 

[peregenem]

Note that one to one relationships are VERY uncommon.

Actually, one to one relationships are quite common, it's just they are
usually modelled in the same table

 

[Alpha]

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

 

[Rebecca Dell]

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

 

[Allen Browne]

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.

 

[Rebecca Dell]

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.

 

[Rebecca Dell]

Thanks Allen

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.