Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Dupes (A2K)

    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?
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting Dupes (A2K)

    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.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Dupes (A2K)

    Thanks Hans,

    Worked beautifully....it's greatly appreciated
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •