Numbering Records Based On Another Field In A Report

[Wayne Aprato]

I posted this yesterday and it seems like a moderator has thrown it in
another thread. This is a totally different question to the one asked
in that thread, so I'm posting it again. It is not a simple
"numbering records on a report" question. It is more complex than
that.

I have a report that shows the results of a query. One of the fields
is an autonumber field from the query which shows for instance: 120,
121 , 122 for 3 records. Is there a way to have another field that
shows the numbering starting from 1 ie. 1, 2, 3? If the records were
sorted so that the first field showed the records in the order: 121,
120, 122 then I would need the second field would show them numbered
as 2, 1, 3. I would also need the second field to follow the numbering
sequence 1, 2, 3 even if the first field skipped a number eg. 120,
122, 123.

Thanks in advance.

[Larry Linson]

"Wayne Aprato" wrote

I posted this yesterday and it seems
like a moderator has thrown it in
another thread.

It may, indeed, _seem_ so, but that is not the case because
comp.databases. ms-access is unmoderated and no one can "rearrange" the
messages. Either you inadvertently responded to a message in the other
thread, or you had an exact duplicate of the subject in that thread and your
newsreader uses subject to identify the thread (this is the case with
http://groups.google.com -- there seem to be some exceedingly long threads
there, but on examination, it turns out to be a large number of actual
threads appended one after the other).

Larry Linson
Microsoft Access MVP

[Larry Linson]

You _can_ create a record number in a Query, using a user-defined function,
but you have to be careful -- a given record may be processed more than once
because of the way the query engine works, so you'll need to check the field
on which you are ordering the Query records within the function.

(Unfortunately, my example of this was lost in the course of a change of
primary computers.)

Almost always, there's some other way than using a record number to
accomplish one's purpose. If you'll explain what you need to do, rather than
how you'd like to do it, perhaps someone can suggest an alternative
approach. If not, well, you can create those numbers as I describe above.

Larry Linson
Microsoft Access MVP
Larry Linson
Microsoft Access MVP

"Wayne Aprato" <cq*******@volc anomail.com> wrote in message
news:4d******** *************** ***@posting.goo gle.com...

I posted this yesterday and it seems like a moderator has thrown it in
another thread. This is a totally different question to the one asked
in that thread, so I'm posting it again. It is not a simple
"numbering records on a report" question. It is more complex than
that.

I have a report that shows the results of a query. One of the fields
is an autonumber field from the query which shows for instance: 120,
121 , 122 for 3 records. Is there a way to have another field that
shows the numbering starting from 1 ie. 1, 2, 3? If the records were
sorted so that the first field showed the records in the order: 121,
120, 122 then I would need the second field would show them numbered
as 2, 1, 3. I would also need the second field to follow the numbering
sequence 1, 2, 3 even if the first field skipped a number eg. 120,
122, 123.

Thanks in advance.