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
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.
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).
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.