MS Access inner join with inexact matching (wildca

Question

I have an Access database that I am currently working on. I have 2 tables and I want to combine both tables. The issue I am coming across is that the field I am using to match both tables is not always the same, meaning I will have to use a wildcard and I am not too sure on how to do that.

The names of my two tables are:

ACW,Hold

QMT

Query will have the following fields:

RM Field that is present on both tables.

ACW comes from table ACT,Hold

Avg Hold comes from table ACT,Hold

Score comes from table QMT.

The field I am using is "RM" however, since it is names some of them are first name last name in the first table and last name first on the other table. Also, there is extra characters in some scenarios. Is there a way to accomplish this?

I tried the following with no luck:

    SELECT [ACW,Hold].RM, [ACW,Hold].ACW, [ACW,Hold].[Avg Hold], QMT.Score
    FROM [ACW,Hold] INNER JOIN QMT ON [ACW,Hold].RM = QMT.RM & "*";

Answer 1

The SQL operator that supports wildcards is the LIKE operator, so your query should use it instead of the = operator:

SELECT [ACW,Hold].RM, [ACW,Hold].ACW, [ACW,Hold].[Avg Hold], QMT.Score
FROM [ACW,Hold] INNER JOIN QMT 
    ON [ACW,Hold].RM LIKE QMT.RM & "*";

I just tried a similar query in Access 2010 and it seemed to work as expected.

Update

If you need to perform matching that is more sophisticated than a single LIKE comparison can offer then you could also create a VBA function that accepts the two field values as arguments and returns a Boolean value indicating whether or not they match. For example, with a function like

Option Compare Database
Option Explicit

Public Function DoTheyMatch(product As String, ingredient As String) As Boolean
    Dim result As Boolean
    If product Like ingredient & "*" Then
        result = True
    ElseIf ingredient = "some special thing" And product = "value to match" Then
        result = True
    Else
        result = False
    End If
    DoTheyMatch = result
End Function

you could use that function as the ON condition of the JOIN:

SELECT i.Ingredient, i.Supplier, p.Product 
FROM Ingredients i INNER JOIN Products p 
    ON DoTheyMatch(p.Product, i.Ingredient);

I just tried that in Access 2010 and it worked, too.