Similar Names

[Christine Boissoin]

My table contains a list of unique last names. I want to write a query that will return all names who have the same first (5) characters. For example:



Smith
Smithe
Smithers

Thanks.
Christine

[rpeare]

In your query put this in:

FirstFive: left(lastname,5)

Where lastname is the name of your surname field.

Then in the criteria put in

[Enter the first five characters to search]

note this won't work if the person puts in anything but five characters but you can mitigate that with a LIKE

like [Enter UP TO the first five characters of the last name] & "*"

this still won't work if they put in 6 characters but it handles anything less than or equal to 5 characters.

[Christine Boissoin]

Thanks.

I was hoping to have the query return all examples where names are similar without any run-time involvement.

Smith
Smithe

and

Wilks
Wilkson

etc.

[rpeare]

Then create a stand alone query that just has two fields, FirstFive as listed in my previous post and TotalCounted: count(lastname)

Link this to your existing query (also with the firstfive field) through the firstfive field and in the criteria of the firstfive from the query above put in >1

[Christine Boissoin]

Perfect - thanks very much for the assistance.
Christine