DataBase - Select the "older" cell

Asked By kikeman

20-Nov-09 05:49 PM

Hi Guys,

I have a table "Orders" and I would like to erase the older entry from
for one Customer:

Customer - Order - DateDone
John - 1234 - 08.20.2009.6pm
John - 4567 - 10.20.2009.5pm
John - 8910 - 11.20.2009.7pm

What would be the DELETE/SELECT command that I would use to select the
oldest entry and erase it where the user would be John?
(in this case would be "John - 1234 - 08.20.2009.6pm")

I am using C#. But any suggestion in the command would help me.

Thanks,
--
Eng. Enrique Lopez.

20-Nov-09 06:14 PM

This should do it:

DELETE *
FROM Orders AS O1
WHERE DateDone =
(SELECT MIN(DateDone)
FROM Orders AS O2
WHERE O2.Customer = O1.Customer);

Be sure to switch to datasheet view before executing the query to check that
it will be deleting the correct rows.  And it goes without saying that before
these sort of set operations are undertaken the database should be backed up.

BTW database tables do not have 'cells'.  That's a spreadsheet concept. Tables
have rows with values at column positions in each row.

Ken Sheridan
Stafford, England


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200911/1

20-Nov-09 06:17 PM

Why do you want to delete orders? That seems like a really bad idea.

Assuming OrderNumber is unique, you could write:
(note: untested air code)
delete * from Orders where OrderNumber in (
select Min(OrderNumber from Orders group by Customer)
)

-Tom.
Microsoft Access MVP