Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Newcastle upon Tyne, Tyne and Wear, England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete unneeded records (2000)

    Good morning.

    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.

    James.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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:

    DELETE tblCommodity.*
    FROM tblCommodity
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •