Ranking files

[hyperkink]

I have a database and it contains 800 files. Some of them are called “open
files†and the rest of them are called “closed filesâ€. For each file, it has
its own priority score. I want to create a textbox called “Ranking†which
ranks only open files by the priority score. If the open file has the highest
score, then the ranking number is the lowest. And when an open file changes
to closed file, it will automatically remove from the ranking system. Do you
have any suggestion that how to achieve it? Thanks a lot.

 

[Ken Snell \(MVP\)]

This should get you close... replace generic names with the real names:

SELECT TableName.*,
(SELECT Count(*) FROM TableName AS T
WHERE T.OpenClosedFieldName = "Open" AND
T.PriorityNumber <= TableName.PriorityNumber AND
T.PrimaryKeyField <= TableName.PrimaryKeyField)
AS Ranking;

 

[hyperkink]

When I use the code, the computer shows error with the code. Do you have any
other suggestion about how to rank files? Thanks.

 

[Ken Snell \(MVP\)]

Could you tell us what error message you get? Did you replace my generic
names with the real names (e.g., replace OpenClosedFieldName with the actual
name of the field that contains the Open/Closed data value; TableName with
the real table name)? Can you post the SQL statement that you are trying to
use now?