Others

Use Fuzzy Name Matching in a Microsoft Access SQL Join

Huma Ashraf ChHuma Ashraf Ch Nov 20, 2026 869 views

Question details

Match registrant names in an Access SQL join despite changes in name order, middle initials, and punctuation.

Use Fuzzy Name Matching in a Microsoft Access SQL Join
Product
Microsoft Access
Device & OS
not provided
Scenario
A database query needs to compare registrant names with names in another table.
Observed behavior
A standard LIKE join does not reliably match names when first and last names are reversed, a middle initial is present, or punctuation differs.
Before you start

Identify the name fields and any other available identifiers, such as date of birth, in both tables. The example uses VBA, so enable database content only if you trust the database.

Solution 1Recommended

Create a normalized name key and join saved queries

Use a VBA function and two saved queries to match common two-part names, reversed names, and names with a middle initial.

Access has no built-in fuzzy join. This example creates an exact match on a normalized key; it does not calculate similarity or detect spelling errors.

The function accepts two-part names and three-part names with a one-letter middle initial. It skips other name structures, which should be reviewed separately.

1
Add the name-key function

In Access, select Create > Module and paste the following code into a standard module. Save the module with a name such as modNameMatching: Public Function NameKey(ByVal v As Variant) As String Dim s As String, a As String, b As String, t As String Dim p() As String If IsNull(v) Then Exit Function s = LCase$(Trim$(CStr(v))) s = Replace(s, vbTab, " ") s = Replace(s, ",", " ") s = Replace(s, ".", "") s = Replace(s, "'", "") s = Replace(s, "-", " ") Do While InStr(s, " ") > 0 s = Replace(s, " ", " ") Loop s = Trim$(s) If Len(s) = 0 Then Exit Function p = Split(s, " ") If UBound(p) = 1 Then a = p(0): b = p(1) ElseIf UBound(p) = 2 Then If Len(p(1)) <> 1 Then Exit Function a = p(0): b = p(2) Else Exit Function End If If a > b Then t = a: a = b: b = t End If NameKey = a & "|" & b End Function

2
Save the registrant key query

Select Create > Query Design, switch to SQL View, and enter the query below. Replace the example table and field names with your own, then save it as qRegistrantKeys: SELECT r.*, NameKey([RegistrantName]) AS MatchKey FROM Registrants AS r WHERE Len(NameKey([RegistrantName])) > 0;

3
Save the reference key query

Create another query in SQL View, replace the example names with your reference table and name field, and save it as qReferenceKeys: SELECT m.*, NameKey([MemberName]) AS MatchKey FROM Members AS m WHERE Len(NameKey([MemberName])) > 0;

4
Join and review the results

Create a third query in SQL View and run: SELECT r.*, m.* FROM qRegistrantKeys AS r INNER JOIN qReferenceKeys AS m ON r.MatchKey = m.MatchKey; Compare candidate matches using available fields such as date of birth or address before treating them as the same person.

Create a normalized name key and join saved queries
Review complex names separately: Sorting the two name parts can produce false matches, and ignoring a middle initial can combine different people. Hyphenated, multiword, and other complex names may be skipped or require different rules.
Free Microsoft Office alternative

Review Name Lists with WPS Office

WPS Office is a free, lightweight alternative for working with Office documents and spreadsheets. Its familiar interface and Microsoft Office format compatibility make it easy to move existing name lists into WPS Spreadsheets for review. It does not replace or repair an Access SQL join.

Review exported registrant and reference name lists in WPS SpreadsheetsOpen and work with Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface for a smooth transitionWork with a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Does Microsoft Access have a built-in fuzzy join?

No. Access SQL does not provide a built-in fuzzy join. You can use a VBA normalization or similarity function and join or compare the resulting values in queries.

Will the name-key example match spelling mistakes?

No. It matches identical normalized name parts, including reversed order, but does not measure spelling similarity. A custom similarity function is needed to identify possible spelling variations.

Why are some names missing from the join results?

The example excludes blank names and names other than two-part names or three-part names with a one-letter middle initial. Review those records separately or adapt the VBA function to your data.

How can I reduce false matches?

Compare the joined records using additional fields such as date of birth or address. Review candidates manually when identifiers are missing or names are common.

Can WPS Office run the Access VBA function?

No. The function and Access SQL join must run in Microsoft Access. You can export results to an Excel-compatible file and open it in WPS Spreadsheets for review.