CODEWITHSUNDEEP
c#ms-access
UserSN
UserSN

Reputation: 1013

MS Access Database, Sequential Number in Select query

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

Answers (1)

Sergey
Sergey

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

Related Questions