D
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?
----------------------------
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?