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?