Indeterminate relationship

[Dennis Snelgrove]

What would cause a relationship to be "Indeterminate" in Access XP? I've got
two tables, and I'm trying to set up a simple One-to-Many relationship. I've
done it a hundred times. This time, the program insists that it can't
determine what kind of relationship there should be. The tables are as
follows:

tblNames
NamesID - autonumber PK
Name - Text
....

tblCallOutDetail
CallID - autonumber PK
ShiftID - Long Integer
NamesID - Long Integer FK

I'm trying to join the two NamesID fields, but it keeps returning an
"Indeterminate" relationship, and has greyed out all the options in the
Relationship box.

Any help would be really appreciated...

 

[Dennis Snelgrove]

Yes. I checked again just now and the Primary keys are as I said. The
tblNames table is in another file and is joined through a link. The
tblCallOutDetails is a local table.

 

[Pat hartman]

Sorry, but the only way that I can get the relationship type to say indeterminate is if the one-side tabletable does not have a primary key.

 

[John Vinson]

Yes. I checked again just now and the Primary keys are as I said. The
tblNames table is in another file and is joined through a link. The
tblCallOutDetails is a local table.

You cannot create a relationship with relational integrity enforced
unless both tables are in the same database. The reason? If you put a
referential constraint in A.MDB on a table in B.MDB, there is nothing
to prevent someone from opening B.MDB directly (or from X.MDB) and
entering data which would violate the constraint.

You simply cannot do what you ask.

 

[Dennis Snelgrove]

Ah. That's understandable, but does this mean that I can't make this a 1:M
relationship? Visually, the program looks like it's creating a 1:1 in the
relationship window. Or does it just look that way because it can't create
any referential integrity? I'm just interested in the 1:M relationship; not
enforcing integrity. My form will be ensuring integrity by using dropdown
boxes with the"Limit to list" turned on.

 

[John Vinson]

Ah. That's understandable, but does this mean that I can't make this a 1:M
relationship? Visually, the program looks like it's creating a 1:1 in the
relationship window. Or does it just look that way because it can't create
any referential integrity? I'm just interested in the 1:M relationship; not
enforcing integrity. My form will be ensuring integrity by using dropdown
boxes with the"Limit to list" turned on.

There are several graphics for joins. A join that does not have RI
enforced will just have a dot at each end; a one to one will have a
numeral 1 at each end; a typical RI enforced one to many will have an
arrowhead on the many side.

You won't be able to check RI in the relationships window, but you
should be able to create a relationship which will be the default join
whenever you include the two tables in a query. If the "one" side
table is in the database where you're editing the relationship, and
you use the field with a unique index in the join, it should show 1:n
rather than indeterminate - but I'll confess I haven't actually tried
this exact situation!