Hi,

I am trying to write a query in MS Access. Say i have a table as
follows

Player, Date_Joined, Games_Player, Won, Lost, Points

As players win/lose they change position on the table.

I want to run a query the selects the Player and there Points AND i
want to add a colomnn to the query that ranks the players by score ie

Rank, Player, Points
1, Joe, 90
2, Rob, 78
3, Jim, 78
3, Bill, 70
4, Sam, 65

OR

Rank, Player, Points
1, Joe, 90
2, Rob, 78
3, Jim, 75
4, Bill, 70
5, Sam, 65

How do I add the Rank column (based on Points) if it does not exist in
any other tables?

Regards

Greg

Hi Greg,

This newsgroup is for FoxPro and Visual FoxPro questions. While some of us
here work with Access I think you would you do better in an Access
newsgroup. There are lots on Microsoft's free news server,
msnews.microsoft.com.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden (AT) mvps (DOT) org, www.cindywinegarden.com

"Greg Thomas" <greg64thomas (AT) hotmail (DOT) com> wrote

Greg,
I assume that you query MS Access from Foxpro, since you post in this group.

Suggestion 1: Use the report variables in Foxpro's report writer and define
a variable which you increment for each row. Example:

Variable name: nRanking
Value to store: nRanking + 1
Initial value: 0

Then include the nRanking in the report and run it. If needed you can direct
the result to a text file for further processing.

Suggestion 2: Use a readwrite enabled cursor. Include numeric column with 0
in your query, then scan through the cursor and replace the value with the
ranking number, which you increment for each row.
select 000 as ranking, <field list> from <alias> into cursor temp order by
<ranking field>
*!* replace ranking with recno() all
*!* or
m.ranking = 0
scan
m.ranking = m.ranking + 1
replace ranking with m.ranking
endscan

Suggestion 3: Simply use the record number from the created cursor - it
holds the rank value, starting from 1.

Krister


"Greg Thomas" <greg64thomas (AT) hotmail (DOT) com> wrote