Logistical Help (1 Viewer)

T

terryvanduzee

Registered User.
Local time
Today, 06:32
Joined
Sep 23, 2005
Messages
22
Hello

Scenario

I have 5 fields (F1,F2,F3,F4,F5)
I have numbers 1 through 100 that could show up in each field.
Each row will not contain duplicates, but there could be many instances of the same number for the full scope of the recordset.

Ex:

Number 5 Exists 10 times in the 5000 records
Number 56 Exists 75 times in the 5000 records, but only 7 times in the same record as number 5
Number 95 Exists 45 times in the 5000 records, but only 2 times in the same record as number 5 and 7 times in the same record as number 56

Etc, Etc Etc.

I cannot seem to find the logic that will look through numbers 1 to 100 and compare the number to each field (F1,F2,F3,F4,F5). This would be a count of the number of times each of these numbers shows up in the entire recordset with each of the other numbers (1 through 100).

I tried this in Excel and got some help with it, but it took too long for Excel to calculate the cells.

Any help would be appreciated

Thank you
Terry
 
ajetrumpet

ajetrumpet

Banned
Local time
Today, 08:32
Joined
Jun 22, 2007
Messages
5,638
Terry,

I'm only writing this function for you because this is an incredibly difficult piece of logic to digest, and I love tough problems... :)
Code: 
function TerrysFunction()

  dim tbl as tabldef, rs as recordset, rs2 as recordset
    dim fld as field, x as integer, i as integer, j as integer

set tbl = currentdb.createtabledef("mymatrix")

  with tbl
    .fields.append .createfield("myNumber", dblong)
  end with

for x = 1 to 100
  with tbl
    .fields.append .createfield((cstr(x), dblong)
  end with
next x

      currentdb.tabledefs.append tbl

set rs = currentdb.openrecordset("mymatrix", dbopendynaset)
set rs2 = currentdb.openrecordset("yourNumberTable", dbopendynaset)

  with rs2
    .movefirst

    for x = 1 to 100
      rs.addnew
      rs!myNumber = x
        while not .eof
          for i = 0 to 4
            if .fields(i) = x then
              for j = 0 to 4        
                rs(cstr(.fields(j))) = rs(cstr(.fields(j))) + 1
              next j
                goto NextRecord
            end if
          next i
NextRecord:
            .movenext
        wend
            .movefirst
      [color=red][b]rs[/color][/b].update
    next x
      .close
        [COLOR="Red"][B]rs.close[/B][/COLOR]

  end with

set rs = nothing
set rs2 = nothing

end function
 
Last edited:
T

terryvanduzee

Registered User.
Local time
Today, 06:32
Joined
Sep 23, 2005
Messages
22
Adam

Thanks so much for the reply
I think I follow most of the logic, but Im not sure about it all.

1. It does create a table called mymatrix and creates the fieldnames
2. I place the name of my table where you have "Yournumbertable"
3. I get a 3020 error (Update or CancelUpdate without AddNew or Edit) at .update when I run the code
4. If I add the .addnew above .update, blank records are appended to the "Yournumbertable" instead of records being added to the mymatrix table

Any suggestions as to what may be happening?

Thanks so much
Terry
 
ajetrumpet

ajetrumpet

Banned
Local time
Today, 08:32
Joined
Jun 22, 2007
Messages
5,638
terryvanduzee said:
3. I get a 3020 error (Update or CancelUpdate without AddNew or Edit) at .update when I run the code
Any suggestions as to what may be happening?

Thanks so much
Terry
Click to expand...
Yes, I do.

You need an rs in front of the UPDATE. sorry!! (if that's all I missed here, that's amazing... :))

The code is corrected now...
 
T

terryvanduzee

Registered User.
Local time
Today, 06:32
Joined
Sep 23, 2005
Messages
22
The code does build the matrix (100 x 100). 100 fields (1-100) and 100 rows. However, there is no data in the matrix (no records except the row numbers 1 - 100.

I created a table called table1 where I simply put the 5 columns of data in it.

Any suggestions?

Thanks so much

Terry
 
ajetrumpet

ajetrumpet

Banned
Local time
Today, 08:32
Joined
Jun 22, 2007
Messages
5,638
Post the code Terry, and I'll look at it (I have a feeling there is some syntax out of place, but I won't know what the exact problem is until I look at it)...
 
T

terryvanduzee

Registered User.
Local time
Today, 06:32
Joined
Sep 23, 2005
Messages
22
Hi Adam

Code: 
Function TerrysFunction()

  Dim tbl As TableDef, rs As Recordset, rs2 As Recordset
    Dim fld As Field, x As Integer, i As Integer, j As Integer

Set tbl = CurrentDb.CreateTableDef("mymatrix")

  With tbl
    .Fields.Append .CreateField("myNumber", dbLong)
  End With

For x = 1 To 100
  With tbl
    .Fields.Append .CreateField(CStr(x), dbLong)
  End With
Next x
'Stop
      CurrentDb.TableDefs.Append tbl

Set rs = CurrentDb.OpenRecordset("mymatrix", dbOpenDynaset)
Set rs2 = CurrentDb.OpenRecordset("table1", dbOpenDynaset)
'MsgBox rs2.RecordCount
  With rs2
    .MoveFirst

    For x = 1 To 100
      rs.AddNew
      rs!myNumber = x
        While Not .EOF
          For i = 0 To 4
            If .Fields(i) = x Then
            'Stop
              For j = 0 To 4
                rs(CStr(.Fields(j))) = rs(CStr(.Fields(j))) + 1
              Next j
                GoTo NextRecord
            End If
          Next i
NextRecord:
            .MoveNext
        Wend
            .MoveFirst
      rs.Update
    Next x
      .Close

  End With

Set rs = Nothing
Set rs2 = Nothing

End Function

Thank you
Terry
 
T

terryvanduzee

Registered User.
Local time
Today, 06:32
Joined
Sep 23, 2005
Messages
22
Hi Adam

If this helps at all, you can see in the code, I've included a msgbox for a record count. rs comes up with 1 record in the recordset and rs2 comes up with 0.

Thanks Adam, it is very much appreciated.

Terry
 
T

terryvanduzee

Registered User.
Local time
Today, 06:32
Joined
Sep 23, 2005
Messages
22
My mistake adam, I was putting the msgbox in the wrong place, it does reflect the right number of records (1 and 1095, 2 and 1095, 3 and 1095 etc).

But still does not populate the table with a count of occurences. Im not sure where in the code it actually does a count to update the field value.

Thanks Adam

Terry
 
ajetrumpet

ajetrumpet

Banned
Local time
Today, 08:32
Joined
Jun 22, 2007
Messages
5,638
TERRY...I got it!! I was way off... :)

I didn't test the first function that I put up. I have just made a test run on this one, and it works fine:
Code: 
Function TerrysFunction()

  Dim tbl As TableDef, rs As Recordset, rs2 As Recordset
    Dim fld As Field, x As Integer, i As Integer, j As Integer

Set tbl = CurrentDb.CreateTableDef("mymatrix")

  With tbl
    .Fields.Append .CreateField("myNumber", dbLong)
  End With

For x = 1 To 10
  With tbl
    .Fields.Append .CreateField(CStr(x), dbLong)
  End With
Next x
'Stop
      CurrentDb.TableDefs.Append tbl

Set rs = CurrentDb.OpenRecordset("mymatrix", dbOpenDynaset)
Set rs2 = CurrentDb.OpenRecordset("table1", dbOpenDynaset)
'MsgBox rs2.RecordCount
  With rs2
    .MoveFirst

    For x = 1 To 10
        rs.AddNew
        rs!myNumber = x
          While Not .EOF
            For i = 0 To 4
              [COLOR="SeaGreen"]'It checks to see if the evaluated number is in the record here[/COLOR]
              If .Fields(i) = x Then
              [COLOR="SeaGreen"]'If it's in the record, it counts the occurances of other numbers in the same record here[/COLOR]
                For j = 0 To 4
                  If .Fields(j) <> x Then
                    [COLOR="SeaGreen"]'It updates the matrix with the number of occurances at this line[/COLOR]
                    rs(CStr(.Fields(j))) = IIf(IsNull(rs(CStr(.Fields(j)))), 1, rs(CStr(.Fields(j))) + 1)
                  End If
                Next j
                    [COLOR="SeaGreen"]'Here it goes to the next record in 'table1' because all of the occurances in this record have now been counted[/COLOR]
                    GoTo NextRecord
              End If
            Next i
NextRecord:
              .MoveNext
          Wend
              .MoveFirst
        rs.Update
    Next x
      .Close
        rs.Close

  End With

Set rs = Nothing
Set rs2 = Nothing

End Function
The "number" row tells you what number is being evaluated, and the field headings in "mymatrix" tell you how many times that number appears in the same record with the number being evaluated.

Hope this helps you out... :)
 
Last edited:
T

terryvanduzee

Registered User.
Local time
Today, 06:32
Joined
Sep 23, 2005
Messages
22
Hi Adam

I have tried it with both of those lines and at the point of
Code: 
 If .Fields(i) = x Then
I get the error 3265 : Item not found in this selection. It happens when i = 5

My table has field names 1, 2, 3, 4, 5
I tried using
Code: 
 option base 1
and changing
Code: 
 for i=0 to 4
to
Code: 
 For i = 1 to 5

My fields in the table are number format (long integer) so I tried taking the Cstr out of the code > no change.

any idea what I can check at this point?

Thank you
Terry
 
ajetrumpet

ajetrumpet

Banned
Local time
Today, 08:32
Joined
Jun 22, 2007
Messages
5,638
Terry,

I just updated my post for the last time.

Have you seen the finished product?? I got the same message you got when I ran it the first time.

It works now...(be sure to look at post #10 again close. I completely changed it from what you saw when you posted your last message).

<edit>

I am also using Access 2003.
 
T

terryvanduzee

Registered User.
Local time
Today, 06:32
Joined
Sep 23, 2005
Messages
22
Hey Adam

Thank you so much, works like a charm
I really do appreciate you taking this amount of time and effort to help me out here.
I have also added to your reputation

Again, thank you so much

Terry
 
You must log in or register to reply here.

Similar threads

D
Recordset to update a table with sequential numbers based on group of OrderID
Replies
3
Views
206
Pat Hartman
P
Noson5434
Conditional Formatting
Replies
12
Views
751
theDBguy
theDBguy
B
Retrieve 6 numbers from string
Replies
2
Views
237
arnelgp
arnelgp
ahmedjamalaboelez
Solved F Keys Functions for each object in form
Replies
2
Views
701
The_Doc_Man
The_Doc_Man
J
Solved 7 reports 1 or 2 buttons
2
Replies
21
Views
684
jazsriel
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom