Return the difference between two “strings” of part numbers

[David Ball]

Hi,

I have a query with three fields; a primary key that is an Activity ID and then two fields that contain the part numbers associated with the Activity ID (PrtNos1 and PrtNos2). One of the part number fields is from the existing database and one is the new requirement.
I am searching for differences so that I can update my data as efficiently as possible.
An example of what might appear in the two part number fields are:
PrtNos1: CN-MSTGN-GN-GN-000, CN-MSTGN-GN-GN-010
PrtNos2: CN-110D0-ST-FM-010, CN-MSTGN-GN-GN-000, CN-MSTGN-GN-GN-010
Is there a way to add another field that returns the difference between PrtNos1 and PrtNos2?
Thanks very much

Dave

 

[sneuberg]

How are you defining "difference"?

Do the fields actually contain multiple part numbers delimited by commas?

Could you show us a few examples of what you want; input and output?

 

[MarkK]

What exactly do you mean by "difference?" The difference between 9 and 6 is 3, so in the case of numbers when we talk about the "difference" it is implied that we do a subtraction.

Between strings there is no such standing rule about what "difference" means. What is the difference between the strings you posted? Difference in length? Difference in what characters appear? Difference in various substrings? Difference in the count of the delimited elements? See what I mean?

 

[Galaxiom]

One way of measuring the difference between two strings is the Damerau-Levenshtein distance. It is based on the number of changes required to get from one string to the other.

Have a look at the function in post 10 of this thread.

The smaller the number, the closer the strings are to being the same.

 

[arnelgp]

are they multivalue fields?

 

[David Ball]

If one string is XX-001, XX-002 and the other is XX-001 the difference is XX-002

Thanks

 

[smig]

You can split each string into Array and match each value in Array2 against all values in Array1.
This will give you the parts in String2 that do not exist in String1

Why do you save the parts numbers like this? Why not in 2 differnet One-Many tables?
This is a bad design

 

[David Ball]

Hi smig,

It is not the design of my database, it is an export from Primavera P6 scheduling software.

Thanks

 

[David Ball]

"You can split each string into Array and match each value in Array2 against all values in Array1.
This will give you the parts in String2 that do not exist in String1"

How would I do that? How do I split the strings into Arrays?

Thanks

Dave

 

[sneuberg]

Try the split function

http://www.techonthenet.com/access/functions/string/split.php

 

[David Ball]

Sorry, I don't even know where to start with any of this. I'm not sure what I'm supposed to do with this Array thing or the Split function?

Thanks

Dave

 

[sneuberg]

You would create a public function in a module that would accept two strings as parameters and return a string which would be the difference you are looking for. You could then put this function in your query as an expression.

The function would first split each input string into an array of strings using the split function Then with nested for each loops it would somehow compare them and form an output array. You would probably need some checks for empty arrays. This output array would be converted to a string with the join function.

If nobody else bites maybe I'll write this function tomorrow but it's my bedtime now.

Good Night and Good Luck

 

[arnelgp]

try this

in your query expression:

PrtDiff: fnDiff([PrtNos1], [PrtNos2])

Public Function fnDiff(s1 As Variant, s2 As Variant) As String

    Dim sSource As String
    Dim sTarget As String
    Dim aSplit() As String
    Dim iLoop As Integer
    Dim bolSwap As Boolean
    
    s1 = s1 & ""
    s2 = s2 & ""
    
    If Len(s1) > Len(s2) Then
        bolSwap = True
        sSource = s2
        sTarget = s1
    Else
        sSource = s1
        sTarget = s2
    End If
    
    If InStr(sSource, ",") = 0 Then
        ReDim aSplit(0)
        aSplit(0) = sSource
    Else
        aSplit = Split(sSource, ",")
    End If
    
    For iLoop = LBound(aSplit) To UBound(aSplit)
        sTarget = Replace(sTarget, aSplit(iLoop), "")
        sSource = Replace(sSource, aSplit(iLoop), "")
    Next iLoop
    If bolSwap Then
        If sTarget = s1 Then
            fnDiff = s1 & ", " & s2
        Else
            sTarget = LTrim(RTrim(Replace(sTarget, ",", "")))
            sTarget = Replace(sTarget, " ", ",")
            fnDiff = sTarget
        End If
    Else
        If sTarget = s2 Then
            fnDiff = s1 & ", " & s2
        Else
            sTarget = LTrim(RTrim(Replace(sTarget, ",", "")))
            sTarget = Replace(sTarget, " ", ",")
            fnDiff = sTarget
        End If
    End If
End Function

 

[David Ball]

sneuberg, thanks very much for taking the time to reply several times.

It all sounds a little beyond my current level but I will investigate.

Dave

 

[David Ball]

Thanks very much, arnelgp, I will try that code.

Dave

 

[MarkK]

Here's a bit leaner code . . .

Public Function fnDiff2(s1 As String, s2 As String) As String
    Dim tmp As String
    
    tmp = TestList(Split(s1, ", "), s2)         [COLOR="Green"]'test 1st list against 2nd[/COLOR]
    tmp = tmp & TestList(Split(s2, ", "), s1)   [COLOR="Green"]'test 2nd list against 1st[/COLOR]
    If Len(tmp) > 0 Then fnDiff2 = Mid(tmp, 3)
End Function

Private Function TestList(v1, s1 As String) As String
    Dim tmp As String
    Dim v
    
    For Each v In v1                [COLOR="Green"]'test each array element[/COLOR]
        If Not InStr(v, s1) Then   [COLOR="Green"] 'if it's not in the other list[/COLOR]
            tmp = tmp & ", " & v    [COLOR="Green"]'then accumulate it[/COLOR]
        End If
    Next
    TestList = tmp
End Function

 

[sneuberg]

amelgp you almost got it. I like the cleaver way it just replaces the matches with empty strings, but it takes out some commas where it shouldn't and leaves a trailing comma sometimes. Examples:

PrtNos1: A
PrtNos2:
PrtDiff: A,

PrtNos1: A,B,C
PrtNos2: C
PrtDiff: AB

PrtNos1:
PrtNos2:
PrtDiff: ,

 

[sneuberg]

MarkK, your version produces errors if one or both of the fields are null. amelgp avoided this by using variants. Also your code seems to be producing the sum rather than the difference. Examples:

PrtNos1: A
PrtNos2: B
PrtDiff: A,B

PrtNos1: A,B
PrtNos2: C
PrtDiff: A,B, C

PrtNos1: A,B,C
PrtNos2: A,C
PrtDiff: A,B,C, A,C

 

[Grumm]

I understand that people try to make it on their own. But I use google first (maybe a bad habit since i'm the only one doing it)
I found this :

http://www.jpsoftwaretech.com/useful-array-functions-for-vba-part-5/

Just scroll to the "Return the Difference of two Arrays" and there you go. A function who will return all different elements. Only trick you must do is putting your data in arrays. (But that isn't really to hard since you use a query (read recordset to populate an array)

I hope this can help you out.

 

[sneuberg]

mr. arnelgp,

Happy to but sad to report this still doesn't do it. You can run the query in the attached database to see the problems.