Ranking within a query for multiple fields

[Timc24]

I hope I am stating this correctly. I can get a query to rank on one variable. What I am trying to do is, take a list of dozens of golfers. Each golfer has multiple golf scores, from one to thirty five. I am trying to rank each golfer's scores within a query, without having to change the query to each golfer's name. Also, when I try to rank them all at once, it will just rank the scores, regardless of golfer. Or, if I try to group in a report, I get an error. Can one of the professionals here offer any expertise? Eternally grateful, Tim

[pbaldy]

It would help if you show your method, but basically you have to add golfer to your criteria.

[Timc24]

It would help if you show your method, but basically you have to add golfer to your criteria.

Thanks, I am new to the forum.

i have

SELECT t2.Name,(select count(t1.score)
From one as T1
Where T1.score >=T2.score) as rank, t2.score
from one as t2
where (((t2.name)="ark in,bill"))
order by t2.score desc;
this lets me do one golfer at a time, but this will take forever. Any thoughts?

tim

[pbaldy]

Like I said, try adding the golfer:

Where T1.score >=T2.score And t1.Name = t2.Name

Played some golf in your area during a visit last year. Had fun!

[Timc24]

Very cool about the golf! This is for a local men's club. I will give it a try. I am mostly a basic user, some of the SQL stuff gives me pause.

I will ill let you know how I do!

tim

[Timc24]

Nope, something isn't working. It still ranks the scores, but mixes all of the golfers in together.

[Timc24]

Could it be that I need to order by name, and then by score?

[IrogSinta]

Can you give a sampling of data from your table and what the expected result would be?

Ron

[pbaldy]

What was your complete SQL?

[Timc24]

Hi, sorry for the delay, if still available:
select t2.name,(select count(t1.score)
from One as t1
where t1.score<=t2.score and t1.name=t2.name) as rank,t2.score
from One as t2
order by t2.name

i get a ranking of scores, but I need to group by each golfer. When I try to Group, the numbers in the Rank all turn to zero.

thanks all!

tim

[pbaldy]

Can you post the db here, or a representative sample?

[Timc24]

Give me a few minutes and I will get a table posted. Thank you

[Timc24]

Ok, what I have here is my small database.

For info, I used something called Differential rather than Score (it is a golf calculation), but Differential is the number to be ranked, and then parsed out by golfer. Only by taking my ranked query and re-posting it into a spreadsheet, and then re-importing it into access as a table was I able to get it to group by golfer name.


So, by Table "One", query and report


And, by Table "Rank", query and report. Rank is where I had to do the re-import dance. Sorry, due to size, it made me delete the Rank table, query and report, but I figure you get the idea. If needed I will see if I can re-post.


I hope this helps, and thanks again in advance!

Tim

[pbaldy]

The query appears to be correctly ranking within each name. Is the problem when you try to group on name in the report? If so:

http://allenbrowne.com/subquery-02.h...tiLevelGroupBy

The first 2 options aren't really practical, and the 3rd didn't work in a test with your db. The 4th should work, as will the 5th. For the 5th, you could automate a process that emptied a temp table, repopulated it with your query data, then opened the report. You could also do the ranking on the report rather than in the query, using DCount().

I'm familiar with the differential, slope and course rating (as long as there's no quiz!). I looked it up, we played Castle Pines, Fossil Trace and Riverdale during our trip. Enjoyed them all, though Fossil Trace was probably the "coolest" surroundings-wise.