Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    deleteondition all with where (Access 2000)

    I want to delete all the records from the tables orders and order details,
    where the customerid = 100
    I cannot do it because Access says it cannot delete all the records.
    the three tables are connected like that customers-orders-order details
    The table orders contains the cutsomerid as a foreign key.


    How can i do it ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: deleteondition all with where (Access 2000)

    You have two options:

    1) Select Tools | Relationships... Double click the join between Orders and Order Details (on the OrderID field), and make sure that all the check boxes are ticked:
    - Enforce Relational Integrity
    - Cascade Update Related Fields
    - Cascade Delete Related Records
    Click OK to close the Edit Relationships dialog, then close the Relationships window. If you now try to delete the records from Orders with CustomerID = 100, Access will delete related records in Order Details too, after asking for a confirmation.

    2) Another way is to delete the records in the Order Details table first. The SQL for a delete query to do this is

    DELETE * FROM [Order Details] WHERE OrderID In (SELECT OrderID FROM Orders WHERE CustomerID=100)

    Only then delete the records from Orders:

    DELETE * FROM Orders WHERE CustomerID=100

Posting Permissions

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