Assistance requested transforming one set of values into another 2

[VickyC]

hello

I have been really struggling with a problem that maps values from one column into values in another column. I'll explain this as clearly as I can: (I'm not even sure that this can be done in SQL, but I thought I'd ask for assistance anyway!)

a) Consider the table shown below. Look at the first 6 integer values in column Val1, corresponding to Case = 1. For every such case, there will be three values = 0, and 3 non-zero entries. These represent MAPPINGS (in the mathematical sense). So...

For case = 1: p maps to q (1 space below), r maps to t (2 spaces below), and s maps to u (2 spaces below) and...

For case = 2: p maps to t (4 spaces below), r maps to u (3 spaces below), and s maps to q (2 spaces ABOVE).

So far, column Val2 is empty.

tblData       Case     ColNum      Val1     Val2
             -------------------------------------
               1          p         1
               1          q         0
               1          r         2
               1          s         2
               1          t         0
               1          u         0

               2          p         4
               2          q         0
               2          r         3
               2          s        -2
               2          t         0
               2          u         0

               3  ...  etc ...

b) What I need to do is transform the values in column Val1, then write the transformed values in column Val2. The TRANSFORMATION RULE is given in the following table:

tblTranspose        V1   V2
                   ----------
                    p     r
                    q     u 
                    r     q
                    s     p
                    t     t
                    u     s

Here's how the transposition rule is applied (referring to tblData, Case = 1): (shown in the table below.)

tblData says 'p maps to q', so tblTranspose changes this to 'r maps to u', which is 3 spaces below it. (recorded in column Val2 below)
tblData says 'r maps to t', so tblTranspose changes this to 'q maps to t', which is 3 spaces below it. (recorded in column Val2 below)
tblData says 's maps to u', so tblTranspose changes this to 'p maps to s', which is 3 spaces below it. (recorded in column Val2 below)

Here's how the transposition rule is applied (referring to tblData, Case = 2): (shown in the table below.)

tblData says 'p maps to t', so tblTranspose changes this to 'r maps to t', which is 2 spaces below it. (recorded in column Val2 below)
tblData says 'r maps to u', so tblTranspose changes this to 'q maps to s', which is 2 spaces below it. (recorded in column Val2 below)
tblData says 's maps to q', so tblTranspose changes this to 'p maps to u', which is 5 spaces below it. (recorded in column Val2 below)

tblData       Case     ColNum      Val1     Val2
             -------------------------------------
               1          p         1        3
               1          q         0        3
               1          r         2        3 
               1          s         2        0
               1          t         0        0
               1          u         0        0

               2          p         4        5
               2          q         0        2
               2          r         3        2
               2          s        -2        0
               2          t         0        0 
               2          u         0        0

               3  ...  etc ...

I just can't seem to figure out how to write the code to fill column Val2.
(by the way, my actual problem uses (0, 1, 2, 3, 4, 5) in the ColNum column, not (p, q, r, s, t, u). I think this
actually simplifies the problem, but it makes describing the problem in this forum more confusing!)


Many thanks for any help, even if it is just to get me pointed in the right direction.

Vicky C.

 

[MajP]

Seems pretty straight forward. I do not have Access on this computer so this is off the top of my head, but here is some code. May need some tweaking.

Private Type Mapping
  colNum1 as integer
  colNum2 as integer
end type

public function getMap(col1 as integer, col2 as integer) as Mapping
  getMap.colNum1 = col1
  getMap.colNum2 = col2
end function

Public function getTransposeMap(map as Mapping) as mapping
  dim rs as dao.recordset
  set rs = currentdb.openrecordset("tblTranspose")  
  rs.findfirst "v1 = " & map.colnum1
  getTranspose.colNum1 = rs!v1
  rs.findFirst "v2 = " & map.colNum2
  getTranspose.colNum2 =rs!v2
end function

Public function getVal2(TransposeMap as Mapping) as integer
  dim rs as dao.recordset
  dim pos1 as integer
  set rs = currentdb.openrecordset(tblData)
  rs.findfirst "colNum = " & transposeMap.colNum1
  pos1 = rs.absoluteposition
  rs.findfirst "colNum = " & transposeMap.colNum2
  getVal2 = rs.absoluteposition - pos1
end function

Public sub loadVal2s
  dim rs as dao.recordset
  dim originalPosition as long
  dim col1 as integer
  dim coll2 as integer
  dim val2 as integer
  dim map as Mapping
  dim transposeMap as mapping
  dim strSql as string
  set rs = currentdb.openrecordset (tblData)
  
  do while not rs.eof
    originalPosition = rs.absolutePosition
    col1 = rs!colNum
    rs.move rs!col1
    col2 = rs!colNum
    map = getMap(col1,col2)
    transposeMap = getTransposeMap(map)
    val2 = getVal2(transposeMap)
    rs.absoluteposition = originalPosition
    rs.edit
      rs.Val2 = val2  
    rs.update
    rs.movenext
  loop
end sub

 

[MajP]

I had about a handful of syntax errors, but it works. Got the same results as you.

Option Compare Database
Option Explicit

Private Type Mapping
  colNum1 As Integer
  colNum2 As Integer
End Type

Public Function getMap(col1 As Integer, col2 As Integer) As Mapping
  getMap.colNum1 = col1
  getMap.colNum2 = col2
End Function

Public Function getTransposeMap(map As Mapping) As Mapping
  Dim rs As dao.Recordset
  Set rs = CurrentDb.OpenRecordset("tblTranspose", dbOpenDynaset)
  rs.FindFirst "v1 = " & map.colNum1
  getTransposeMap.colNum1 = rs!v1
  rs.FindFirst "v2 = " & map.colNum2
  getTransposeMap.colNum2 = rs!v2
End Function

Public Function getVal2(transposeMap As Mapping) As Integer
  Dim rs As dao.Recordset
  Dim pos1 As Integer
  Set rs = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
  rs.FindFirst "colNum = " & transposeMap.colNum1
  pos1 = rs.AbsolutePosition
  rs.FindFirst "colNum = " & transposeMap.colNum2
  getVal2 = rs.AbsolutePosition - pos1
End Function

Public Sub loadVal2s()
  Dim rs As dao.Recordset
  Dim originalPosition As Long
  Dim col1 As Integer
  Dim col2 As Integer
  Dim val2 As Integer
  Dim map As Mapping
  Dim transposeMap As Mapping
  Dim strSql As String
  Set rs = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
  
  Do While Not rs.EOF
    MsgBox IsNull(rs.AbsolutePosition)
    Debug.Print rs.AbsolutePosition
    originalPosition = rs.AbsolutePosition
    col1 = rs!colNum
    rs.Move rs!Val1
    col2 = rs!colNum
    map = getMap(col1, col2)
    transposeMap = getTransposeMap(map)
    val2 = getVal2(transposeMap)
    rs.AbsolutePosition = originalPosition
    rs.Edit
      rs!val2 = val2
    rs.Update
    rs.MoveNext
  Loop
End Sub

Lets see someone else code that off the top of their head in a text editor.

 

[MajP]

BTW comment out the debug.print and Msgbox in the subroutine was used to find an error since I did not specify 'dbopendynaset'. You have to use the second parameter of the openrecordset method because the default is forward only. This does not support the absoluteposition property.

 

[VickyC]

Hey MajP

First let me say MUCH THANKS for your efforts and programming ability! I picked through your first response and found most, but not all, of the minor syntax issues you mentioned in 'version 2'. When I run the new code, I get the Val2 column being identical to the Val1 column. I'm sure there's a minor error somewhere. I think it in in Function getTransposeMap, but I just can't find it! (I think part of my problem is that I don't really know exactly what transposeMap is supposed to represent.)

again, thanks for your efforts
Vicky C.

 

[MajP]

Sorry that was the wrong version. That was where the typos were. I had v1 and v2 in the wrong places.

Option Compare Database
Option Explicit

Private Type Mapping
  colNum1 As Integer
  colNum2 As Integer
End Type

Public Function getMap(col1 As Integer, col2 As Integer) As Mapping
  getMap.colNum1 = col1
  getMap.colNum2 = col2
End Function

Public Function getTransposeMap(Map As Mapping) As Mapping
  Dim rs As dao.Recordset
  Set rs = CurrentDb.OpenRecordset("tblTranspose", dbOpenDynaset)
  rs.FindFirst "v1 = " & Map.colNum1
  getTransposeMap.colNum1 = rs!v2
  rs.FindFirst "v1 = " & Map.colNum2
  getTransposeMap.colNum2 = rs!v2
  Debug.Print "Map: " & MapToString(Map) & " Transpose: " & MapToString(getTransposeMap)
End Function

Public Function getVal2(theCase As Integer, transposeMap As Mapping) As Integer
  Dim rs As dao.Recordset
  Dim pos1 As Integer
  Set rs = CurrentDb.OpenRecordset("Select * from tblData where Case = " & theCase, dbOpenDynaset)
  rs.FindFirst "colNum = " & transposeMap.colNum1
  pos1 = rs.AbsolutePosition
  rs.FindFirst "colNum = " & transposeMap.colNum2
  getVal2 = rs.AbsolutePosition - pos1
  Debug.Print "Val2: " & getVal2
End Function

Public Sub loadVal2s()
  Dim rs As dao.Recordset
  Dim originalPosition As Long
  Dim col1 As Integer
  Dim col2 As Integer
  Dim Val2 As Integer
  Dim Map As Mapping
  Dim transposeMap As Mapping
  Dim strSql As String
  Set rs = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
  
  Do While Not rs.EOF
    originalPosition = rs.AbsolutePosition
    col1 = rs!ColNum
    rs.Move rs!Val1
    col2 = rs!ColNum
    Map = getMap(col1, col2)
    transposeMap = getTransposeMap(Map)
    Val2 = getVal2(rs!Case, transposeMap)
    rs.AbsolutePosition = originalPosition
    rs.Edit
      rs!Val2 = Val2
    rs.Update
    rs.MoveNext
  Loop
End Sub

Public Function MapToString(Map As Mapping) As String
  MapToString = "(" & Map.colNum1 & ", " & Map.colNum2 & ")"
End Function

I am not sure of your results.
Here are mine

Map: (16, 17) Transpose: (18, 21)
Val2: 3
Map: (17, 17) Transpose: (21, 21)
Val2: 0
Map: (18, 20) Transpose: (17, 20)
Val2: 3
Map: (19, 21) Transpose: (16, 19)
Val2: 3
Map: (20, 20) Transpose: (20, 20)
Val2: 0
Map: (21, 21) Transpose: (19, 19)
Val2: 0
Map: (16, 20) Transpose: (18, 20)
Val2: 2
Map: (17, 17) Transpose: (21, 21)
Val2: 0
Map: (18, 21) Transpose: (17, 19)
Val2: 2
Map: (19, 17) Transpose: (16, 21)
Val2: 5
Map: (20, 20) Transpose: (20, 20)
Val2: 0
Map: (21, 21) Transpose: (19, 19)
Val2: 0

For the first one
(16,17)or (P,Q) transpose (18,21) or (R,U) and value 3: OK

now for the Second record it has "Q" and val1 of 0 so Q maps to Q
Q,Q would transpose to U,U. And U is 0 from U and val2 should be 0. You have 3?

For the third (18,20) or (r,t) transposes to (17,20) or (Q,T) and a value of 3. OK

For the fourth (19,21) or (s,u) transposes to (16,19) or (p,s) and a value of 3. You have 0?



It appears what you say in words is the same as I get, but not what you put in val2

 

[VickyC]

hi again MajP - awsome code. The correct values are being written to column Val2, but I still don't believe that they are being moved to the correct rows. Here's what I mean....

For these comments, I'll use ColNum values of p, q, r, s, t, u instead of the actual 0,1,2,3,4,5, just to help keep things a bit clearer.)

In tblData, p has Val1 = 1, so this indicates that it will map down 1 row to q. (We don't move it - this is just a specification for a mapping)
tblTranspose tell us how to CHANGE this mapping, and this new mapping will be shown in column Val2.
tblTranspose says that the mapping 'p-->q' is Transposed to the new mapping 'r-->u'. Now u is 3 rows below r in ColNum, so r has Val2 = 3.

so far, so good, bit here comes a problem...

The next non-zero number in column Val1 tells us what happens to r. The mapping always specifies non-zero numbers moving into '0' slots)

In tblData, r has Val1 = 2, so this indicates that it will map down 2 rows to t.
tblTranspose tell us how to change this mapping, and this new mapping will be shown in column Val2.
tblTranspose says that the mapping 'r-->t' is Transposed to the new mapping 'q-->t'. Now t is 3 rows below q in ColNum, so q has Val2 = 3. (in your solution, you write a 3, but it doesn't get placed in the 'q' row.

I believe the FINAL Val2 column should look like...

tblData       Case     ColNum      Val1     Val2                       tblTranspose        V1   V2
             -------------------------------------                                        ---------- 
               1          p         1        3                                              p    r  
               1          q         0        3                                              q    u 
               1          r         2        3                                              r    q  
               1          s         2        0                                              s    p 
               1          t         0        0                                              t    t 
               1          u         0        0                                              u    s 

               2          p         4        5
               2          q         0        2
               2          r         3        2
               2          s        -2        0
               2          t         0        0 
               2          u         0        0

               3  ...  etc ...

Again, I very much appreciate this help because it involves code considerably beyond my current skill level! I feel like the solution is 99% done thanks to your efforts.

Vicky C.

 

[MajP]

Public Sub loadVal2s()
  Dim rs As dao.Recordset
  Dim originalPosition As Long
  Dim col1 As Integer
  Dim col2 As Integer
  Dim Val2 As Integer
  Dim Map As Mapping
  Dim transposeMap As Mapping
  Dim strSql As String
  Set rs = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
  'Prepopulate zeros in the empty fields
  strSql = "Update tblData Set val2 = 0"
  CurrentDb.Execute strSql
  Do While Not rs.EOF
    originalPosition = rs.AbsolutePosition
    col1 = rs!ColNum
    If rs!Val1 <> 0 Then
       rs.Move rs!Val1
       col2 = rs!ColNum
       Map = getMap(col1, col2)
       transposeMap = getTransposeMap(Map)
       Val2 = getVal2(rs!Case, transposeMap)
       'move to correct row
       rs.FindFirst "case = " & rs!Case & " AND colNum = " & transposeMap.colNum1
       rs.Edit
         rs!Val2 = Val2
       rs.Update
       rs.AbsolutePosition = originalPosition
    End If
    rs.MoveNext
  Loop
End Sub

I was assigning it to the row I started with, but you wanted the row it ended with.

Example (R,T) transposes to (Q,T) with a value of 3. I assumed this would go in the R row (starting) not the Q row. I also now fill each record with zero to start with so that it is not left null.

 

[VickyC]

hi MajP - I can only say how much appreciation I have that you spent so much time on this problem. Your solution works lie a charm! I found it very instructive as well, with lots of ideas I can use in other places.

much thanks
Vicky C.

 

[MajP]

No problem. Glad it was instructive.

 

[MajP]

BTW, can you explain what this is and how you got this datastructure? I have to assume it is dumped from some other program. Maybe there is a better way to convert and store the data on the front end. The whole thing is really strange.

 

[VickyC]

hi MajP - its part of a mathematics project that involves searching for scheduling permutations. These perms are hard to find because unlike simple permutations, they are subject to a large number of constraints. When a 'solution' is found, I can generate additional solutions using the mapping scheme in this problem. This mapping will actually generate 6! = 720 additional solutions by using, in tblTranspose.Val2, all permutations of the 6 Val1 numbers.