Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    36

    Unanswered: Duplicate Search Query

    Hi,

    We have a 'Customer Details' form and on which we record basic details i.e. Name, Address, Tel number etc.

    The problem we are experiencing is that customers are being contacted a number of times because users are too lazy to search the database and are creating new records every time contact is made with a customer, rather than search if any existing details are held. (Each time a new record is created a telephone call is made and letter sent).

    Users have been warned countless times but we have been fruitless in success, what I have found for example is that if they are speaking to Mr Jones, they will search the name Jones find 213 records and create a new records rather than find the one applicable to him. (I know they could then again do a further search but this does not happen)

    Ideally what I would like is that when a user clicks on the 'Create New Record' command button which takes them to a new record. A 'Pop Up' will appear with two field’s i.e. Customer name and postcode (These fields are ones used on the form).

    The user can then enter these to quickly view any records that match the 2 fields.

    Long winded I know but I’d be grateful for any help anyone can give, Please find attached the database used

    Thanks

    Matthew
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ok, here’s my 2 cents:

    1. Split the “Full Name” field into 3 fields. If you’re inclined, add an extra field for title (Mr, Mrs, Dr, etc.). This will make searching faster and more reliable.
    2. There are a number of ways to handle the “duplicate record” creation. I would head it off before it happens. As I am one of those people who likes a fancy, formal user interface, I would give the user some decent search capabilities at the top with a set of unbound controls - they can enter in zip, last name, and whatever else they want. Have a counter display the number of matches, filter the form to show the sub set and make the bound controls read-only. To add a new record, the “matching record” counter should be zero and the data gets transferred from the unbound controls to the bound controls (no need to type it twice). You may opt to give the user an over-ride, but put in a message box regarding accountability for duplicate prospects.
    3. Introduce accountability. When a new record is created, include the user ID of the person who did it and the timestamp. If your folks are using a common terminal or common login, this is more difficult, but if they each use their own machine, then you can grab the user ID from windows. There is a thread in this forum with a function I provided to accomplish this at http://www.dbforums.com/showthread.p...01#post3660501 The function will work with Novell, Windows XP, 2K, 98 and 95.
    4. Make yourself a “check for duplicates” query (there’s a wizard that will generate a basic duplicates query for you).

    It sounds like half the problem you have is without accountability, they have no reason to try harder.

    Good luck
    Last edited by tcace; 07-21-04 at 22:38.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Jun 2004
    Posts
    4
    Generally I find that searching on First name, Last name, and City that you find the most duplicates unless you make a copy of the First name field and reduce the field length to say 3 (still won't catch phonetic but it catches most). Or you can use the first four digits of a zip code (we have a database of about a million people and we rarely have false dupes on those searches).

  4. #4
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Quote Originally Posted by tcace
    1. Split the “Full Name” field into 3 fields. If you’re inclined, add an extra field for title (Mr, Mrs, Dr, etc.). This will make searching faster and more reliable.
    I was give an excel file with firs, last, middle. How can i split them up. I need to
    Ryan
    My Blog

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You can use Access or Excel - the method and commands are similar in each. Personally, I would do it in Excel because you have more flexibility in Excel.

    Use commands such as InStr, Mid$, Left$, Right$ (in Access) or Find, Left, Right, Mid (in Excel) to parse the data. I suggest Excel becasue the data is not uniform, so some doctoring will be required and Access is much more rigid.

    When you're done, you bring it back into Access and match it up using the key field. I have a sample spreadsheet that started the process for you.

    You will have to decide how you want to handle company names (probably stuff it in the last name field) and multiple names on one record (Mr and Mrs).

    Retain the "Full Name Field" just in case you need it later.

    Have fun
    Attached Files Attached Files
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Thanks! I'll see what I can do! Anyway to automate? Because what if a new person comes in?
    Last edited by rguy84; 07-16-04 at 16:30.
    Ryan
    My Blog

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    go to the source of the data - who ever is putting the data in would be the one to seperate it.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  8. #8
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    See I am only setting it up and handing it off...I am only a summer hire
    Ryan
    My Blog

Posting Permissions

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