Search a Field for Similar Entries

[zrwolf]

I have a very large database (30,000+ entries) which contain information on baseball players selected in the amateur draft. One of the fields contains their name in the format: Last Name, First Name

I am looking for a way to search this field and display all similar entries. I.e. Wolf, Zack and Wolf, Zachary would appear but not Wolf, John (unless there was also a Wolf, Johnathan or something similar)

Is there a way to do this? I know a few things about VBA from Excel but am relativly new to databases and Access.

Thanks!

 

[Andrew Fergus]

Hi zrwolf, I'm not aware of any fancy way of doing this but there is a way using a query as follows.

Create a new query, add the "players" (or whatever you have called it) table, add the name field to your query and add another field from your table (any field will do, you could even use the name again) to your query. For the purposes of this example I will call the name "name" and the second variable in your query "field2".

In the query, Menu -> View Totals, change the "Total:" for "field2" to "Count", set the "Criteria:" for "field2" to >1.

Assuming you want to match the surname and (say) the first 3 characters of the first name, change the "Field" for the first variable, "name", to this :

=Left([name],4+InStr([name],","))

This will provide a list of possible matches where the surname and first 3 characters of the first name match. You can change this to 4 or 5 or how ever many characters you want - but add 1 extra in the query to account for the space.

Please note that I have not stress tested this solution and don't know how it will respond where the first name is 2 or less characters (it's possible! e.g. Al, Jo, Ng etc.). BTW this would have been a lot easier if the surnames and first names were in differing fields in the table. :wink:

HTH and let me know how you get on.

Andrew

 

[Joe4]

As Andrew's solutions suggests, writing code to do something shouldn't be that difficult. The difficult thing will be determining what the "rules" are. What do you constitute as a match? The first 3 letters of the first name? What about names like "Anthony", which may have a nickname like "Tony"?

As you can see, this can be quite difficult. Computer programs can't think, they can only do what you tell them to.

Normally when creating any sort of database, query, function, etc. you first need to determine what you rules are. Once you have established those, you usually can write code to do that. Establishing those rules will be your big challenge.

 

[Andrew Fergus]

I agree with jmiskey 100% - any database or query etc. is difficult in the absence of rules. As jmiskey suggested, the rules should be set up before you design your database. I actually dithered as to how many characters of the first name I should try and match - hence I gave you the choice. You could write a fancy query that trys to match the first half of all characters after the comma.

However, a fancy query still won't get around the Tony / Anthony problem outlined by jmiskey (as well as Jack/John and people who use just the initial of their first name etc.). Also, what about names such as "Smith, John" versus "Smyth, John" versus "Smythe, John"? These are all very similar but very hard to detect (or for example "Kirby-Jones, Al" versus "Jones-Kirby, Al").

Lastly, you might actually struggle to use my suggested solution - it will provide potential matches but as soon as you modify the query to try and see the original names, then the query will not function. To get aroung this, you could use my query either underneath another query that will show the original names (not tested), or you could use my query to make a table that you then use in another query to see what the original names were.

HTH, Andrew.

 

[zrwolf]

I completely agree that this would be done much easier with a set of standards. Unfortunatly, the entire reason I am building this database is beacuse very little of this information exists and certainly not in a defined format.

I am thankful for your code suggestion, as I think it will at least narrow down the list if I force the first three letters of a last name to be the same. Unfortunatly, it will have to wait until this weekend to be played with.

Thanks again, as always.

 

[zrwolf]

Andrew,

I think your solution is on the right track for what I am trying to do. Using the query I get a list like this...

Aaro 3
Abar 2
Adam 6

and so on...

What I really need is to display the full listings for these so that I can identify which are really duplicates. So the query would give me...

Aaron, Hank
Aaron, Henry
Aaron, Bob
Abarns, Jason
Abarns, Peter

etc...

Thanks for all of your help so far. Please let me know if I need to clarify things better.

 

[Andrew Fergus]

Hi zrwolf,

It looks like you are using Left([name],4) instead of Left([name],4+InStr([name],",")).

No matter, because you can't bring up the original name once you have identified the duplicates per my previous post and I have another solution.

In your names table, add another field called something like "dup_name", format as text, index with duplicates ok.

1st query :
Create a new query
Add the names table
Query -> Update
add dup_name
In the "Update To" section, enter Left([name],4+InStr([name],","))
Save and Run

2nd query :
Create a new query
Add the names table
View Totals
Field 1 = "dup_name"
Field 2 = "name"
Total for field 2 = "Count"
Criteria for field 2 : >1
Save

3rd query :
Create a new query
Add the "names" table, add the 2nd query,
Link the two based on the "dup_name"
First field = "dup_name" from the 2nd query
Second field = "name" from "names" table
Sort both fields as Ascending
Save and View results.

HTH, Andrew.