MS Access内部联接具有不精确的匹配(通配符或类似字符) [英] MS Access inner join with inexact matching (wildcard or similar)

问题描述

我有一个正在使用的Access数据库.我有2张桌子，我想合并两个桌子.我遇到的问题是，我用来匹配两个表的字段并不总是相同的，这意味着我将不得不使用通配符，但我不太确定该怎么做.

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.

我的两个表的名称是:

ACW，按住

QMT

查询将具有以下字段:

两个表上都存在RM字段.

RM Field that is present on both tables.

ACW来自表ACT，保持

ACW comes from table ACT,Hold

平均保持来自表ACT，保持

Avg Hold comes from table ACT,Hold

得分来自表QMT.

我使用的字段是"RM"，因为它是名称，其中一些在第一个表中是名字姓氏，在另一个表中是名字姓氏.另外，在某些情况下，还会有多余的字符.有没有办法做到这一点?

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 & "*";

推荐答案

支持通配符的SQL运算符是LIKE运算符，因此您的查询应使用它而不是=运算符:

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 & "*";

我刚刚在Access 2010中尝试了类似的查询，它似乎按预期工作.

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

更新

如果您需要执行比单个LIKE比较所提供的更为复杂的匹配，则还可以创建一个VBA函数，该函数接受两个字段值作为参数并返回一个布尔值，指示它们是否匹配.例如，使用类似

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

您可以将该函数用作JOIN的ON条件:

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);

我只是在Access 2010中尝试过，它也起作用.

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

这篇关于MS Access内部联接具有不精确的匹配(通配符或类似字符)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！