Add query increment field (1 Viewer)

WPM

New member
Local time
Today, 13:31
Joined
Feb 23, 2020
Messages
3
How to Add query increment field
 

plog

Banishment Pending
Local time
Today, 15:31
Joined
May 11, 2011
Messages
11,611
I'm only 75% sure of what you are asking and if I am right with that, then the answer is you can't.

Could you demonstrate with data what you want your query to do? Show me data illustrating your issue, then I can confirm for 100%.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:31
Joined
Oct 29, 2018
Messages
21,358
Hi. If you're asking how to add a sequential number in your query result set, you could try using a subquery, but you'll need a unique value in your data to designate the sequence.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:31
Joined
Feb 19, 2002
Messages
42,974
Are you asking how to increment the value of fldA for all rows? If so you would create an update query. Use the QBE to create queries when you are not familiar with SQL Syntax. Open the query builder. Select the table. Select the column you want to increment. Change the query type from select to update. Access adds an "update to" row. In the "update to" row add

Nz(MyFieldName,0) + 1

Obviously, you need to use your own table and column names. The calculation I supplied takes into account that the field you want to increment might be null. If it is null, the Nz() function will change it to zero. the last step is +1. If you want to increment by 10, 33, 812, or whatever, use that number to replace the 1.

If you only want to update some rows, add selection criteria that chooses which rows should be updated.

Also, think again about your table and process, it is quite likely that they are not designed correctly. You are almost certainly storing a piece of data that should be calculated. For example, you would always store DOB rather than Age. If you store Age instead of storing the date of birth, each new day, some part of your population has the wrong value for age. Age should be calculated in the query by using a function. A function is used because calculating age is more complicated than it sounds and so it is just easier to do in VBA.
 

Users who are viewing this thread

Top Bottom