Pattern Matching problem 2

[VickyC]

hello to all

tbl_Ref holds thousands of records organized into Sets of 8 records, each having Position 1 to 8.
tbl_Test holds just 1 Set of 8 records. My task is find any matches between the 8 records in tbl_Test and the Sets in tbl_Ref.

 [b]
tbl_Ref                                                  tbl_Test
Set     Posit     DVal                                   Posit     DVal [/b]
 1        1        99                                      1        82
 1        2        11                                      2        44
 1        3        99                                      3        60
 1        4        75                                      4        82
 1        5        55                                      5        51
 1        6        11                                      6        44
 1        7        11                                      7        44
 1        8        75                                      8        60

 2        1        37
 2        2        16
 2        3        18
 2        4        37
 2        5        13
 2        6        16
 2        7        16
 2        8        18

 3        1        etc....

*** Here's the problem. The thing that must be matched is NOT the values of DVal, but a PATTERN formed by these values...

In tbl_Test, we notice that some of the values of DVal form DOUBLETS (Posit 1 & 4, and Posit 3 & 8), some form a TRIPLET (Posit 2, 6 & 7), and there is also a Singleton (Posit 5). I need help adding a PATTERN column to tbl_Test and to tbl_Ref like the table below. D1 means 1st Doublet, D2 means 2nd Doublet, etc...

 [b]
tbl_Test   
Posit     DVal     PATTERN  [/b]
  1        82        D1    
  2        44        T1
  3        60        D2
  4        82        D1
  5        51        S1
  6        44        T1
  7        44        T1
  8        60        D2

Once the PATTERN column is added, it will be easy to find matches between the 2 tables. In the example shown above, tbl_Test matches Set 2 in tbl_Ref. Set 1 comes close to a match, but the Doublets are in the wrong order.

I just can't figure out how to generate values for this PATTERN columns of both tables. Thank you in advance for any help.

Vicky C.

 

[lameid]

To identify your doublets and triplets I'd use a count and group by test value. You might order this query according to how sequencing you need.

Then, I would loop through this query with a recordset, find matches in the source and write the appropriate values (It may help to order the results in a query like the sort order for grouping).

 

[VickyC]

hi lameid. Thanks for your help.

I know that I can use Count and Group By to identify which DVal numbers are Doubles, Triples, etc, but I also need to be able to, say, distinguish one pair of doubles from another. This is where I'm really stuck.

In the 'PATTERN' column of my table, I distinguish between the pair (Posit 1 & 4) and the pair (Posit 3 & 8). This is precisely why Set 1 in tbl_Ref is NOT a match. It has doubles in these same 4 positions, but the pairs are (Posit 1 & 3) and (Posit 4 & 8). So, no match. Set 2 is a match because its pattern of Doublets, Triplets and Singletons is an exact match with tbl_Test.

I can handle the actual matching (in DAO or SQL) if I could create entries for the 'PATTERN' column, as shown above, in both tables.

Vicky C

 

[lameid]

This code is untested but I mean something like the below.

Dim strSQL as string
Dim RSTestGroup as DAO.Recordset
Dim RSTest as DAO.Recordset
Dim DB as DAO.Database
Dim iCount (8) as integer
Dim strPattern as string

strSQL = "Select DVal, Count(DVal) as DValCount " & vbcrlf & _
         "From tbl_Test" & vbcrlf & _
         "Group By Dval"
Set DB = Currentdb()

Set RSTestGroup  = DB.openrecordset(strSQL)

strSQL "Select * From tbl_Test"
Set RSTest = DB.openrecordset(strSQL)

While Not RSTestGroup.EOF
  iCount(RSTestGroup!DValCount) = iCount(RSTestGroup!DValCount) + 1
  
  Do 
    RSTest.Find ("Dval = " & RSTestGroup!Dval)  'May need to vary from find depending on sort order or 
                                                'add order by clauses to Select statments
    Select Case RSTestGroup!DValCount
    Case 1
        strPattern  = "S"
    Case 2
        strPattern  = "D"
    Case 3 
        strPattern  = "T"
    Case Else   
        strPattern  = "X" & RSTestGroup!DValCount & "X"
    End Select
    If RSTest.Nomatch = False Then
       RSTest.Edit
       RSTest!Pattern = strPattern & iCount(RSTestGroup!DValCount)
       RSTest.Update
    End if
  Loop Until RSTest.Nomatch
  RSTestGroup.MoveNext
Wend

 

[VickyC]

hi lameid

This is very nice code. (Picking through it line by line was a good learning experience for me!)

I had to change 'RSTest.Find' ... to 'RSTest.FindNext'.... , because I was getting a compile error at this line.

Also, I added 'RSTest.MoveFirst' immediately after the line 'Loop Until RSTest.Nomatch' to reset the current record at the top before running the loop again.

There is now one small error, but I'm not sure how to fix it. The code properly updates the Pattern column to 'T1' for the 3 values of 44, then 'S1' for the 1 value of 51, then 'D1' for the 2 values of 60, ***but it only updates the 2nd value of 82 to 'D2'. It misses the 1st value of 82 in the first record. I think this is because the 'RSTest.MoveFirst' moves the current record to the first value (82), but then the 'RSTest.FindNext' finds the FOLLOWING, or 2nd value of 82.

My understanding was that FindNext should start at the current record, but it doesn't seem to be doing that???

Any insights would be welcome. Thanks again to lameid for solving 99.999% of this problem!

 

[PHV]

I'd use this sort of loop:

While Not RSTestGroup.EOF
  iCount(RSTestGroup!DValCount) = iCount(RSTestGroup!DValCount) + 1
  RSTest.FindFirst "Dval = " & RSTestGroup!Dval
  Do While Not RSTest.Nomatch
    Select Case RSTestGroup!DValCount
...
    End Select
    RSTest.Edit
    RSTest!Pattern = strPattern & iCount(RSTestGroup!DValCount)
    RSTest.Update
    RSTest.FindNext "Dval = " & RSTestGroup!Dval
  Loop
  RSTestGroup.MoveNext
Wend

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Like PHV noted, Findfirst instead of movefirst should be all you need to correct.

 

[VickyC]

lameid and PVH. Thanks again for some really insightful code. I have another area where I'll easily be able to export these ideas.
Vicky c.