Numbering Records

[mrfritz44]

I have a table with 51348 records in it. 51348 is a multiple of 66. This table is autonumbered (from 1 to 51348, of course). I need to build a query that lists a derived column (call it AutoNumber2) that numbers the records in batches of 66. The first 66 records would have identical AutoNumber and AutoNumber2 columns. The AutoNumber row 67 would then have an AutoNumber2 value of 1 and each row after it would start counting up to 66 again, etc......

I've come close using the MOD funciton, but the first 2 sets of 66 rows always seem to be out of whack.

Any hints?

Thanks,

Fred

 

[CosmoKramer]

Fred,

Try this in your query:

AutoNumber2: IIf([RealAutoNumberField] Mod 66=0,66,[RealAutoNumberField] Mod 66)

Si hoc legere scis, nimis eruditionis habes

 

[Golom]

Or this

UPDATE tbl Set NewAutonum = IIF(Autonum < 67, Autonum,(Autonum MOD 66) + 1)

 

[mrfritz44]

Thanks a bunch!

 

[CosmoKramer]

Golom,

A slight correction. I think your code should be:

UPDATE tbl Set NewAutonum = IIF(Autonum < 67, Autonum,(Autonum MOD 6[COLOR=red]7[/color]) + 1)

but, I prefer to use numbers in statements like this that have direct meaning to the situation (in this case - 66 records per group)



Si hoc legere scis, nimis eruditionis habes