Access 2000 Query Question

  • Thread starter Thread starter C. Homey
  • Start date Start date
C

C. Homey

Does anyone know if there is a way to do this:

We have a table that has several thousand records, against which we
write many different queries. My boss wants a field in the query that
will automatically number the records sequentially everytime he runs
the query.

Autonumber is not the solution because it assigns a number per record
in the table and that doesn't update when you run queries. (He really
wants it to work like A1+1.)

I haven't a CLUE how this might be accomplished - or even if it can be
accomplished and would appreciate any help that can be offered.
 
Does anyone know if there is a way to do this:

We have a table that has several thousand records, against which we
write many different queries. My boss wants a field in the query that
will automatically number the records sequentially everytime he runs
the query.

Autonumber is not the solution because it assigns a number per record
in the table and that doesn't update when you run queries. (He really
wants it to work like A1+1.)

I haven't a CLUE how this might be accomplished - or even if it can be
accomplished and would appreciate any help that can be offered.
Click to expand...

I'd STRONGLY suggest never letting a boss see a query datasheet!

Instead, create a Report based on the query. In a Report you can put a
textbox with a Control Source of 1 (the number one), and a RunningSum
property of Over All. This textbox will display your running count.
The report can be Previewed to display the results onscreen or
printed, and can be opened from a command button on a form.

It's possible to get a running count in an arbitrary query, but to do
so you need a calculated field using DCount() to count the records up
to the current record:

SeqNo: DCount("*", "your-query-name", "sortfield <= " & [sortfield])

This requires that your query contain a field (which I've called
sortfield) which is in strictly ascending order.
 
Back
Top