Complicated (nested) For Next Look to alter data

I have built an interface for entering time sheet information into a shrink-wrapped accounting program. The database in the accounting package allows for multiple batch numbers but they each have to be sequentially correct.

All the time entry from my front end goes into a temporary table. Once verified, the data is exported to the batch table of the accounting package. Of course my table increments record numbers automatically but the batch table that I am going to export into requires that each batch be incremented separately. Since I don't have anyway of knowing how many people are going to be working in any one batch I left the batch sequence field in my table blank with the hopes of being able to use a for next loop to increment the batch sequences.

It sounds very complicated so let me show what I need to this way:

Batch #1
Record 1
Record 2
Record 3

Batch #2
Record 4
Record 5
Record 6
Record 7
Record 8

Batch #3
Record 9
Record 10

I need to loop through my table and increment one value for every record per batch#. Once I reach the next batch number it should start at 1 and increment to the number of records in the specific batch again.

Batch #1
Record 1 BatchSeq = 1
Record 2 BatchSeq = 2
Record 3 BatchSeq = 3

Batch #2
Record 4 BatchSeq = 1
Record 5 BatchSeq = 2
Record 6 BatchSeq = 3
Record 7 BatchSeq = 4
Record 8 BatchSeq = 5

Batch #3
Record 9 BatchSeq = 1
Record 10 BatchSeq = 2

(record number is a auto incremneted number set by SQL server)

I will be doing this code at the very end of time entry (each week) and it is only done one time just before the data is transferred to the "real" database. Any ideas of how the nested For Next Loop should be structured to get my desired results?

I don't see what your problem is. It's just a loop inside a loop.

VB Code:

For iNum As Integer = 1 To 3
   For iCount As Integer = 1 To 10
      MsgBox "OuterNum:" & iNum & "  InnerCount:" & iCount
   Next iCount
Next iNum

I don't know the amount of records or sequences. Last week there were 5 batches each consisting of more then 900 sequences. Next week it could be 3 Batches with 10000+ sequences.

None of the batches have a consistent amount of sequences either. In last weeks table one batch had 957 another had 985 and so on.

The other thing that complicates this for me is that I have to do this in an existing database table. By that I mean there is an empty field there waiting for me to do the look inside a loop right in the table. (I think I have to do an Update or an Alter table but I am not sure yet)