Using Looping to insert an incremental number into a field

[Linda Ribbach]

Hi,
I need to create a routine that will insert line numbers into a table
e.g. 01,02,03,04,05,06 until a number in another field changes.

Then, when that other number changes again, I want the routine to start
the count over at 01 .
I want this to continue until the end of the table.

I want to call a routine from this query: UPDATE tblLineRecord SET
tblLineRecord.LineNo = "My Routine Placed Here";

I don't how to write a loop that increments the LineNo field until a
certain condition in the RefNo Field occurs. I want this routine to
continue until there are no more records.

To reiterate: "Increment the LineNo field as long as the data in the
RefNo field is the same. Everytime the RefNo field's data changes to
another number set the LineNo field back to 01 and increment it.
Continue this routine until end of file."



Example:
Line No RefNo
01 767CB100303
02 767CB100303
03 767CB100303
01 767CB100304
02 767CB100304
03 767CB100304
04 767CB100304
01 767CB100305
01 767CB100306

Thanks In advance
Linda

 

[Van T. Dinh]

I don't think you can do this in an Update Query since Query processing is,
by nature, a batch processing.

You can however, write a VBA Subroutine to create a Recordset with the
correct ordering and loop through the Recordset and update the LineNos as
required.

Check Access VB Help on Recordset.

 

[Tom Ellison]

Dear Linda:

Please see my response to your earlier post on this problem.

Hi,
I need to create a routine that will insert line numbers into a table
e.g. 01,02,03,04,05,06 until a number in another field changes.

Then, when that other number changes again, I want the routine to start
the count over at 01 .
I want this to continue until the end of the table.

I want to call a routine from this query: UPDATE tblLineRecord SET
tblLineRecord.LineNo = "My Routine Placed Here";

I don't how to write a loop that increments the LineNo field until a
certain condition in the RefNo Field occurs. I want this routine to
continue until there are no more records.

To reiterate: "Increment the LineNo field as long as the data in the
RefNo field is the same. Everytime the RefNo field's data changes to
another number set the LineNo field back to 01 and increment it.
Continue this routine until end of file."



Example:
Line No RefNo
01 767CB100303
02 767CB100303
03 767CB100303
01 767CB100304
02 767CB100304
03 767CB100304
04 767CB100304
01 767CB100305
01 767CB100306

Thanks In advance
Linda

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts