Delete all Odd records 1,3,5,7,9...... (1 Viewer)

[jomuir]

I have been given a spreadsheet that I have imported into Access, and I want to delete quite a large number of rows. The records have a Customer ID field (Primary Key), addr1, addr2, ..... Duplicate.

If the Duplicate filed has a Y in it then there is an address with 2 Customer ID’s (an Old Tennant) however I only want the latest tenant.

So I have run a query that shows all Duplicate = “Y” , sorted by Address 1, then Customer ID...this has given me about 7000 records.

Is there anyway of easily deleting every Odd record i.e. records 1,3,5,7,.......6997, 6999?

SELECT *
FROM [tblCust]
WHERE ((([tblCust].[Duplicate])="y"))
ORDER BY [tblCust].addr1, [tblCust].CustID;

Sorry - I have just been sent an updated spreadsheet and some of the records have more that 2 customers at an address, so I need to delete all duplicates leaving the address and Customer ID, where the Customer ID is the largest number:-

CustID Addr1
2000134 10 BEECHES TERRACE
2005634 10 BEECHES TERRACE
2006023 10 BEECHES TERRACE
2006034 10 BEECHES TERRACE

In this example I would want to delete the first 3 and leave the record with CustID 2006034

 

[DCrake]

Use the table with duplicate records in it in a new make table query make it distinct as well so that any duplicate records are not appended to the new table.

David

 

[jomuir]

Thank you for the quick reply!!

I am not sure where or how I enter the DISTINCT part into the query

SELECT * INTO test
FROM [tblCust]
WHERE ((([tblCust].[Duplicates])="y"));

 

[DCrake]

first make a copy of your database

1. highlight the table you want to replicate and select Edit Copy from the main menu.

2. Select Edit Paste from the main menu but click the option Definition Only.

3. Design the table and set the customer Id to be a primary key.

4. Create an append query and append all from the duplicates table into the new table.

The append will run but thow you an error dialog stating that x number of records could not be appended due to key violations, this is to be expected.

5. Open the new table and check for uniqueness.

6. Rename the new table the old table name and overwite it.

David

 

[jomuir]

Thank you very much!!!

I had to change a couple of things, but it appears to have worked a treat!!!

The duplicate fields where the addr1, with numerous CustID’s– so made addr1 the Primary Key.

First attempt I received all the lowest CustID, so change my append query to Sort Descending for CustID and this seems to have done the trick.

Thanks once again!!