Remove Duplicate Entries

[levi.rogers]

I am using information pulled from a separate database to create a new database for mail merger purposes. I have created delete queries that go out and delete specific records from the new database to help us get the proper list before setting up the mail merge. However, now I am stuck. We have customers that have multiple accounts with us that are qualified for this mailing, yet each customer needs to receive only one mailing.

The table looks like this essentially.

Table 1

id statCode acct custNumber* addy-etc.


*This is the field in which we can find exact duplicates. What I need to do is remove the duplicate entries and leave at least one of the entries within the table. It does in fact need to remove the whole record, but as I said leave at least one of the records behind.

Can someone please help me to find a solution to this.

I guess I should add that I am not very good with Access and I use it very sparingly.

I assume that the above can be accomplished using a SQL statement.

Any help will be greatly appreciated.

LR


**EDIT** I also needed to add that I do need to exclude 000 00 0000 from being removed because of duplication.

 

[shudini]

What is stored in the ID field? Is that an AutoNumber?

 

[levi.rogers]

A little mistake.

My apologies but there is no idea field.

The account number is defined as the primary key as it has no duplicates.

So ex out the ID number and imaging account number as the primary key field, and no the acct field is not an auto-number it was a part of the import from the other dB

 

[shudini]

Is the Acct Number of number data type then? and if it is the primary key, which acct number would you want to keep?

 

[levi.rogers]

cont.

the acct field is of the number data type. It is not important which of these is kept as they were brought in solely to use as a unique identifier for the table. The custNumber is the field that has the duplicates in it so I would assume the query would be focused on that field, right?


For the record, thanks for your quick response.

 

[shudini]

I would recommend backing up the table just in case this doesn't run right, but i would try the following:

delete * from myTable 
where acctNum not in 
   (select min(acctNum) from myTable group by custNum) 
    and custNum<>"000 00 0000"

I'm not sure i understood the 00000 at the end part that you want excluded, but basically this query is saying delete ever record from the table where it's not the lowest number for that customer number. This way, if there is only one customer number the subquery will list that acctNum since it's by definition the lowest number, and if there are more than one acctNum, it will delete all but the lowest one.

 

[levi.rogers]

hooray

Thanks for your time that worked like a charm.

All Hail The Internet Guru's.