X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Numbering rows in a query    
post Jan 15 2004, 09:35 PM

UtterAccess VIP
Posts: 722
Joined: 17-January 03
From: São Paulo, Brazil

This sample shows how to create a sequential number field in a query. It's based on Peter Schroeder code, published on Access Advisor many months ago.
Attached File(s)
Attached File  bdQryRowNumber_LC.zip ( 64.06K )Number of downloads: 1887
Go to the top of the page
post Jan 25 2014, 10:13 AM

Posts: 1
Joined: 23-January 14

SELECT (SELECT Count(FieldName) FROM TableName AS R WHERE TableName.IDFieldName>R.IDFieldName ) AS AutoNum,*
FROM TableName
Go to the top of the page
post Jan 25 2014, 11:31 AM

UA Forum + Wiki Administrator
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada

ill your solution work with data similar to that in the original attachment where there is no numeric field?
Go to the top of the page
post Jan 25 2014, 01:36 PM

UdderAccess Admin + UA Ruler
Posts: 19,557
Joined: 27-April 02
From: Upper MI

Glen - after testing Gordon's submission, I find his (Gordon's) response to be accurate - except for the the point thate the > results in the count starting at zero instead of one. By changing the > to a >= the count begins at one. (As you so niftily pointed out in another thread. Thanks!)
han applied to Claudio's table, the resultant SQL appears thus:
SELECT (SELECT Count(CódigoDoCliente) FROM Clientes AS R WHERE Clientes.CódigoDoCliente>=R.CódigoDoCliente ) AS Linha,*
FROM Clientes
(I replaced "AutoNumber" with Claudio's "Linha") Works as advertised. hat_tip.gif
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd March 2018 - 11:38 PM