How can I make the following query read-write rather than read-only?

[David Grant]

How can I make the following query read-write instead of read-only?

SELECT LocMatrix.L1.LocationID, LocMatrix.L2.LocationID, Distance
FROM ((SELECT L1.LocationID, L2.LocationID
FROM tblLocation AS L1, tblLocation AS L2
ORDER BY L1.LocationID, L2.LocationID) AS LocMatrix) LEFT JOIN
tblDistanceMatrix ON (LocMatrix.L1.LocationID =
tblDistanceMatrix.fromLocationID AND LocMatrix.L2.LocationID =
tblDistanceMatrix.toLocationID);

The subquery (shown below) is read-only which is the underlying problem:

SELECT L1.LocationID, L2.LocationID
FROM tblLocation AS L1, tblLocation AS L2
ORDER BY L1.LocationID, L2.LocationID

The above subquery returns every permutation of to and from locationID.

tblDistanceMatrix contains a record only for to/from location pairs for
which a distance has been entered.

When a locationID is added to tblLocation, a simple requery is all that
should be needed for the user to see new location permutations without loss
of existing distance data. Cascade Delete Related Records ensures necessary
records are deleted in tblDistanceMatrix when locationIDs are deleted in
tblLocation.

Any help is appreciated. Thanks,

David

 

[James A. Fortune]

How can I make the following query read-write instead of read-only?

SELECT LocMatrix.L1.LocationID, LocMatrix.L2.LocationID, Distance
FROM ((SELECT L1.LocationID, L2.LocationID
FROM tblLocation AS L1, tblLocation AS L2
ORDER BY L1.LocationID, L2.LocationID) AS LocMatrix) LEFT JOIN
tblDistanceMatrix ON (LocMatrix.L1.LocationID =
tblDistanceMatrix.fromLocationID AND LocMatrix.L2.LocationID =
tblDistanceMatrix.toLocationID);

The subquery (shown below) is read-only which is the underlying problem:

SELECT L1.LocationID, L2.LocationID
FROM tblLocation AS L1, tblLocation AS L2
ORDER BY L1.LocationID, L2.LocationID

The above subquery returns every permutation of to and from locationID.

tblDistanceMatrix contains a record only for to/from location pairs for
which a distance has been entered.

When a locationID is added to tblLocation, a simple requery is all that
should be needed for the user to see new location permutations without loss
of existing distance data. Cascade Delete Related Records ensures necessary
records are deleted in tblDistanceMatrix when locationIDs are deleted in
tblLocation.

Any help is appreciated. Thanks,

David

You can't edit new permutations in tblDistanceMatrix because the ID
combinations don't exist for them by definition.

Perhaps one way around this is to create a Make Table query to put the
permutations (along with existing distances) into a new table
(tblDistanceMatrixNew). After all the new distances are placed in the
new table you can delete any records without distances if you feel you
must, or use a WHERE to keep them out of your queries.

qryMakeNewDistanceMatrix (A97 version):
SELECT CLng(LocMatrix.L1.LocationID) AS fromLocationID,
CLng(LocMatrix.L2.LocationID) AS toLocationID,
tblDistanceMatrix.Distance AS Distance INTO tblDistanceMatrixNew FROM
[SELECT L1.LocationID, L2.LocationID FROM tblLocation AS L1, tblLocation
AS L2 ORDER BY L1.LocationID, L2.LocationID]. AS LocMatrix LEFT JOIN
tblDistanceMatrix ON (LocMatrix.L2.LocationID =
tblDistanceMatrix.toLocationID) AND (LocMatrix.L1.LocationID =
tblDistanceMatrix.fromLocationID);

qryMakeNewDistanceMatrix (my guess at your version):
SELECT CLng(LocMatrix.L1.LocationID) AS fromLocationID,
CLng(LocMatrix.L2.LocationID) AS toLocationID,
tblDistanceMatrix.Distance AS Distance INTO tblDistanceMatrixNew FROM
((SELECT L1.LocationID, L2.LocationID FROM tblLocation AS L1,
tblLocation AS L2 ORDER BY L1.LocationID, L2.LocationID) AS LocMatrix)
LEFT JOIN tblDistanceMatrix ON (LocMatrix.L2.LocationID =
tblDistanceMatrix.toLocationID) AND (LocMatrix.L1.LocationID =
tblDistanceMatrix.fromLocationID);

James A. Fortune
(e-mail address removed)