Sequentially number records in a query, dynamically

[Guest]

Hello, in order to facilitate a report I'm working on, I need create a query
which has a dynamic column to sequentially number the records. The primary
key of the table being reported on is an autonumber field, and is fragmented
due to record deletions. The report groups by 100's, but needs an
unfragmented column in order to do so.

Any ideas and / or suggestions greatly appreciated!

Thanks in advance,

--Jon

 

[Rick B]

Well, if you are using it in a report, jsut create "line numbers" on the
report...


Place an unbound text box on the detail section with the ControlSource
expression to "=1" and the RunningSum property to "Over Group"







Hope that helps,



Rick B

 

[Marshall Barton]

Hello, in order to facilitate a report I'm working on, I need create a query
which has a dynamic column to sequentially number the records. The primary
key of the table being reported on is an autonumber field, and is fragmented
due to record deletions. The report groups by 100's, but needs an
unfragmented column in order to do so.

As long as the sort order is on a unique field, you can use
a subquery to calculate a sequential number:

SeqNum: (SELECT Count(*) FROM table As X WHERE X.ID <=
table.ID)

 

[Guest]

Dude, you're good! Works like a charm.

Thanks!

As long as the sort order is on a unique field, you can use
a subquery to calculate a sequential number:

SeqNum: (SELECT Count(*) FROM table As X WHERE X.ID <=
table.ID)

 

[Guest]

Hi, jumping in on this post, and not really understanding the SQL (although
the problem is the same as mine). I have a single field table where all the
records are different. I assume in the SQL below the table would be the name
of the table however what is x? Also can I use the single field name in
place of the ID field in the code below.

Thanks in advance

Ant

 

[Guest]

oh forgot to say my field is text rather than numeric which might be part of
the reason it doeasn't work. Maybe there is a function that lets you use get
the record number?

Cheers

Ant

 

[Guest]

Have now sorted it, thanks for your code, now understand it which makes the
task a lot easier!

 

[Marshall Barton]

Yes, you would replace "table with the name or your table
and ID eith the name or your field.

The X is just an alias name and can stay the way it is.

As long as you want the records ordered (sorted) in
alphabetical order, it's fine if the field is a text field.