Query who output ranking of rows? (1 Viewer)

[fregal]

Hi

I have a Access db where sportsteams register their score etc. I have a Query who output the totals, and put the team with most score on top etc. BUT I want to have a ranking number to the left of the teams name. I use this in a webreport (asp).

ex:


1. Holy Eagles 50 points
2. Crazy Bears 44 points
3. Insane Falcons 42 points
4. ...... etc....

Any ideas? Should be simple?


Best Regards
fregal

 

[Twin]

Hi,
Create a table with the same fields as your query with the adittion of an autonumber field. Make your existing query Append Query to the new table, and given that the query has descending order sor on points you should get what you want.
Now, to have this updated on the regular basis, I would create a macro: first step Delete query ( delete new table records, but not structure); second step your Append query.
It should do the trick.

HTH

Chris

 

[David R]

Don't know if you can do it in a query, but you can do this on a form..

If you put your query into a form/subform, this article may help: http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q210340

 

[The_Doc_Man]

Or, if you are trying to do this to show rankings AND you want to allow for a tie...


Make a query where the rank-number field is computed according to

{ranking} = 1 + DCount("[TeamName]", "TeamData", "[TeamScore] > " & CStr([TeamScore] ))

This DCount tells you how many teams have GREATER (but not EQUAL) scores to the team in question.

For the first place team, this is 0. 1 + 0 = 1.

For the second place team, this is 1. 1+1 = 2.

For two teams tied for third, this is 2. 1+2 = 3.

For the team beneath the ones tied for third, the answer is 5 because four teams have greater aggregate scores.

Note: This is correct unless you have a second attribute that acts as a tie-breaker, which you didn't mention in your question.

Look up DCount in the help files to make sure you understand what you are doing when you use it. But I think you will be pleased to use it this way.

 

[fregal]

Hi

thanks for replying. I will try out your suggestions.


fregal

 

[fregal]

Or, if you are trying to do this to show rankings AND you want to allow for a tie...

The_Doc_Man!

your suggestion makes sense, because a tie on points may happen. But, could you please help me some more? Im a newbie when it comes to use access without the help of the buildt in wizards and "drag & drop" functionality. AND I do have a attribute who counts only when there is a tie, the one with lowest handicap is ranked higher. We are talking about Golf (RESULTS_HANDICAP)

My Query have the following field names:

RESULTS_POINTS -> in table RESULTS
RESULTS_HANDICAP -> in table RESULTS
TEAM_NAME -> in table RESULTS (lookup from table TEAMS)

Do I need to put in a new field called TEAM_RANK anywhere?

Where exactly do i put your calculation:

{ranking} = 1 + DCount("[TeamName]", "TeamData", "[TeamScore] > " & CStr([TeamScore] ))

...and how will it look?

Added comment: It is not very likely that two teams have the same points as well as the same handicap, so not to make things more complicated than needed, I think handicap doesnt have to be a factor here.


Im most grateful for any help, thanks.


Best regards
fregal

 

[David R]

Try this:

In your query, change the Sort for RESULTS_POINTS to Descending, and the Sort for RESULTS_HANDICAP to Ascending. Make sure POINTS is to the left of HANDICAP in the query.

What this will do is sort all records by points from greatest to smallest, and then in the event of a tie, place the record with the smaller Handicap first.

I don't know ASP unfortunately, but perhaps the link I posted above will help you number the items.

 

[fregal]

The sorting is not a problem. The problem is to get a ranking number as well, so that the leading team get '1', second team get '2', etc.

--
Best Regards
fregal

 

[Rich]

Try downloading the Qrysmp from MS there are three methods of ranking within a query shown

 

[fregal]

Hi,

im learning, but....

I downloadet the Query samples from Microsoft, and got a little further. But now I also see that the Handicap must be considered in the calculations as long as i want a ranking with these metods.

I think its a bit odd that there isnt a way to just count the rows and give each row a number, and let the sorting decide the rank (I know i could use a second table with just numbers, and put them side by side, but then i will be limited in other ways):


How far I did get:

I made a Query and includet the following fields:

team_name
results_team_points (sum)
results_team_hcp (sum)

and saved it "q_ranking_1"

I then made a new Query of the "q_ranking_1" and aliased to "q_rank", and put in the following expression in a field:

Ranking: (select count (*) from q_ranking_1 where [sumofresults_team_points] > [q_rank].[sumofresults_team_points])+1

The result when there's a tie:

................hcp........points........rank
TeamB.....63,3.......70.............1
TeamA.....67,4.......70.............1


BUT to be correct, it should have been:

................hcp........points........rank
TeamB.....63,3.......70.............1
TeamA.....67,4.......70.............2


I need something more behind here that tells the query when there's a tie, the team with the lowest handicap shall be ranked first.

Any good ideas? Thanks for all the help so far!

--
Best Regards
fregal

 

[David R]

Again, I know how to do it for a form...

But for ASP, you are out of my league. I'm bumping this so perhaps someone else will see it.

 

[The_Doc_Man]

There are many ways to handle this, but sometimes the best way is to compute your way through it by combining the fields in some way.

Your query can contain fields that aren't displayed on your ASP. The ASP merely calls out what it wants. So compute your rank number in a column that is in the query but not referenced by the ASP.

In the query grid, you can make an expression have a name by putting the name and a colon. In the top row of the SELECT query, you could do this for the "ties broken" column...

Assume that no handicap is greater than 100. (If it is, pick another number and use that instead.)

TotPts: [Handicap] + ( 100 * [Score] )

Now, instead of doing the DCount on the score, do it on the TotPts column.

Ranking: 1 + DCount( "[TeamName]", "MyTable", "[TotPts] > " & [TotPts] )

Just because you compute the [TotPts] value in the query doesn't mean you have to show it. Just compute the ranking and show THAT, but the numbers you use to get there are just "behind-the-scenes" and can stay that way.

This should work as long as you have the Score field as either Long (integer) or one of the Real types.

 

[fregal]

The_Doc_Man

are you sure the expression is correct?:
Ranking: 1 + DCount( "[TeamName]", "MyTable", "[TotPts] > " & [TotPts] )

I get an "invalid expression" whatever i try to do with this...

Example:

I put the TotPts expression in the query Q_TABLE

I make a new query of Q_TABLE and call it Q_TABLE_RANKING where i put your expression, wich with my names would be:

Ranking: 1 + DCount( "[TEAM_NAME]", "Q_TABLE", "[TotPts] > " & [TotPts] )


???


--
Best regards
fregal