Google-like search capability?

[John Williamson]

We have produced an Access DB application to record visitors to a charity.
Many of the visitors (mostly foreign born) have long names comprised of four
or more individual names; e.g., Aaaaa Bbbbb Ccccc Ddddd. The names were
entered originally by North Americans with little clue as to the preferred
order of the names, so they migh appear in some other order and with
abbreviations, such as: Cccc Dddd Aaaaa B. The legacy data source separated
names into FName LName, but confusion reigns over which constitutes a LName,
so we concatenated them into a single Name field to facilitate searching.
Even so, we find ourselves searching for each individual name, one at a
time, and then scanning the resulting list for the best match with the other
names. I would like to include in our application a 'Google'-like search
capability in which our users can enter several names in a text box, and see
a resulting list of person records in the order in which they best match the
terms. So, four names could be entered in any order, and the search result
would list records that matched all four names first, then those which
matched three of four, then two of four, and then only one. We can handle
parsing the text string into individual search arguments (variables), but we
are looking for some existing models for processing the individual searchs
and the comparing results to sort the best matches. Guess this sounds like
a rudimentary search engine. Appreciate any direction someone could
provide. Best regards, John (I previously posted this in
access.modulesdaovba.ado, but that group is fairly lightly posted. Sorry for
the cross-post.)

 

[HSalim]

John.
Implementing a rudimentary Google like search is certainly possible.
first, add an unbound text box to your form taht will accept a list of names
ideally, you should use a standard delimiter - say space or comma and
disallow other delimiters such as colon.

Now, in the after-update event or at a command button click
follow this coding outline
'please fill in missing items such as dim statements
'use Split to breakout the individual names into an array.
arrNames = Split(me.txtbox, " ")

' setup SQL statements for insert
strInsert = "Insert into Scratch(VisitorId, VisitorName)
strSelect = "Select VisitorID, VisitorName from MyTable where VisitorName
like '"

(use a scratch table to hold your result set, which can be displayed on a
subform)
db.execute("Delete * from Scratch)

for I = lbound(arrNames) to ubound(arrNames)
strLike = arrNames(i) & "*'"
db.execute (strInsert & strSelect & strLike)
next

finally,
me.subform.requery

Since you are looking at names, you might benefit by looking at trying ti
implement a soundex search
which could return all names that sound similar. however, soundex requires
heavy computation
so you should pre-calculate soundex values and store them in your table.
In your case, you might have to store soundex values for at-least two
columns - first name and Last name


HS