Access SQL how to make an increment in SELECT query

Question

I Have an SQL query giving me X results, I want the query output to have a coulmn called count making the query somthing like this:

count id section
1     15    7
2     3     2
3     54    1
4     7     4

How can I make this happen?

Answer 1

The only trick I have seen is if you have a sequential id field, you can create a new field in which the value for each record is 1. Then you do a running sum of that field.

Add to your query

DSum("[New field with 1 in it]","[Table Name]","[ID field]<=" & [ID Field]) 
as counterthing

That should produce a sequential count in Access which is what I think you want.

HTH.

(Stolen from Rob Mills here: http://www.access-programmers.co.uk/forums/showthread.php?p=160386)

Answer 2

So in your example, "count" is the derived sequence number? I don't see what pattern is used to determine the count must be 1 for id=15 and 2 for id=3.

count id section
1     15    7
2     3     2
3     54    1
4     7     4

If id contained unique values, and you order by id you could have this:

count id section
1     3     2
2     7     4
3     15    7
4     54    1

Looks to me like mikeY's DSum approach could work. Or you could use a different approach to a ranking query as Allen Browne described at this page

Edit: You could use DCount instead of DSum. I don't know how the speed would compare between the two, but DCount avoids creating a field in the table simply to store a 1 for each row.

DCount("*","YourTableName","id<=" & [id]) AS counter

Whether you go with DCount or DSum, the counter values can include duplicates if the id values are not unique. If id is a primary key, no worries.

Answer 3

I frankly don't understand what it is you want, but if all you want is a sequence number displayed on your form, you can use a control bound to the form's CurrentRecord property. A control with the ControlSource =CurrentRecord will have an always-accurate "record number" that is in sequence, and that will update when the form's Recordsource changes (which may or may not be desirable).

You can then use that number to navigate around the form, if you like.

But this may not be anything like what you're looking for -- I simply can't tell from the question you've posted and the "clarifications" in comments.

Answer 4

Alright, I guess this comes close enough to constitute an answer: the following link specifies two approaches: http://www.techrepublic.com/blog/microsoft-office/an-access-query-that-returns-every-nth-record/

The first approach assumes that you have an ID value and uses DCount (similar to @mikeY's solution).

The second approach assumes you're OK creating a VBA function that will run once for EACH record in the recordset, and will need to be manually reset (with some VBA) every time you want to run the count - because it uses a "static" value to run its counter.

As long as you have reasonable numbers (hundreds, not thousands) or records, the second approach looks like the easiest/most powerful to me.

Answer 5

This function can be called from each record if available from a module.

Example: incrementingCounterTimeFlaged(10,[anyField]) should provide your query rows an int incrementing from 0.

'provides incrementing int values 0 to n
'resets to 0 some seconds after first call
Function incrementingCounterTimeFlaged(resetAfterSeconds As Integer,anyfield as variant) As Integer

Static resetAt As Date
Static i As Integer
'if reset date < now() set the flag and return 0

If DateDiff("s", resetAt, Now()) > 0 Then
    resetAt = DateAdd("s", resetAfterSeconds, Now())
    i = 0
    incrementingCounterTimeFlaged = i
'if reset date > now increments and returns
Else
    i = i + 1
    incrementingCounterTimeFlaged = i
End If    
End Function

Answer 6

autoincrement in SQL

SELECT (Select COUNT(*) FROM table A where A.id<=b.id),B.id,B.Section FROM table AS B ORDER BY B.ID Asc

Answer 7

You can use ROW_NUMBER() which is in SQL Server 2008

SELECT ROW_NUMBER() OVER (ORDER By ID DESC) RowNum,
    ID,
    Section
FROM myTable

Then RowNum displays sequence of row numbers.