Re: Access Code Required For New Validation database
- From: Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
- Date: Sun, 11 Dec 2005 08:51:19 GMT
One of your problems seems to involve comparing two strings to determine if they are not identical but are close enough to each other to be likely alternate spellings of the same name.
I suggest you consider using something like the Soundex code (see, e.g., http://en.wikipedia.org/wiki/Soundex) for comparing these. This should be fairly easy to compute for each name, though I think you'd have to write a VBA function to do it.
The article I cited contains links to related articles; for example, VB code to compute one such function is available at http://www.creativyst.com/Doc/Articles/SoundEx1/SoundEx1.htm#VBCode .
-- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx> Please feel free to quote anything I say here.
IfOnlyIKnewCode wrote:
I am in the process of planning a new database to monitor the quality of data entered on to an order administration system. The data is exported into Access 2000 and that is where I come in. My objectives are as follows;
DUPLICATE RECORDS
The database consists of lots of contacts which can or can not be at the same address. For example, we send x number of catalogues to the same company as we have x number of contacts there. My first job is to identify duplicate contacts. This is made harder becuase if the potential duplicate is at the same address, I only have name to compare. I do hav some ideas and I would like to know if this is the best way to go. The name splits up in to initials, first name, last name. Create different views of the name eg; first 4 characters of the last name, middel 4 characters, last 4 character etc and create "bins" of matches. Depending on the number of bin matches will determine how likely it is that it is a duplicate. Thsi would get round spelling mistakes eg. One account Jon Simmons and another account Jahn Simmons. Is the best way of tackling the problem? I have seen reference to a wizrd called partia duplicate and wonder if this might present a solution.
The second part is to give each user a data score depending on the accuracy of the data entered. I know what I want to acheive in "English" but woud not know where to start in terms of building code;
Example Record Name 1, Address1, Address2, Address3, Tel No, Post Code, Fax No, Account Type
1 - Compare records from yesterday with records today to find new values
2 - Loop through all new records and for each record. If the record is a direct duplicate then no points, otherwise loop through all of the fields to check;
(a) If Something has been entered add to score eg. one point e.g. Name entered, one point added to score (b) Check table of global rules to see if value is the same. By this I was thinking of a table of typical wrongs things that people enter for certains fields
Eg Rule No Field Entry Point Deduction 1 Name N/A 0.25
If the value matches, i.e. N/A is entered then take it away from running score e.g. 1-.25=.75
(c) Once the end of the record has been added append the score to the running score for the user in a summary score table i.e.
Date User No Of Accounts Entered Possible Score Actual Score xxxx Sean 1 1 0.25
As I say, although, I hope my logic is ok, I am unsure how to use code for example looping. I really hope some one can help me as this is my first project in my new job and this seems rather daunting to me.
.
- Follow-Ups:
- Re: Access Code Required For New Validation database
- From: Douglas J. Steele
- Re: Access Code Required For New Validation database
- Prev by Date: Re: Emailing Maintenance Expired Date
- Next by Date: Re: Access Code Required For New Validation database
- Previous by thread: Re: AutoNumber behavior in linked table
- Next by thread: Re: Access Code Required For New Validation database
- Index(es):
Relevant Pages
|
