UtterAccess.com
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    
 
   
Luiz_Cláudio
post Jan 15 2004, 09:35 PM
Post#1


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: 1878
 
Go to the top of the page
 
gordon32
post Jan 25 2014, 10:13 AM
Post#2



Posts: 1
Joined: 23-January 14



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


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


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


UdderAccess Admin + UA Ruler
Posts: 19,555
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:
SQL
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    18th December 2017 - 05:17 PM