[AccessD] Sequence number in query

Hi Rocky

Well, yes, this _is_ the "trick". No magic though, just a modification of your query. Should be piece of apple pie for you ...

There are variations, of course, one using DCount() which basically does the same.

Another one, much faster for large recordsets, is using an external function to generate the running number. Most of these, however, fail if you use the query for viewing or somehow else browse the records or move backwards. 
If this is a problem for you, I have a function (published here some years ago) using a Static which overcomes this limitation.

/gustav

>>> bchacc at san.rr.com 21-01-2006 23:13 >>>
Gustav:

I'm trying to recall some trick that I've used before in the QBE grid to 
creating a column which numbers the records retrieved from 1. 

Rocky


Gustav Brock wrote:
> Hi Rocky
>
> If you don't have that many records this simple method may do if you have some unique key:
>
>   SELECT 
>     *,
>       (SELECT 
>         COUNT(*) 
>       FROM 
>         tblTable AS T 
>       WHERE 
>         T.[key] <= tblTable.[key];) AS 
>     SeqID
>   FROM 
>     tblTable
>   GROUP BY 
>     tblTable.[key]
>   ORDER BY 
>     tblTable.[key];
>
> /gustav
>
>   
>>>> bchacc at san.rr.com 21-01-2006 18:32:21 >>>
>>>>         
> Dear List:
>
> There is a trick to creating a column in a query with a sequence number 
> 1, 2, 3, 4... but I can't remember what it is.  Does anyone recall this?