HOW TO DELETE DUPLICATE SERIALNO

[pelagic]

Hi there,

I try to delete duplicate serial number in the table
but keep the latest one and I could not get it to work. Could any one give me a hint? Thanks.
I have the following table named
tblSerialNo which has the following data

SerialNo Date
100 10/25/2002
100 12/28/2002
101 9/15/2004
200 7/24/2003
200 1/24/2004

After the deletion the table should contain the following data.
SerialNo Date
100 12/28/2002
101 9/15/2004
200 1/24/2004

 

[BobAtHome]

How do you wish to do the deletions - from within a form using code or with a query?? Or by opening the table and selecting rows to delete?

 

[PHV]

You may try this:
DELETE A.* FROM theTable A LEFT JOIN
(SELECT SerialNo, Max([Date]) As MaxDate FROM theTable GROUP BY SerialNo) M
ON (A.SerialNo=M.SerialNo) AND (A.Date=M.MaxDate)
WHERE M.SerialNo Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pelagic]

Dear BobAtHome,

Thanks for your reply.
If you could show me with query is great.

Thanks again Bob

 

[PHV]

Have you tried my query ?

 

[pelagic]

Dear PHV

I have but I got this error message

Run time error:
No value given for one or more required parameters.

and I am still trying to figure out why?

Thank you very much for your help PHV
I do Appreciate your time.

 

[PHV]

Try to paste the code in a SQL pane of a Query window to discover what is wrong.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pelagic]

PHV

I pasted into SQL pane and this is what I get

could not delete from specified table.

Even though the table name is correct.