Fuzzy Search (2 Viewers)

[triplell89]

I have a list of accounts that I want to compare to itself and find similiar names.

For example, you have a list of words:

Angle
Angel
Orange
Banana
Bananna
Banana1
Blue

I want to produce a query that would compare this list to itself, and Give an ouput like so if the first three letters match, then it will show it in the second column.

Angle-Angle
Angle-Angel
Angel-Angle
Angel-Angel
Orange-Orange
Banana-Banana
Banana-Bananna
Banana-Banana1
.
.
.
Blue-Blue

I would then like it to on display where column 1 <> column 2
and then keep a count on how many times something appears in column 1 in a third column.

I was trying to do this in access, but I'm having no luck. I know I could write a macro in excel to do it, but I'm sure it's possible in access.

Thanks for your help.

 

[dcb]

Start with something like this
SELECT Left([NameField],3) AS Expr1
FROM Table4
GROUP BY Left([NameField],3)
HAVING (((Count(Left([NameField],3)))>1));

 

[triplell89]

Start with something like this
SELECT Left([NameField],3) AS Expr1
FROM Table4
GROUP BY Left([NameField],3)
HAVING (((Count(Left([NameField],3)))>1));

I tried something like this before, all I get is the first three letters of every field...

Then I tried left(namefield,3) = left(expr1,3) but with no avail.

 

[dcb]

all I get is the first three letters of every field

This will only return the first three letters of fields with duplicates

HAVING (((Count(Left([NameField],3)))>1))

 

[triplell89]

SELECT [Table1].NAME, [CopyTable1].NAME
FROM [Table1], [CopyTable1] AS [Copy]
GROUP BY [Table1].CUSTOMERNAME, [Copy].CUSTOMERNAME;

This gives me two columns, however the length of the rows in the query becomes however many entries there are squared, meaning that a large table would become very large (16k to 250M), however the format is what I want. I then want to compare the two columns. If column 1 is similar to column two, I want to display it.

What logic used for "similar" is something else I would want to know.

 

[dcb]

Lookup "LIKE"