Citeria MS Access 2010 uses for selectig the type of realtionship in edit realtions dialogue box

[Lou_Reed]

How does MS Access determine what kind of relationship (one-to-one, one-to-many, many-to-many) when you draw the line from primary key to foreign key and then it selects the type of relationship from one of the three listed above.



I know that if both have indexes (primary and foreign key) do not allow duplicates then that is a one-to-one relationship. It seems that it is arbitrary.

Is there a link that discusses this in detail?

Any help appreciated. Thanks in advance.


Respectfully,


Loou Reed

[ranman256]

Its how you keyed the tables. And your join.
IF the key allows many items in the child then it will be many.

[Lou_Reed]

Okay, as I said when you allow indexes with no duplicates (on both keys) then you get a one-to-one. But that is all I know. Could you expand yours a little more? I am only going by indexes, there must be more?

Thanks in advance.

Respectfully,

Lou Reed

[June7]

If you set a field in one table as Primary with Index (No duplicates) and field in other table as allow duplicates then when you create the line in Relationships builder, Access 'knows' what the relationship is.

[Lou_Reed]

Okay, in this specific case you wrote what is the relationship; it clearly is not one-to-one.

R,


Lou Reed

[June7]

Right.

So this explains and clarifies what happens?

If you don't set a field on one table as primary and then link to another field - what happens?

Do some experimenting.

I suggest that if there is not enough info to know otherwise, Access will default to 1-to-1.

I just did an experiment. Did not quite get what I expected.

I linked PK with FK and the dialog says Relationship Type: One-To-Many (that was expected); however, the JOIN Type defaults to INNER. So, YOU define the JOIN Type you want as default when a query is built.