Deleting Dupes (A2K)

Topic

New Reply

Good Morning,

I have a rather unique problem (or I think it’s unique anyway).

A sample table structure is provided:

Our corporate office provides us with member data. We need to send mailings to our members, however, not we don’t want to send a mailing to each member of the household. Therefore, each household only recieves 1 mailing. So we need to remove the duplicate records prior to preparing the labels. We are comparing the address line (Address=Address, City, ST, ZC)

I’ve searched this site and the internet and can’t quite find the suggestion that will remove ALL dupes except 1 (doesn’t matter which one, we just need the address and a name). Many of the suggestions will only work when the only difference in the record is the unique ID, that’s not the situation in our case. Each record will be very different EXCEPT for the address and it’s the address that I want to compare on.

Does anyone have any suggestions on how I can achieve this?

Reply | Quote

Replies

You can do this using two queries.

1) Create a query based on the table.
Add the ID_File, Address_Line1, City, State and Zip fields to the query grid.
Do *not* add any other field to the query grid.
Select View | Totals.
Set the Total option for ID_File to Min. Leave the Total option for the other fields as Group By (the default).
Save this query as (for example) qryUniqueAddresses.
This query returns the unique addresses, plus the lowest ID for each address, without any name information.

2) Create a query based on the table and on the query that you made and saved in step 1.
Join the table and query on the ID_File field.
Add the fields that you want to use from the table to the query grid, or the asterisk * if you want to use all fields.
Do *not* add any fields from the query.
This query will return unique addresses, with the name from the lowest ID for each address.

Reply | Quote

Thanks Hans,

Worked beautifully….it’s greatly appreciated

Reply | Quote