Update table based on counter

[Cloonalt]

I have two tables, table 1 has a counter (autonumber) and table 2 has a sequential set of numbers.

Every time the counter in table 1 increments by 35 I have to update that set of 35 records with the next sequential number from table 2.

Anybody done something like this? Any help appreciated. Thanks.

 

[drkhelmt]

How often does table1 incriment by 35? If it is less than once a day, you might consider puting some code in the onOpen event of a form to compare table2 to table1 and if necessary incriment it.

Another way would be to add basically the same code to the code/form that is making table1 incriment.

I think what you're lookign for is to get the greatest value of table1, then devide by 35 and that should equal the greatest value of table2. If it is one greater than the greatest value in table2, incriment it.

Hope this gets you closer to the solution.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[Cloonalt]

This is just a one-time update - no application involved, and I'm looking for help with the code to do it.

Thanks.

 

[drkhelmt]

Oh, well, in that case...

dim rst as dao.recordset
dim dbs as dao.database
dim i as integer
dim n as long
dim sqlst as string

set dbs = currentdb
set rst = dbs.openrecordset(table1)

i = 1
n = 1
do while not rst.eof
   i = i + 1
   if i = 35 then 'we're on the 35th record
      mx = mx + 1 'incriments the max of table2
      sqlst = "INSERT INTO table2(field1) VALUES (" & n & ");"
      docmd.runsql(sqlst)
      n = n + 1
   end if
loop

Not tested, but I think this is what you want

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[Cloonalt]

Not exactly.

If The counter is between 1 and 35 then for those records a field in that table gets updated to 100001 from the other table.

If the counter is between 36 and 75 then the field for those records gets updated to 100002 and so on.

Actually, I guess I don't need two tables.

Thanks.

 

[drkhelmt]

If you don't need two tables it may just be easier to add a field to table1.

How many records we dealing with? If there's not too many, you may just consier doiing and update query and say if field on is between x and y, update field2 to xy.

Hope this gets you closer to the solution.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[Cloonalt]

Yes, I can update a field, but I've got 70,000 records so I need to write some code to do it.

Thanks.

 

[drkhelmt]

Yeah, that's too many to do manually. OK, then lets change the above code a little...

dim rst as dao.recordset
dim dbs as dao.database
dim i as integer
dim n as long
dim sqlst as string

set dbs = currentdb
set rst = dbs.openrecordset(table1)

i = 1
n = 10001
do while not rst.eof
   i = i + 1
   mx = mx + 1
   sqlst = "UPDATE table1 " _
      & "SET (field2 = " & n & ") " _
      & "WHERE field1 = " & rst!field1 & ";"
   docmd.runsql(sqlst)
   if i = 35 then 'we're on the 35th record
      i = 0
   end if
loop

Is this closer to what you were looking for?

Hope this is closer to the solution.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[Cloonalt]

That code works. Except that I guess I'm not being clear.

If the counter is 1 through 35 then the field gets updated to 1000001 for all 35 of those records.

If the counter is 36 through 70 then the fields gets updated to 1000002 for all 35 of those records.

and so on until the end of the file..

...the updated field increments its value on each group of 35 records.

Thanks.

 

[drkhelmt]

Riiight... thats what I was thinking but not what my code was exactly doing... try this:

dim rst as dao.recordset
dim dbs as dao.database
dim i as integer
dim n as long
dim sqlst as string

set dbs = currentdb
set rst = dbs.openrecordset(table1)

i = 1
n = 1000001
do while not rst.eof
   sqlst = "UPDATE table1 " _
      & "SET (field2 = " & n & ") " _
      & "WHERE field1 = " & rst!field1 & ";"
   docmd.runsql(sqlst)
   i = i + 1
   if i = 36 then 'we're on to the next group
      i = 1 'reset to 1 for the next 35 records
      n = n + 1 'add one to the group number
   end if
loop

Try this... let me know if works. If not, I will build a mock up and play with it.

Hope this is closer to the solution.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[Cloonalt]

Andrew, I modified your code a little bit to get it to move to the next record and it worked like a charm.

Thanks for your help!

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim i As Integer
Dim n As Long
Dim sqlst As String
Dim sql as string

Set dbs = CurrentDb

Docmd.SetWarnings False

sql = "Select Counter, Box from Master order by counter"
Set rst = dbs.OpenRecordset(sql)

i = 1
n = 1000001

Do While Not rst.EOF
With rst
DoCmd.SetWarnings False
sqlst = "UPDATE Master " _
& "SET Box = " & n & " " _
& "WHERE Counter = " & rst!Counter
DoCmd.RunSQL (sqlst)

i = i + 1
If i = 36 Then 'we're on to the next group
i = 1 'reset to 1 for the next 35 records
n = n + 1 'add one to the group number
End If
.MoveNext
End With
Loop

MsgBox "Done"