Removing duplicate records and retain original

[90405]

I would appreciate if anyone could suggest a method to remove duplicate records, I am still a bit of a nove and still would appreciate a simple as possible method.

But essentially, the company can have similar:
product sectors
provincial locations
telephone numbers

I would also be interested in associating a unique numeric code and not the auto nuymber if possible?

If you like I could send you the excel file to give you a better understanding.


AlmasPelliSasDiMassaroAlessandroC
AlmasPelliSasDiMassaroAlessandroEC
AlmasPelliSasDiMassaroAlessandroEC
AlmasPelliSasDiMassaroAlessandroEC

ApisSRL
ApisSrl
ApisSrl
ApisSrl
ApisSrl


AIPASpaAgenziaItalianaPerPubblicheAmministrazioni
AIPASpaAgenziaItalianaPerPubblicheAmministrazioni
AIPASpaAgenziaItalianaPerPubblicheAmministrazioni

AFiorDiPelle
AFiorDiPelle
AFiorDiPelleDiReginatoSonia
AFiorDiPelleDiReginatoSonia

3PRappresentanzaCommercioEsteroSRL
3PRappresentanzaCommercioEsteroSRL
3PRappresentanzeCommercioEsteroSrl

ConfartDiTosanaPAECSAS
ConfartDiTosanaPAECSAS
ConfartDiTosanaPAECSAS
ConfartDiTosanaPAECSAS
ConfartDiTosanaPierachilleECSAS
ConfartDiTosanaPierachilleECSAS

 

[Pat Hartman]

The simplest way to remove duplicates is to create a new table with the same format as the old. In design view set the fields that you don't want duplicated as the primary key. You can define up to 10 fields as part of a primary key. Then use an append query to copy rows from the original table and append them to the new table.

This will not identify all the rows in each of your groups as duplicates. For example, the first group will end up as two rows. If you mean for them to end up as one row, you'll need to do it via code. No rule(s) that I can think of will automatically turn each of the groups into a single row. Once you get rid of the obvious dups, you'll need to do some manual work.

What is your aversion to an autonumber? It is the simplest way to obtain a unique identifier for a record.

 

[90405]

Could you explain the application of the autonumber and primary key as I am a novice and I am getting a bit confused trying to understand and distinguish the advantages and disadvantages, i.e. if I should let access assign an automatic autonumber or otherwise create an autonumber perhaps derived from the company name and then assign this as the primary key?

 

[Pat Hartman]

Primary keys that include "intelligence" such as part of a name followed by a number seem to work in situations where you only have a small number of keys in play at one time. So a small company that has only about 100 active customers will see some value in an "intelligent" key since long time employees will tend to recognize the customer id without having to look it up. Once the number of active companies becomes much larger any benefit is lost. I don't recommend them at all.

If you have a unique identifier that is assigned by some other system, it is fine to use that as your primary key. For example, your payroll system probably assigns EmployeeID's so an Access app that needs Employee data should use the company standard EmployeeID rather than assigning yet another id. If you don't already have a unique id, use an autonumber. They are easy to use and reliable. If you want a fixed length numeric value, you can seed the autonumber of your table by running a query that appends a row with an autonumber value of one less than where you want your numbering set to start. So, if you want a 7 digit number, append a record with an autonumber value of 1000000 and the next number assigned by Jet will be 1000001. Once a real record has been added, you can delete the dummy record. Most companies won't have any trouble with a 9 million range of values.