Access Code Required For New Validation database

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.

hi,

Do you get a daily data feed? and should your solution build automation into
the daily received records by comparing them with the previous day's records
only.

Regards,
----------------
Haris
http://www.Designing-Systems.com
------------------------------------------

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

Thank you for your reply. The data is exported intoaccess using Business
Warehouse software. Therefore the feed will be two generations of the same
file containing contact records. All records....compared to latest imported
all records. The "new customer No's will be ones to validate.


"Haris Rashid" wrote:

hi,

Do you get a daily data feed? and should your solution build automation into
the daily received records by comparing them with the previous day's records
only.

Regards,
----------------
Haris
http://www.Designing-Systems.com
------------------------------------------

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

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/Articl...Ex1.htm#VBCode .

-- Vincent Johns
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.

You might also check my April, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Vincent Johns" wrote in message
. com...
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/Articl...Ex1.htm#VBCode .

-- Vincent Johns
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.