Results 1 to 2 of 2
Thread: Delete unneeded records (2000)
2003-01-06, 09:50 #1
- Join Date
- Jan 2001
- Newcastle upon Tyne, Tyne and Wear, England
- Thanked 0 Times in 0 Posts
Delete unneeded records (2000)
I have two tables, One (tblSupplier)containing fields 'OriginalName'
and 'ActualName', and the other (tblCommodity)containing 'ActualName'
and 'Commodity'. They are linked by the 'ActualName' field (one
occurance in tblCommodity, many in tblSupplier).
If a user changes an 'ActualName' record in tblSupplier, upon closing
the form the new entry is added to tblCommodity.
However, this leaves the original entry in tblCommodity useless, and I
want to get rid of it. I tried to create a Delete Query, which linked
tblSupplier and tblCommodity, and would delete all from tblCommodity
where there was no longer a corresponding value in tblSupplier. But it
brings up a "Cannot delete from specified tables" error. Is this
because my query contains linked tables? In the Preview screen I can
see the record(s) I want to delete, it just won't do it.
Any help would be greatly appreciated.
2003-01-06, 10:08 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Delete unneeded records (2000)
1. You can't execute an action query if is not updatable (that is, if you run it as as selection query, you can't edit records).
You can get around it by creating a subquery.
The SQL for the query could look like:
WHERE tblCommodity.ActualName Not In (SELECT tblSupplier.ActualName
FROM tblSupplier LEFT JOIN tblCommodity ON tblSupplier.ActualName = tblCommodity.ActualName
WHERE tblSupplier.ActualName Is Null)
2. If you have a relation between tblSupplier and tblCommodity on ActualName, you can set referential integrity for the relation, and set updating cascades. If a user changes the ActualName field in tblSupplier, the corresponding field in tblCommodity will change with it automatically - no need for creating and deleting records.