A query that returns the record counter

[Guest]

Hi all

I hope some guru can help me out there. I'm sure this must be a really easy answer but I can't think of it for the life of me

I have a query that retrieves TEAMNAME, and POINTS from a table TEAM and sorts it descending on the points field. This is simply to show a league table of teams with the team having the highest points displayed at the top. All that I want to do is display the actual position of each team in my form (a continuous form). i.e. the team in 1st position to have a '1' next to it etc

Has anyone any ideas how I can do this? I thought I might be able to just use s derived field in my query ??

Any assistance would be greatly appreciated - it's doing my head in

Cheers
Ada

 

[John Viescas]

SELECT (SELECT Count(*) FROM TEAM As T2 WHERE T2.POINTS > TEAM.POINTS) + 1
As Rank, TEAMNAME, POINTS
FROM TEAM
ORDER BY POINTS Desc;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi all,

I hope some guru can help me out there. I'm sure this must be a really

easy answer but I can't think of it for the life of me.

I have a query that retrieves TEAMNAME, and POINTS from a table TEAM and

sorts it descending on the points field. This is simply to show a league
table of teams with the team having the highest points displayed at the top.
All that I want to do is display the actual position of each team in my form
(a continuous form). i.e. the team in 1st position to have a '1' next to it
etc.

Has anyone any ideas how I can do this? I thought I might be able to just

use s derived field in my query ???

 

[John Viescas]

Um. What is Column1? TEAMNAME? If so, then do:

SELECT (SELECT Count(*) FROM TEAM As T2 WHERE T2.TEAMNAME = TEAM.TEAMNAME
AND T2.POINTS > TEAM.POINTS) + 1
As Rank, TEAMNAME, POINTS
FROM TEAM
ORDER BY TEAMNAME, POINTS Desc;

Glad you like the book. Inside Out is even better! (Well, it has about 400
more pages. <s>)
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

John,

I'm trying to make a query number the repeating data. please see below.

Column1 Column2
A 1
A 2
B 1
C 1
C 2
C 3

P.S. I bought your book "Running Microsoft Access 2000". Excellent

starter book for the novice user or good reference material.

 

[Guest]

Just wanted to thank you for that John. I would never have worked that out but when you run through it, it is so simple - that's the beauty of good coding

Cheers
Adam

 

[Guest]

John

You are the man! I did another discussion with some other MVP of access in this forum and he was saying to me that I do not know what I'm doing and it was not possible in access. I was also afraid that I would have to do it in VBA. Thanks for the Help! P.S. There is no other book out there that is easier to read than yours, Lots of pictures and well explained examples. The perfect ingredient for a successful book

Thanks

Corey

----- John Viescas wrote: ----

Um. What is Column1? TEAMNAME? If so, then do

SELECT (SELECT Count(*) FROM TEAM As T2 WHERE T2.TEAMNAME = TEAM.TEAMNAM
AND T2.POINTS > TEAM.POINTS) +
As Rank, TEAMNAME, POINT
FROM TEA
ORDER BY TEAMNAME, POINTS Desc

Glad you like the book. Inside Out is even better! (Well, it has about 40
more pages. <s>
--
John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com
(Microsoft Access MVP since 1993

 

[John Viescas]

Well, that makes my day! If you want to return the favor, got to Amazon.com
and post a positive review:

http://www.amazon.com/exec/obidos/tg/detail/-/0735615136/

THANKS!
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

John,

You are the man! I did another discussion with some other MVP of access

in this forum and he was saying to me that I do not know what I'm doing and
it was not possible in access. I was also afraid that I would have to do it
in VBA. Thanks for the Help! P.S. There is no other book out there that is
easier to read than yours, Lots of pictures and well explained examples.
The perfect ingredient for a successful book.