Textbox properties

[hyperkink]

Hi: I am currently revising a form. There is a textbox named score and
another one named ranking. I wan to know is that possible to show the ranking
number depends on the score. If the score is highest number , then the
ranking shows the lowest number.
Thank you

 

[KARL DEWEY]

You have to do Ranking in your query.
Here is an example --
SELECT Q.[Group], Q.[Points], (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points];

 

[Ken Sheridan]

I assume that the scores are in a column in a table on which the form is
based. You can display the ranking in an unbound text box on the form (don't
have a ranking column in the table as that introduces redundancy) with a
ControlSource of:

= DCount("*","YourTable", "Score >" & [Score])+1

This counts the rows with a score higher than the current record's score and
adds 1, and will also handle ties, so if two scores tie for second place
they'll both be ranked second and the next lowest score will be ranked fourth.

If you want to rank within a subset of rows in the table, e.g. if you have
multiple events in the table, each identified by a numeric value in an
EventID column, and toy want the ranking for each event, then you'd include
this in the criteria for the DCount function:

= DCount("*","YourTable", "EventID = " & [EventID] & " And Score >" &
[Score])+1

You can of course do the same in a report or in a computed column in a
query. If you don't need the query to be updatable you can also use a
subquery to compute the ranking:

SELECT Score,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.Score >T1.Score)+1
AS Ranking
FROM YourTable AS T1
ORDER BY Score DESC;

Note how the two instances of the table are differentiated by giving them
aliases T1 and T2.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

"toy want" ???

That should have been 'you want' of course.

Ken Sheridan
Stafford, England

 

[hyperkink]

Thank you for your help. I am wondering that is it possible to use VBA to
program codes and achieve my goal?

 

[Ken Sheridan]

I'm not sure I understand what you mean. What do you have in mind?

You could wrap the DCount function call in a function in the form's module
and call the function as the ControlSource property of an unbound control,
passing the score into it as an argument, but I'd see no advantage in this
over calling the DCount function directly in the ControlSource property.

Ken Sheridan
Stafford, England