League Table

[JR]

Hi, any help would be much appreciated here.

i have a table, containing fields NAME,DIVISION,GAMES,
POINTS

48 records through 4 divisions.

GAMES and POINTS are updated through an update query from
a separate results table. when i run this update query i
would also like it to add a fith field called POSITION to
be created and updated dynamically acording to the number
of points each person has, and also relating to their own
division only.

eg division1 has 12 players ordered high to low giving
number 1 through 12... division2 has 12 players ordered
high to low giving number through 1 to 12, etc etc

clear as mud... hope someone can help,

thanks in advance
J

 

[Ken Snell]

First, don't use Name as the name of a field or control. It's a reserved
word in ACCESS and you can confuse ACCESS when you use it.

Second, as you're already storing the points, just use a query to display
the records based on descending order of the POINTS value. Why store a value
that will continually change?

 

[JR]

Thanks for taking time to reply Ken...

i dont use NAME as field really - just tried to simplify
my problem a bit by saying name (Its actually FIRSTNAME &
LASTNAME)

my reason for wanting it stored is to be able to use it
as a column value in ASP website, where a player profile
page could show say for example their current league
position as a value.

sorting by points also gives the problem of firstly
sorting by division too as the whole table contains every
player from divisions 1-4

also i want the update to automatically change the number
to reflect their position within the division - so i can
simply update the results each week and everything else
updates itself.

Thanks
J

 

[Ken Snell]

Well, let me see if I get you started with how to calculate a rank in a
query, and then you can work with the criteria and ordering to get the
desired result.

What you want is to create a calculated field (call it Ranking) in the
query, where this calculated field uses a subquery to return the ranking
number:

Ranking: (SELECT COUNT(*) FROM TableName AS T WHERE T.PrimaryKeyField <
TableName.PrimaryKeyField)+ 1