Add sequential row numbers based on sorted field

[daffy333]

I would like to make a make-table query that counts records using a
type of row number, based on the number in the field [Batch].
For example:

Record--[Batch]
1--6114
2--6114
3--6114
4--6115
5--6115
6--6116
7--6117
8--6117
9--6117

And the second table would look like:
Record -- [Batch]--[Row]
1--6114--1
2--6114--2
3--6114--3
4--6115--1
5--6115--2
6--6116--1
7--6117--1
8--6117--2
9--6117--3

To give more background to what I am doing, I am using Access to
systematically correct an export of Program A, so that the resulting
data can be imported into Program B. While program A does not give an
"index" to each individual record in a batch, Program B requires it.

I believe that I may have to write a function to do this, but if there
was an easier way to do this through a query, I would be extremely
happy!

Thanks in advance for your help,
Greg

 

[Guest]

You can create a function that you can use within the query that return this
sequence

Follow this steps:
=====================
1. Create two global variable within a module
Option Compare Database

Global OldValue As Double
Global MyOldName As String
================================
2. Create a function in a module to return the counter

Function GetNextNum(MyName As String) As Long
If Nz(MyOldName, "") <> MyName Then
OldValue = 1
MyOldName = MyName
Else
OldValue = OldValue + 1
End If
GetNextNum = OldValue
End Function
===============================
In the query add a field

Rank : GetNextNum([Batch])

The Query need to be sorted by the Batch field
===============================