Question

  Asked: Apr 18 2008   5:53 PM GMT
  Asked by: Samotek


Delete query


Access 2000, Database, DELETE statement

Can you help me solve the following problem. Whenever i have an order where the field Audit = True, i will have to delete all the previous orders having the Audit = True. And only when customerid = 121. With other words i have to delete the previous orders having Audit = true but not the last one.
My query is the following :
SELECT [orders].[orderid], [orders]. FROM orders INNER JOIN Customers ON [orders].[customerid]=[Customers].[Customerid]
WHERE ((([orders].
I have to build a delete query deleting all the previous orders where Audit = True and Customerid = 121.
I need help

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window

Answer Wiki (Improve, edit or add to this answer)


 RATE THIS ANSWER
-1
Click to Vote:
  •   0
  •  -1



You'll want a delete statement along these line.

delete from Orders
where CustomerId = 121 and OrderID <> (select max(OrderId) from Orders where CustomerID = 121)
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Database.

Looking for relevant Database Whitepapers? Visit the SearchOracle.com Research Library.


Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Mrdenny  |   Apr 18 2008  6:10PM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

 

Dwaltr  |   Apr 18 2008  6:40PM GMT

Are you entering the data via a form? If so you can use the Before Insert event (in the Form Property Sheet) to delete all the data prior to inserting the new row. That eliminates the subquery, making your delete statement look like this:

delete from orders where audit=True and customerid = 121 (or Formname.CustomerID)

Using the formname.customerid will allow it to work for whatever customer id is on the form.