My procedure takes forever and often crashes

[Stapes]

Hi

I am writing a routine to match name & address fields on an input file
with records already on the database.

I have approx. 20000 records on the existing database.

First I open my input file. Then I read the existing database records
into an array.

Then, for each input record, I loop through the array of existing
customers looking for matches.

If a match is found, it is stored in a seperate array.
If a better match is found, it replaces the last.

Once all the existing customers have been tried with this input
record, it writes that best match, if there is one, to an output file.

The whole process takes about 4 minutes with 100 input records.

However, most of the imput files are over 1000 records. When running
these, if I try to do anything else on the PC, or the Screensaver
interrupts, all I get is a blank white screen when I go back to the
Access window.

Is there any way of preventing this?

Or any way of speeding up my process?

Stapes

 

['69 Camaro]

Hi, Steve.

However, most of the imput files are over 1000 records. When running
these, if I try to do anything else on the PC, or the Screensaver
interrupts, all I get is a blank white screen when I go back to the
Access window.

Is there any way of preventing this?

Essentially, Access is putting up a "Do not disturb" sign. It's busy. Come
back when it's done, and you'll see the results. You cannot prevent this,
but you can minimize it by running shorter processes.

Or any way of speeding up my process?

If you can avoid a loop and let the database engine compare the two data
sets in a query and select the "best match," then the query results can be
exported to an output file. This would be much faster than repeatedly
looping through the same rows of the two data sets stored in arrays.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Stapes]

Hi, Steve.



Essentially, Access is putting up a "Do not disturb" sign. It's busy. Come
back when it's done, and you'll see the results. You cannot prevent this,
but you can minimize it by running shorter processes.


If you can avoid a loop and let the database engine compare the two data
sets in a query and select the "best match," then the query results can be
exported to an output file. This would be much faster than repeatedly
looping through the same rows of the two data sets stored in arrays.

HTH.
Gunny

Seehttp://www.QBuilt.comfor all your database needs.
Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
Blogs:www.DataDevilDog.BlogSpot.com,www.D....QBuilt.com/html/expert_contributors2.htmlfor contact
info.

Hi

Your suggested method would work very well if we were only looking for
exact matches, but we are also looking for partial matches, using
wildcards.

Stapes

 

['69 Camaro]

Hi, Steve.

Your suggested method would work very well if we were only looking for
exact matches, but we are also looking for partial matches, using
wildcards.

Wildcards can be used in queries, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Norman Yuan]

Most search/matching can be quite easily and quickly done with well defined
queries. If querie could not meet your exact need, at least you can use
queries to significantly reduce the data amount you have to loop through.

 

[Stapes]

Most search/matching can be quite easily and quickly done with well defined
queries. If querie could not meet your exact need, at least you can use
queries to significantly reduce the data amount you have to loop through.







- Show quoted text -

OK. I am trying to match on 8 fields. i.e:

Name
Address 1
Address 2
Address 3
Town
County
Postcode
Country

I am also looking for partial matches, or cross matches, i.e. Address
1 = Address 2 etc.

With my loop method, I assign a percentage to each match / partial
match & add them all together. i.e. +10% for a match on name. +6% for
a partial match. +10% for a match on Address 1. etc.

I am still trying to get my head round how I could achieve this with a
series of queries.