Looking for a smart Query!

A

ANGELICA

hello,
I need help with the following please!:
Every month, I download a customer's activity record which is imported to
Access, the problem is that this report provides the same customer's name but
with small changes. e.i "Show Video Ltd" also appears as "Show Video Ltda".
In order to analyze this customer and get sumarized records I need to find a
way to create a "smart query" so it can group records that have very similar
names, probably 90% similar....For you "access' genius" can this be done?
 
T

Tom Wickerath

Hi Angelica,

Is a customer account number included in the data that you download? If not,
can it be included in future downloads? A customer account number is likely
to be a lot more stable versus human beings entering customer names over and
over again.

If an account number is not available, then I'd suggest looking into using
the Soundex function to help identify close matches, which you can then
manually correct. The VBA Developer's Handbook, 2nd Edition, by Ken Getz and
Mike Gilbert includes coverage of the Soundex function, as do many advanced
Access developer books. For example, when I test your two versions of a
customer name with the Soundex function, using a dhcLen value of 4, I get
S134 in both cases:

? dhSoundex("Show Video Ltd",4)
S134
? dhSoundex("Show Video Ltda",4)
S134

Thus, a query that includes the calculated Soundex values would help you
find slight misspellings of names so that you can correct them prior to
running your report. You might need to experiment with the dhcLen value in
case you get too many false positives. My advice is to either have someone
include the customer account number in the downloaded data, or look further
into using the Soundex function to help spot these anomalies yourself.

Tom
_____________________________________________

:

hello,
I need help with the following please!:
Every month, I download a customer's activity record which is imported to
Access, the problem is that this report provides the same customer's name but
with small changes. e.i "Show Video Ltd" also appears as "Show Video Ltda".
In order to analyze this customer and get sumarized records I need to find a
way to create a "smart query" so it can group records that have very similar
names, probably 90% similar....For you "access' genius" can this be done?
 
T

Tom Wickerath

You're welcome!
And Thank You for marking my response as an answer! (Yes, I'm trying to earn
the bronze badge, which is apparently awarded after 50 responses are marked
as an answer).

Tom
_____________________________________
:

thank you!!!
 
Top