Add Row Numbers based on certain field

[daffy333]

Good morning,

I would like to make a table from another table 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

.... and so on. I know that I will need a function to do this, but I
can not begin to think how to start it. Has anyone else done this sort
of thing?

Thank you,
Greg

 

[Jeff Boyce]

Greg

?And if the number of records change, won't the "counts" change? You won't
know that if your summary table doesn't get updated every time the main
table gets updated.

Another approach would be to use a Totals query, grouping by Batch, counting
IDs. That should give you an always-current count of how many of each Batch
you have.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[daffy333]

Greg

?And if the number of records change, won't the "counts" change? You won't
know that if your summary table doesn't get updated every time the main
table gets updated.

Another approach would be to use a Totals query, grouping by Batch, counting
IDs. That should give you an always-current count of how many of each Batch
you have.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Good morning,

I would like to make a table from another table 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

... and so on. I know that I will need a function to do this, but I
can not begin to think how to start it. Has anyone else done this sort
of thing?

Thank you,
Greg- Hide quoted text -

- Show quoted text -

Thank you for your reply.

I believe that in your response, you have assumed that the second
table would only report the total counts, as so:

Record--[Batch]--[Count]
1--6114--3
2--6115--2
3--6116--1
4--6117--3

.... but that is not what I need.

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.
That is why I need to write a function to add index numbers to each
record -- and the index resets itself to 1 with each new batch #.

I apologize that I did not make this more clear in my original post,
and again I thank you for your timely reply!