[AccessD] SQL Gurus - Running count.

Huh!  Consider my wrists duly slapped!  Sorry,

But, to be honest, I cannot find my way around the archives. It is quicker
(usually) for me to "stumble" across the answer then to find it in the
archives.

The reason for this (and trust me, I am one of the main culprits) is that
the subject very often doesn't represent the contents.  This is particularly
so when it goes off target and then runs and runs.  Couple this with the
"krap" history on postings and it is hard to find the answers.

Anyway, I wouldn't change it for the world and I know that there are great
guys and gals out there who are always willing to step in.

Thanks Gustav.  Top man!

Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 08 June 2009 22:02
To: accessd at databaseadvisors.com
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com