Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate Record Exclusion (2000)

    We have a database for Volunteers and there are several cases where there are two volunteers from the same household (currently being identified by a duplicate query run on the Home Phone Field). We want to run labels but only ONE per household. The duplicate query still shows all the duplicate records. Example below:

    Record 1: John Doe
    123 Main Street, Redondo Beach, CA 90266

    Record 2 Jane Doe
    123 Main Street, Redondo Beach, CA 90266

    LABEL: THE DOE FAMILY
    123 Main Street, Redondo Beach, CA 90266

    I need the query to ONLY OUTPUT one combined record not both of them.

    HELP!!!

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Duplicate Record Exclusion (2000)

    If you want to run labels such that you only get one per household, why not query the data on unique addresses instead of home phone numbers? This may be a problem if all of the data, i.e., Phone, Name, address are combined in one record.

    One question, are the names stored as two records, i.e., Last Name as record 1, First Name as record 2, or one record, i.e,. Last Name, First in one record?.

    If each record contains all of the data you indicate in your post, you may want to break the data fields apart to make processing easier.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record Exclusion (2000)

    The First and Last name field are two seperate fields. It needs to be run off of the home phone due to inconsistancies in the address such as Ave vs Avenue, and typos, etc...

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record Exclusion (2000)

    Haven't tried this, but it seems to me it should work. Set up a summary query (click the Summation button in the Query design screen). Set the 'Total' row to Group By for the home phone and First (or Last if you prefer) for all the other fields.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Duplicate Record Exclusion (2000)

    If the data is not normalized as you say, I would try running a distinct query on the Home Phone and Last Name. (So long as the last names are typed correctly). The problem you are going to encounter is that since the data isn't normalized, even if you select the distinct phone and last name to get the single record for a residence, when you join to the addresses, you will still get more then one result. (Another downfall to this method is having multiple people with different last names at the same residence.)

    I would suggest that you review the data and correct the inconsistencies / normalize the data before continuing. Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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