Ranking Within A Totals Query

[Alan Balthrop]

Hello, all. I posted this question several months ago but truly did
not understand the response. I apologize for asking again.


I am creating a database of soccer statistics. In this version, I am
working with a table (named "field") with statistics where each record
is the stats for a certain player ("player") for each game of the
season.

I have determined how to create a query to tell me the number of goals
a player has for the season, and rank them in decending order:


SELECT field.Player, Sum(field.G) AS Goals
FROM field
GROUP BY field.Player
HAVING (((Sum(field.G))>0))
ORDER BY Sum(field.G) DESC;

....but would like to have a column marked "rank" next to the player
column giving the ranking from one to however many (ties would not be
broken).

I am not writing the SQL code. What appeared above was what was
generated by Access 2002. Any help you could provide this complete
novice would be greatly appriciated!!!



Alan Balthrop

 

[[MVP] S.Clark]

Total Access Statistics will calculate this, and much, much, more for you.

http://www.fmsinc.com/products/statistics/index.html


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

See this article in the MS KB:

Q120608: How to Rank Records Within a Query

http://support.microsoft.com/support/kb/articles/q120/6/08.asp

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQEeOAoechKqOuFEgEQLkawCgkk18sHfu4r34PYOkYKjKXY5nUCgAoPKX
tenT64suRDUK25ubjqYtbKxz
=Vtoj
-----END PGP SIGNATURE-----


Alan Balthrop wrote:

...but would like to have a column marked "rank" next to the player
column giving the ranking from one to however many (ties would not be
broken).

< SNIP >

 

[Alan Balthrop]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

See this article in the MS KB:

Q120608: How to Rank Records Within a Query

http://support.microsoft.com/support/kb/articles/q120/6/08.asp

I read the article referenced above and worked through the examples
using the Northwind database. The code provided there does not seem to
work when used on a totals query instead of a table. Any suggestions?