Filter Partial Matches in Fields

[fejai]

So I hope this will be an easy one. I have two fields. Both are a combination of text and numbers; and sometimes both share a single piece of information. For example;

Label | Identify
-------------------------
1 Cash-R342
2 Money-R342
3

My question is, how by using criteria can I check these two fields and display them in the same record?
I've tried wildcards but I'm having a hard time as they display anything that shares a single character. Thanks in advance.

 

[PaulBricker]

I don't know of any way to display the values from 2 different records in the same record without writing some serious code. If you want to find the records that have similar value as your example, your sql would look like this

SELECT tbl1.Label, tbl1.Identify
FROM tbl1
WHERE (((tbl1.Label) Like "*" & [enter value] & "*")) OR (((tbl1.Identify) Like "*" & [enter value] & "*"));

This will prompt for a value like R342 which will return all the records where that value is present.


Paul

 

[PaulBricker]

Let me rephrase that. If that values you are looking for can be anywhere in the table then I don't know anyway to put them into the same record. If they are in consecutive records then there might be a way using a subquery.

Paul

 

[fejai]

Hm. Being a newcomer to Access maybe we should go with a different approach than to try and get them to fit into the same row. Maybe simply filtering one field out to show only records that share information with the other field would work. I'm imagining to do this would be a simple criteria statement in the query that compares field 1 to field 2 and shows only matches for the chosen field, but I could be wrong.

You're statement works but not unfortunately not in this situation. I need to have the criteria basically compare the two fields without me having to manually put in values to search for; there are too many records to compare manually. Any ideas?

 

[PaulBricker]

Nothing right off the top of my head but I'll try a few things. Are we talking just two records per comparison or are there multiple records that are likely to have similar values?

Paul

 

[fejai]

There's multiple records that I know for a fact share pieces of information together. I've messed around with combinations of "Like---[tbl.Identity]--" and wildcards but I either get everything matched due to the matching of a single character, or just a mess. I was thinking if somehow I could use wildcards but set a specific criteria of how many characters must be matched in order for the record to show. I'm not sure, but I appreciate the help.

 

[Golom]

What seems to be missing is a clear statement of which parts of the two fields must match.

In your first post something as trivial as

Select A.Label, B.Identify
From tbl A, tbl B
Where Right(A.Label,5) = Right(B.Identify,5)

would work.

I suspect however, that it's not that simple.

You might try the Levenshtein Distance. It computes the number of changes (additions, modifications and deletions) required to transform one string into another. From that, you could develop your own statistic for how many changes (say as a percentage of the total string length) that you would accept as a match. Something like this

Public Function StrMatch( _
       Label As String, _
       Identify As String, _
       Optional LessThanPercent As Single = 10.0) As Boolean

   Dim LD  As Integer
   Dim Pct As Single
   LD = L_Dist(Label, IDentify)
   Pct = LD / Len(Label)

   StrMatch = (Pct <= LessThanPercent)

End Function

'********************************
'*** Compute Levenshtein Distance
'********************************
Public Function L_Dist(ByVal S As String, ByVal T As String) As Integer
    Dim D()   As Integer        ' matrix
    Dim m     As Integer        ' length of t
    Dim n     As Integer        ' length of s
    Dim i     As Integer        ' iterates through s
    Dim j     As Integer        ' iterates through t
    Dim s_i   As String         ' ith character of s
    Dim t_j   As String         ' jth character of t
    Dim cost  As Integer        ' cost

    ' Step 1
    n = Len(S)
    m = Len(T)
    If n = 0 Then
        L_Dist = m
        Exit Function
    End If
    If m = 0 Then
        L_Dist = n
        Exit Function
    End If
    ReDim D(0 To n, 0 To m) As Integer

    ' Step 2
    For i = 0 To n
        D(i, 0) = i
    Next i

    For j = 0 To m
        D(0, j) = j
    Next j

    ' Step 3
    For i = 1 To n

        s_i = Mid$(S, i, 1)

        ' Step 4
        For j = 1 To m

            t_j = Mid$(T, j, 1)

            ' Step 5
            If s_i = t_j Then
                cost = 0
            Else
                cost = 1
            End If

            ' Step 6
            D(i, j) = MinVal(D(i - 1, j) + 1, _
                             D(i, j - 1) + 1, _
                             D(i - 1, j - 1) + cost)

        Next j

    Next i

    ' Step 7
    L_Dist = D(n, m)
    Erase D

End Function

and the MinVal Function is

'-----------------------------------------------------------' Procedure : MinVal
' Purpose   : Return the minimum value of the supplied values
' Arguments : Vals()        - Array of Values
'-----------------------------------------------------------
'
Public Function MinVal(ParamArray Vals() As Variant) As Variant
    Dim X                           As Variant
    Dim MV                          As Variant
    If UBound(Vals) = -1 Then Exit Function
    MV = Vals(0)
    For Each X In Vals
        If Not IsNull(X) Then
            If IsNull(MV) Then
                MV = X
            ElseIf X < MV Then
                MV = X
            End If
        End If
    Next
    MinVal = MV
End Function