Row Count
-
This sounds simply but I haven't found a way of doing it.
I want to create a query that will have a field that shows the row number for each row in that query.
So for example if a recordset contains 10 records that one of the fields will have a value from 1 to 10 as below
Rowcount Data1 data2 data3
1 xxx xxx xxx
2 xxx xxx xxx
3 xxx xxx xxx
4 xxx xxx xxx
5 xxx xxx xxx
6 xxx xxx xxx
7 xxx xxx xxx
8 xxx xxx xxx
9 xxx xxx xxx
10 xxx xxx xxx
Can anyone help me with this one?
-
Why can't you use a counter client side when you display the data?
-
I assume you are using Access 2000 projects as a front end if so then this technique works for me.
I use a VBA function with a static variable and increment the variable every time it is called in a query which is once per line. The function needs to be initialised prior to running it so it cannot be run manually.
Public Function MyCount(vKey a variant) as Integer
STATIC Mkey as variant
STATIC mCounter as Integer
If isNull(vKey) then
mkey=""
mCounter=0
endif
mCounter=mCounter+1
MyCount=mCounter
End Function
-
Thanks for the solution grasshopper.
I managed to implement an alternative way.
As I needed the query for an Access report,
I used a global variable on the report and incremented it on the format event (and decreased it by 1 on the retreat event.)
I also had to initialise the counter variable to zero on the report header format.
-
Even simpler, have a hidden box with data = 1, called tbSeed and a visible text box tbRowNumber with data set to tbSeed and the Running sum set to over all.
No initialisation, no fudges.
-
Wow... that's the simplest solution I have ever seen
. -
If you want a (T-SQL) query and your table "data" has a PK, say ID, you can do this:
select (select count(b.ID) from data b where b.ID <= a.ID) as rowN, a.*
from data a
This is of course if "data" is ordered by "ID" in ascending order. If not:
select top 100
(select top 100 count(b.ID) as c
from data b
where b.ID<=a.ID
order by c) as rowN
, a.*
from data a
order by a.ID
-
Or, if you are lucky enough to develop against 2005 exclusively (I'm not in that category yet
), you could use the new row_number() function. -
Com'on guys... that's not something you're supposed to make the server do... unless the whole task is made on the server which is rarely the case.
-
You don't even have to create an invisible text box, you can just create a text box for the Row Number, then set it's control source to =1 and then set the runningsum property.
-
in SQL Server I would populate a temp table with the additional identity field. I think in MS ACCESS you can create a query over your data and use Autonumber field.
Regards,Yelena Varsha
-
Remi has expressed the right point I must admit - this is, if used for reporting, a client-side issue. However, there are times when having some sort of row number on the server is useful for certain algorithms, etc that you may perform in a proc, etc.
Yelena's answer is also good - create a temp table with the same structure except with an additional column - an identity column seeded at 1. This avoids any multiple table scans and tricky logic regarding ordering, subqueries, etc.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply