Deleting Records based on record number

[Raptor136]

Hi all,
Here's a poser.
I have a table that has either 1 or two records with the same info. If I have only 1, then fine. If I have two, then I want to keep the second. example

Fld1 Fld2 Fld3
AAAA xxxx nnnn
AAAA 1234 3456
BBBB uuuu jjjj
CCCC oooo 9999
CCCC pppp 8765

I would want to keep the second instance where Fld1 = AAAA and CCCC. The first instance of each can be safely deleted.
I also want to keep the *only* instance of BBBB.

All help appreciated. Thanks
Phil raptor136@adelphia.net

 

[KenReay]

Hi

In an Access table there is no 'record number' as such, the rows are considered as an unordered list.

To achive Order, you first need a query with an Order By clause to Order your rows in a predictable order so that you can determine which row you wish to delete Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Raptor136]

I understand what you are saying. In the actual tables, I have an autonumber ID as primary key. I can create a Select Query that orders by ID. Once they are in order, Fld1 will sometimes be repeated. The second instance of matching data in Fld1 will always be kept. Does this make sense? Thanks for the reply.

Phil raptor136@adelphia.net

 

[tlbroadbent]

Try this.

Delete TableName.*
From TableName As t1
Where ID <>
(Select Max(ID) From TableName
Where Fld1=t1.Fld1) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.