Table Purge

[Guest]

Hello,

I would like to know if anyone knows of a great utility that can be used to
purge tables of duplicate data? This function also needs to be highly
configurable to allow me to set the fields used for determining this etc.

If there is no tool, does anyone have a code example that does this for a
Access 97 database?

Thank you,

Tracy

 

[Trey Davis]

I'M LOOKING FOR TRCY GORDMAN HAVE YOU SEEN HER

 

[CyberDwarf]

Er... How many parameters do you think you will need?

 

[Guest]

Not many. Duplicates are determined by the primary key which is currently 3
fields.

 

[ChrisM]

Well, if you had a table like this:

RecordNumber KeyField1 KeyField2 KeyField3 .... Other Fields...

Somthing like:

DELETE FROM MyTable OuterLoop
WHERE EXISTS
(
SELECT * FROM MyTable InnerLoop
WHERE InnerLoop.KeyField1 = OuterLoop.KeyField1
AND InnerLoop.KeyField2 = OuterLoop.KeyField2
AND InnerLoop.KeyField3 = OuterLoop.KeyField3
AND InnerLoop.RecordNumber < OuterLoop.RecordNumber
)

Should do the job.

Cheers,

Chris.

 

[Guest]

I should also add that these records will need to be saved. Preferably to a
duplicate table for future reference.

 

[John Vinson]

On Thu, 21 Sep 2006 08:26:03 -0700, Tracy McClarnon <Tracy

Hello,

I would like to know if anyone knows of a great utility that can be used to
purge tables of duplicate data? This function also needs to be highly
configurable to allow me to set the fields used for determining this etc.

If there is no tool, does anyone have a code example that does this for a
Access 97 database?

Thank you,

Tracy

Sounds like you have a specific definition of what constitutes a
duplicate. I know of no general-purpose "utility" to do this.

There are several ways to do this. One - if you want a permanent
solution - is to create a new, empty table with a unique Index on the
combination of fields which identify a duplicate record. Run an Append
query from your current table into the new table; duplicates will be
discarded.

If this is to be a repeated process with different fields - do you
REALLY want to permanently and irrevokably delete the duplicate
records?? If not, might you be able to simply use Queries with the
"Unique Values" property set, or Totals queries grouping on these
fields?


John W. Vinson[MVP]