Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    delete query (Access 2000)

    Whats wrong with my delete query ? Did i miss something ? it seems not to work

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

    Re: delete query (Access 2000)

    You haven't specified from which table you want to delete records - the Delete option says "Where" for all columns.
    Change the Field expression for the first column to orders.*, this will automatically change the Delete option for this column to "From".
    The SQL is:

    DELETE orders.*, orders.orderdate, Customers.MyOffice
    FROM Customers INNER JOIN orders ON Customers.Customerid = orders.customerid
    WHERE orders.orderdate<#1/1/2006# AND Customers.MyOffice=True;

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete query (Access 2000)

    Thank you, Hans !

    Peljo

  4. #4
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete query (Access 2000)

    Dear Hans,

    Having successfully applied the answer to my question i tried the next step to delete also the table order details, which not always is deleted after i delete the table orders, even though it is connected one to many and i also marked cascade deleting.In order to delete also from this table, i included also this table to my qyery and received the following :
    DELETE orders.*, orders.orderdate, Customers.MyOffice, [order details].*
    FROM (orders INNER JOIN Customers ON orders.customerid = Customers.Customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID
    WHERE (((orders.orderdate)<#1/1/2006#) AND ((Customers.MyOffice)=True));

    But Access says it cannot delete. Why is it so ?

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

    Re: delete query (Access 2000)

    You cannot specify more than one table with the "From" option. But you don't need it either. Simply remove the table Order Details from the query. The Cascade Deletes option will automatically delete related records from Order Details when you delete records from Orders.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete query (Access 2000)

    Thank you,Hans.I do not inow how but the orders in the tble order details remain,they are not cascade deleted.It must be due to bad connections in the relatonships.How could i in that case delete the orders
    from the order details:my effort to delete gave no results:
    DELETE orders.orderid, [order details].*, orders.orderdate, Customers.MyOffice
    FROM (Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID
    WHERE (((orders.orderdate)<#1/1/2006#) AND ((Customers.MyOffice)=True));

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

    Re: delete query (Access 2000)

    Perhaps there is a problem with your database. You could try creating a new one.
    Otherwise, you should use two separate delete queries: the first to delete records from order details, the second to delete records from orders.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete query (Access 2000)

    Thanks.Thank you so much. : "My first query,to delete record from order details fails,and the error says " The sql statement could not be executed because it contains ambigious outer joins.To force one of the joins to be performe first,create a separate query that performs the firts join and then include the query in your sql statement." Could you have a look at my sql ?


    DELETE [order details].*, orders.orderdate, Customers.MyOffice
    FROM (orders RIGHT JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN Customers ON orders.customerid = Customers.Customerid
    WHERE (((orders.orderdate)<#1/1/2006#) AND ((Customers.MyOffice)=True));

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

    Re: delete query (Access 2000)

    Why don't you use INNER JOIN instead of RIGHT JOIN? You're going to delete records from order details, so the right join makes no sense.

Posting Permissions

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