Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting unmatched data from a query (Access 2000)

    I have a very big problem with deleting the unmatched data from a query.
    With the help of the Query WIzrad i have built a query called "order details Without Matching orders1 :

    SELECT [order details].OrderID
    FROM [order details] LEFT JOIN orders1 ON [order details].OrderID = orders1.orderid
    WHERE (((orders1.orderid) Is Null));

    This query shows all the orders that are unmatched in the table order details1. I can delete them manually, but i cannot build a delete query
    to delete them through a delete query.

    Some of my failures are the following


    1. I have built a delete query but it says that is will delete 0 rows, and that is not true :

    DELETE [order details].[orderid], *
    FROM [order details]
    WHERE ((([order details].orderid) In (SELECT [order details].OrderID FROM [order details] LEFT JOIN [order details1] ON [order details].OrderID = [order

    details1].OrderID WHERE (([order details1].OrderID) Is Null))));

    2. I have built another delete query but it says " specify the records you want to delete", even though i have specified them

    DELETE [order details].OrderID, orders1.orderid
    FROM [order details] LEFT JOIN orders1 ON [order details].OrderID = orders1.orderid
    WHERE (((orders1.orderid) Is Null));


    I will appreciate every help in this respect

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Deleting unmatched data from a query (Access 2000)

    Your second query starts with
    <pre>DELETE [order details].OrderID, orders1.orderid </pre>

    This refers to two separate tables. Are you trying to delete from order details or from orders1 ?
    Presumably you are trying to delete records from just one table, so you either want
    <pre>Delete [order details].* </pre>

    or
    <pre>Delete orders1.* </pre>

    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Deleting unmatched data from a query (Access 2000)

    Do orders have the same orderid in each table? It looks like they do.

    If so you could just use this:
    <pre>Delete [order details].*
    from [order details]
    where ([order details].orderid Not in (Select orderid from orders1)); </pre>

    Regards
    John



Posting Permissions

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