Skip to content
Advertisement

Access SQL how to make an increment in SELECT query

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?

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement