Self-Join Relationship Problem

[David Grant]

I have two tables

----------------------------
tblLocation:
----------------------------
LocationID(text)
LocationDesc(text)
----------------------------

---------------------------
tblInterconnect:
----------------------------
FromLocationId(text)
ToLocationID(text)
----------------------------


Primary key for tblLocation is LocationID. As I want each location ID to
appear only once in the From list with multiple occurances in the To field
being acceptable, I set ONLY FromLocationId as the primary key for
tblInterconnect.

Relationship is:

tblLocation.LocationId ---one-to-one----> tblInterconnect.FromLocationId
(tblLocation AS tblLocation_1).LocationID ---one-to-many--->
tblInterconnect.ToLocationID

Now I build a query:

SELECT tblInterconnect.FromLocation, tblLocation.LocationDesc,
tblInterconnect.ToLocation
FROM tblLocation AS tblLocation_1 INNER JOIN (tblInterconnect INNER JOIN
tblLocation ON tblInterconnect.FromLocation = tblLocation.LocationID) ON
tblLocation_1.LocationID = tblInterconnect.ToLocation;

I can succesfully add records using this query however if i attempt to
change a value in the FromLocationID field I get an error: "The changes you
requested to the table were not successful because they would create
duplicate values in the index, primary key, or relationship. Change the data
in the field or fields that contain duplicate data, remove the index, or
redefine the index to permit duplicate entries and try again." ...Even
though the value I try to change to exists in tblLocation.

What am I doing wrong here?

 

[Larry Linson]

It is unusual to have a table such as your tblInterconnect for a one-to-many
relationship; the normal way is that the table on the "many" side has a
"foreign key field" referring back to the table on the "one" side.

It appears to me that Access is doing exactly what you told it to do... that
is, limit the occurrence of a given value in FromLocationID to a maximum of
one. By setting FromLocationID as a Primary Key, you force it to be unique,
not duplicated. Relationships are from Table to Table, and your description
of the relationships is not consistent with your definition of the Tables.

I'm not certain what you intend by "multiple occurances in the To field
being acceptable" -- could you clarify? If you intend it to be a
"multi-value" field (possible in Access 2007, but not in earlier versions),
you'll have to have someone else help you, because multi-value fields (aka
"complex data") violate relational database design principles and I don't
work with them.

If you can clarify what it is that you are trying to do, and what you
expected to happen, perhaps someone can be of assistance.

Larry Linson
Microsoft Access MVP

 

[David Grant]

Sorry for not being clear.

I have many "peripheral" locationIDs and several "central" locationIDs. Each
peripheral location connects to a central... I want each ID to appear once
in the From field, but the To field (the central locationIDs) can have
duplicates.

As in:

FromLocationID-ToLocationID

1-100
2-100
3-100
4-200
5-100
6-200
7-300
8-200
..
..
..
100-NULL
200-NULL
300-NULL
..
..
..

I did an experiment switching to a single-table design (eliminating
tblInterconnect) with tblLocation having a ConnectsTo field, using a
self-join relationship. This works as I require, but I still don't
understand why the former model didn't.

It is unusual to have a table such as your tblInterconnect for a
one-to-many relationship; the normal way is that the table on the "many"
side has a "foreign key field" referring back to the table on the "one"
side.

It appears to me that Access is doing exactly what you told it to do...
that is, limit the occurrence of a given value in FromLocationID to a
maximum of one. By setting FromLocationID as a Primary Key, you force it
to be unique, not duplicated. Relationships are from Table to Table, and
your description of the relationships is not consistent with your
definition of the Tables.

I'm not certain what you intend by "multiple occurances in the To field
being acceptable" -- could you clarify? If you intend it to be a
"multi-value" field (possible in Access 2007, but not in earlier
versions), you'll have to have someone else help you, because multi-value
fields (aka "complex data") violate relational database design principles
and I don't work with them.

If you can clarify what it is that you are trying to do, and what you
expected to happen, perhaps someone can be of assistance.

Larry Linson
Microsoft Access MVP

 

[Jason Lepack]

Usually when I hear the words "One-To-One", it means, these things
should be in the same table. Not that I said usually, but this time
I'm right.

You should have one table.
LocationID, LocationName, CentralLocationID

Then you only require a self-join. Add the table to the relationships
screen again and join CentralLocationID to LocationID of the new
table.

Note that for any value to occur in CentralLocationID it must already
exist in LocationID.

Cheers,
Jason Lepack