Incrementing Expression Field in Query (Access 2002 SP-2)

Topic

New Reply

Occasionally, I’d like to add a field to a query that results in a sort of record index value in the query result. That is the result of the query might look something like:

1 George
2 Mary
3 Ralph
4 Sally

etc.

where the 1,2,3,4,… doesn’t come from a source table, but rather from a calculation; something like Index:Index+1 (which doesn’t work, but hopefully you get the idea).

Can this be done?

Reply | Quote

Replies

Users should never see a query directly. If you want to do this in a form, see HOW TO: Display Line Numbers on Subform Records in Access 2002.

Reply | Quote

Hans,

I agree. In this case, I’m using this just for my day-to-day “use-Access-as-a-tool-to-do-my-job” rather than building “finished” applications. Sometimes I ‘d just like to number the records to be shown on a form or report or just added to a table or query result. My workaround has been to make a temporary table with the query results and a new index field, then use VBA code to step through the table records (in the desired order) and increment this new field (this seems to be similar to the approach used in the link you provided). I was just hoping there might be a way to build this into the Make Table query and avoid the stepwise VBA routine.

Certainly, if the end result is a report, the incrementing could be done independent of the source table/query using a calculated field or just a label and assigning it in the format or print event routine. And your link shows how to do it (using stepwise VBA) for forms.

Thanks.

Reply | Quote

In a report, you don’t need these tricks at all. You can easily number records in a report, using a text box with Control Source set to =1 and Running sum set to Over All or Over Groups, depending on the desired scope of the numbering.

But in a query, there is no “natural” way to create a record number.

Reply | Quote

Good idea (as usual!) for the report record number. Thanks, Hans. I guess there’s some consolation in the fact that I apparently wasn’t overlooking some “no brainer” solution for the query problem…

Reply | Quote

Good idea (as usual!) for the report record number. Thanks, Hans. I guess there’s some consolation in the fact that I apparently wasn’t overlooking some “no brainer” solution for the query problem…

Reply | Quote

In a report, you don’t need these tricks at all. You can easily number records in a report, using a text box with Control Source set to =1 and Running sum set to Over All or Over Groups, depending on the desired scope of the numbering.

But in a query, there is no “natural” way to create a record number.

Reply | Quote

Hans,

I agree. In this case, I’m using this just for my day-to-day “use-Access-as-a-tool-to-do-my-job” rather than building “finished” applications. Sometimes I ‘d just like to number the records to be shown on a form or report or just added to a table or query result. My workaround has been to make a temporary table with the query results and a new index field, then use VBA code to step through the table records (in the desired order) and increment this new field (this seems to be similar to the approach used in the link you provided). I was just hoping there might be a way to build this into the Make Table query and avoid the stepwise VBA routine.

Certainly, if the end result is a report, the incrementing could be done independent of the source table/query using a calculated field or just a label and assigning it in the format or print event routine. And your link shows how to do it (using stepwise VBA) for forms.

Thanks.

Reply | Quote

Users should never see a query directly. If you want to do this in a form, see HOW TO: Display Line Numbers on Subform Records in Access 2002.

Reply | Quote