Reputation: 1013
I have a query in MS Access SQL that is grabbing some information from my table. I'd like to include a field in my select statement that shows a integer and increments by X (1,2,3 or other) for each row.
So my data set would look something like:
(If incremented by 2)
Name SKU SortOrder <-This is my Auto Increment Field
Table XYZ123 0
Chair YRZ345 2
Sofa UFD355 4
I'm not storing sort order in my table I just want to generate these values in Access SQL.
Thank you for your help.
Update I've found the following article that explains a few ways to accomplish this through the access program and vba however i'm writting a c# program that's using an Access TSQL query to grab my data and then export into an excel file so i'm not going to be able to add anything to the original database in the Access Program, additionally going the temp table route, they mention it bloating the database.
https://accessexperts.com/blog/2015/07/28/ways-to-do-sequential-numbering-in-access/
I just want a column of sequential numbering that is created in my select statement, is that not possible?
Upvotes: 0
Views: 3150
Reputation: 243
I would recommend do not use SQL to calculate such data. It's bad practice. It would better to add a field into table and perform calculations before sql statement execution if needed.
Anyway can implement function in VBA to do this. Like:
Private m_counter as Integer
Function CounterInit(Byval counter as Integer)
m_counter = counter
End Function
Function CounterGetNext(Byval incr as Integer)
m_counter = m_counter + incr
CounterGetNext = m_counter
End Function
And then use CounterGetNext() in SQL. But you still have to call CounterInit() before sql execution. Use RecordsetType = Snapshot for such query.
Upvotes: 1