[AccessD] SQL Gurus - Running count.

Gustav Brock Gustav at cactus.dk
Mon Jun 8 16:32:55 CDT 2009


Hi Max

You are right. A search option is missing.
However, you can often google. Try:

databaseadvisors gustav rowcounter

Now, here's the surprise: Do remember to click here:

<snip>
In order to show you the most relevant results, we have omitted some entries very similar to the 1 already displayed.
If you like, you can repeat the search with the omitted results included.
</snip>

This reveals one more link you don't want to see as it is only a little more than one year old.
Time to join Susan's Club. Yesterday I had to google to find out how to do the inverse of tangens (that's arc tangens you may know) to calculate an angle in the construction of the official Euro sign, €. But what should I do? Haven't used that stuff since the 70s.

/gustav


>>> max.wanadoo at gmail.com 08-06-2009 23:10 >>>
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





More information about the AccessD mailing list