Page 1 of 1

Assigning Ranks

PostPosted: Thu Jan 21, 2016 6:03 pm
by evil44
I have a Table titled Teams_Table with fields called Team_ID (primary key, short text) and Amaze_Score (number). I need to be able to assign a rank (1 through 10) based on Amaze_Score. There are 50 different teams, but I just want the top 10 and to stratify them 1st through 10th. If it's possible to add a column in the same table with that ranking value, that would be great.

I assumed this would be done via a query but I can't seem to get the syntax to work properly. Any help would be appreciated.

Thanks,
Dr. Evil

Re: Assigning Ranks

PostPosted: Thu Jan 21, 2016 7:31 pm
by eremmel
Making a query with ranks is not so difficult. But you need to deside what to do with equal ranks.
Having a database like oracle, MSSQL server, DB2 and Postgress, its just part of the syntax. With other database you have to self join the table.
Here a layout (Fix the needed quoting yourself)
Code: Select all   Expand viewCollapse view
SELECT COUNT(*) as "rank", a.Team_ID, a.Amaze_Score
FROM Teams_Table a LEFT JOIN Teams_Table b
    ON a.Amaze_Score <= b.Amaze_Score
GROUP BY a.Team_ID, a.Amaze_Score
HAVING COUNT(*) <= 10
ORDER BY 1

With equal scores you will see two teams with same rank. You might add other criteria to fine grain e.g.
ON a.Amaze_Score < b.Amaze_Score OR a.Amaze_Score = b.Amaze_Score AND a.Team_ID <= b.Team_ID

Good Phd.

Re: Assigning Ranks

PostPosted: Thu Jan 21, 2016 8:47 pm
by evil44
Good stuff, Good PhD, but where do I input that layout? I'm using Access 2013. I don't have super in-depth knowledge of Access and am slowly working my way through it.

As for equal scores, it's fine to give them equal ranks....like 1, 2, 2, 4, 5.

Re: Assigning Ranks

PostPosted: Thu Jan 21, 2016 10:40 pm
by eremmel
In Base you have a section where you can save queries. So you can create a new query and save it there.
You need to do some studie on how to use Base. Base is one of the more technical tools that need your study time before you can use it to make things happen.
On the forum you can find tutorials and also on YouTube you can find many short movies that teach you all kind of aspects.

Re: Assigning Ranks

PostPosted: Fri Jan 22, 2016 1:29 pm
by evil44
Now you lost me. I'm using Access 2013 because that's the program that my employer allows on my computer. Can anyone out there help with Access 2013 syntax for this issue?

Re: Assigning Ranks

PostPosted: Fri Jan 22, 2016 2:43 pm
by Villeroy
Everybody here uses the Base component of either OpenOffice or LibreOffice. You are on the wrong forum.
http://openoffice.org/
http://libreoffice.org/
Nevertheless, eremmel's SQL statement should work with MS Access as well (if you know how to do anything non-trivial with that development tool).