[AccessD] SQL Gurus - Running count.

Hi Max

Now what is this? An impatient youngster (aged 65?) who can't browse the archive?

Anyway, Jim is right. Creating a temp table with an AutoNumber is much faster than using the traditional subquery.
However, here is the other method from 2001 using a sequential variable and taking care of the issue Charlotte mentions:

  http://databaseadvisors.com/mailman/htdig/accessd/2005-June/035324.html 

and another one here using a static collection which is extremely fast - and smart too - as it can be used in a select query you browse(!):

 http://databaseadvisors.com/pipermail/accessd/2006-May/044208.html

/gustav


>>> max.wanadoo at gmail.com 08-06-2009 21:09 >>>
Any takers please?

Max



-----Original Message-----
From: Max Wanadoo [mailto:max.wanadoo at gmail.com] 
Sent: 08 June 2009 19:14
To: 'Access Developers discussion and problem solving'
Subject: SQL Gurus - Running count.

Hi Guys,

With a select statement, how do I get a running count matching the rows return.
Starting at 1 and incrementing by 1 for each row.
The final count to be equal to the number of rows returned.
The count to be part of the query.

I also want a second Count but this time not incremented.  Just a 1 in a column for each row returned.

Thanks

Max