Remove 1 set of dup records

[imwalrus38]

I am importing a file from excel into access. The table has about 6300 records. Some records are duplicates or even triplicates. How do I write a querie to leave only 1 of of these records and rmove the duplicates.

Second, I will be importing new files weekly that after I do the above I will need to append to the first table and then remove duplicates. How dod I do this.


thanks

 

[imwalrus38]

I forgot one thing that may be important. To determine a duplicate 4 fields must be exact matches.

 

[Pat Hartman]

Either define those four fields as the primary key for the table or use them to create a unique index. To create a compound primary key, hold down the cntl key and select each of the four fields. When all are selected, press the key icon to create the primary key.

With a unique primary key or index, Access will discard the second and subsequent duplicate records.

 

[imwalrus38]

Didn't work out

I tried the primary key idea for all 4 fields. This is the problem I'm having. Any of those 4 field may have a null value and I need to allow each field to have duplicates. It's only a duplicate if all 4 fields match. I can identfiy the dups with a dup query and group by.

I need to figure out how to keep one record and eliminate all of the duplicated records.

 

[Pat Hartman]

For purposes of eliminating duplicates, you can change all the null values to 0 and change the table default for these fields to be 0 rather than null.

 

[imwalrus38]

That worked great

Thanks for your help.