CODEWITHSUNDEEP
ms-accessvba
thisIsSteve
thisIsSteve

Reputation: 23

How can I efficiently compare string values from two tables in Access?

I have created a VBA function in Access 2010 to compare a list of terms in one table against a list of terms in another table. If the values are alike (not necessarily an exact match), I sum the value from a column from the second table for each match. TableA has approximately 150 terms. TableB has approximately 50,000 terms with a related count (integer).

Example tables:

TableA             TableB
---------          ----------
ID                 ID
Term               Term
                   Count

I have a simple SQL query which calls a VBA function to compare the terms and SUM the count if they have a fuzzy match.

SQL:

SELECT TableA.[Term], TermCheck(TableA.[Term]) AS [Term Count] FROM TableA ORDER BY 2 DESC;

VBA:

Option Compare Database

Public Function TermCheck(Term) As Long

    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("TableB", dbOpenDynaset)
    Dim ttl As Long
    ttl = 0

    With rst
        While Not .EOF
            If rst(1) Like "*" & Term & "*" Then
                ttl = ttl + rst(2)
            End If
            .MoveNext
        Wend
    End With

    rst.Close
    Set rst = Nothing

    CurrentDb.Close

    TermCheck = ttl

End Function

The issue I have is that it uses about 50% of my CPU and I'd like to make it as lightweight as possible. Is there a more efficient way to accomplish this task using Access? Moving to a purely SQL alternative is not an option at this point, although it would make me happier. I'm not an Access or VBA guru, but feel that I'm missing something obvious in my query that would improve performance.

EDIT:

The expected result would list out all terms in TableA with a sum of the count column from TableB where a fuzzy match occurred.

Example Data:

TableA
-------------
1 blah
2 foo
3 bar
4 zooba

TableB
-------------
1 blah    16
2 blah2   9
3 foo     7
4 food    3
5 bar     3

Example result:

Term          Count
---------------------
blah          25
foo           10
bar           3
zooba         0

Upvotes: 2

Views: 8685

Answers (3)

js-ak
js-ak

Reputation: 1

I typically build an expression using iif:

TestFlag:iif([TableA]![Term] = [TableB]![Term],"Same","Different")

Upvotes: 0

HansUp
HansUp

Reputation: 97101

SELECT
    TableA.Term,
    Nz(subq.SumOfCount, 0) AS Count
FROM
    TableA
    LEFT JOIN
    (
        SELECT a.Term, Sum(b.Count) AS SumOfCount
        FROM TableA AS a, TableB AS b
        WHERE b.Term ALike '%' & a.Term & '%'
        GROUP BY a.Term
    )  AS subq
    ON TableA.Term = subq.Term;

Edit: I used ALike and standard ANSI wild card character. That allows the query to run correctly regardless of whether it is run from SQL-89 or SQL-92 mode. If you prefer the * wild card, use this version of the WHERE clause:

WHERE b.Term Like '*' & a.Term & '*'

Note that will only do the matching correctly when run from SQL-89 mode.

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91316

On these lines?

SELECT ta.ID, tb.Term, ta.Term, tb.Count
FROM ta, tb
WHERE ta.Term Like "*" & tb.term & "*";

ID  tb.Term  ta.Term  Count
2   hat      hat      2
3   hat      the hat  2
3   the hat  the hat  4
4   mat      mat      6
5   mat      matter   6
5   matter   matter   8

Upvotes: 1

Related Questions