I
IfOnlyIKnewCode
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.
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.