Ranking Query help - over my head and going down 2

[kb9prt]

I am relatively inexperience with Access and have found myself over my head. I have put together an Access database for a local power-lifting meet. It is a relatively simple DB but has preformed well over the last few years. Each year I have been asked to add a little something to it: this year – team totals. Team totals are calculated from the top eleven lifters for each team. Each first place in a weight class earns a team 12 points, second place earns 9 points and so on. Note: there are 12 weight classes for both Men and Women in two divisions for each (junior varsity and varsity).

My problem is I do not save a lifters place. After much searching and more frustration I believe that I need to run a ranking query that will save the rank of each lifter in his/her weight class. I have tried with out success. I have a query which sorts by GENDER, WEIGHTCLASS, DIVISION, and by TOTALPOUNDS. I have changed the alias of the query to TeamAwards1 with the query name as TeamAwards and then I tried to add the field

 Ranking: (Select Count (*) from TeamAward Where [CountofTOTALPOUNDS] > [TeamAwards1]. [CountofTOTALPOUNDS]) + 1

All this did was put a 1 in each lifters rank field.

I believe that I need to build the SQL statement to include the sort into the Ranking statement but I an unsure how to go forward. Please have pity and point me in the right direction. I have Googled and searched but have not been able to solve this one. I am sure there is an easy solution yet is alludes me.

Thank you

 

[cmmrfrds]

The classic way to do this in SQL is with a self join, providing the table has a unique key which all tables should have. In Access you can do this by saving the self joined sql as a separate query and then joining this query back to the table(s) you are working with. Here is a link that explains this.

http://www.1keydata.com/sql/sql-rank.html

 

[kb9prt]

Thanks cmmrfrds - I'll give it a try

 

[kb9prt]

I tried the following code but get an error message of
"You tried to execute a query that does not include the specified expression 'IDKey' as part of an aggregate function"

SELECT A1.IDKey,A2.IDKey, A1.Gender, A1.WtClass, A1.Division, A1.BodyWt, A1.Expr5 AS TotalWt,Count(A2.Epr5)AS WtClassRank
FROM AllInfo AS A1, AllInfo AS A2
WHERE
 (((A1.Division)=[A2].[Division]) AND(A1.WtClass)=[A2].[WtClass]) AND ((A1.Gender)=[A2].[Gender]) AND ((A1.Expr5)<=[A2].[Expr5]) OR (((A1.Expr5)=[A2].[Expr5]) AND ((A1.IDKey)=[A2].[IDKey]))
GROUP BY A1.Name, A1.Gender, A1.Division, A2.WtClass, A1.Expr5
ORDER BY A1.Expr5 DESC , A1.IDKey DESC;

I could sure use some additional help! By the way - sorry it took so long to respond but I have a lot to learn and wanted to try a few things to see if I could get it myself

 

[SkipVought]

Hi,

Try this...

SELECT
  A1.IDKey
, A1.Gender
, A1.WtClass
, A1.Division
, A1.BodyWt
, Count(A2.BodyWt) AS WtClassRank

FROM 
  AllInfo AS A1
, AllInfo AS A2

WHERE A1.BodyWt  <= A2.BodyWt OR (A1.BodyWt = A2.BodyWt AND A1.IDKey = A2.IDKey) 

GROUP BY 
  A1.IDKey
, A1.Gender
, A1.WtClass
, A1.Division
, A1.BodyWt

ORDER BY A1.BodyWt DESC , A1.IDKey DESC;

Skip,

_[red][/red]

 

[PHV]

And what about this ?
SELECT A1.IDKey, A1.Name, A1.Gender, A1.WtClass, A1.Division, A1.BodyWt, A1.Expr5 AS TotalWt, Count(*) AS WtClassRank
FROM AllInfo AS A1 INNER JOIN AllInfo AS A2
ON A1.Division=A2.Division AND A1.WtClass=A2.WtClass AND A1.Gender=A2.Gender
WHERE A1.Expr5<=A2.Expr5
GROUP BY A1.IDKey, A1.Name, A1.Gender, A1.WtClass, A1.Division, A1.BodyWt, A1.Expr5
ORDER BY A1.Expr5 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kb9prt]

Thanks SkipVought and PHV : works like a charm - maybe you can help with one other problem I found while testing this. When two lifters have the same total weight lifted then the lifter with the lighter body wt should rank first. I tried modifing your SQL but I didn't have much luck. Could you point me in the right direction?

Thanks so much..

 

[SkipVought]

Why don't you have a field for LiftWt as well as BodyWt? That's one thing that was confusing me originally.

Skip,

_[red][/red]

 

[kb9prt]

Sometimes I have a hard time explaining what I am tring to do - the total weight lifted is actually the Expr5 field and I did name it as such (TotalWt)in the "live" query but because it is a calculated field and I put together a test DB to experiment with.... I did not document too much. Sorry to be confusing.. sure appreciate the help

 

[SkipVought]

What is in Expr5?

It looked to me that Expr5 referred to BodyWt.

Why does it not have a FIELD designation in your table?

Skip,

_[red][/red]

 

[SkipVought]

Thinking about it, LiftWt would be in a separate table that might be named Scores, the other data would be in a Participants table.

Skip,

_[red][/red]

 

[kb9prt]

Hi SkipVought -

I tried to keep the tables somewhat "Normalized". The total weight lifted or Expr5 is the sum of the maximum lift from Squat, bench press and Deadlift. Each lifter actually gets three attemps at each lift and the maximums are added for the total weight lifted. The "Lifters" table only includes the raw data for each lifter with IDKey set as the primary key.

 

[PHV]

When two lifters have the same total weight lifted then the lifter with the lighter body wt should rank first
I don't know what is a "lighter body wt", so just a guess:
SELECT A1.IDKey, A1.Name, A1.Gender, A1.WtClass, A1.Division, A1.BodyWt, A1.Expr5 AS TotalWt, Count(*) AS WtClassRank
FROM AllInfo AS A1 INNER JOIN AllInfo AS A2
ON A1.Division=A2.Division AND A1.WtClass=A2.WtClass AND A1.Gender=A2.Gender
[!]WHERE A1.Expr5<A2.Expr5 OR (A1.Expr5=A2.Expr5 AND A1.BodyWt<=A2.BodyWt)[/!]
GROUP BY A1.IDKey, A1.Name, A1.Gender, A1.WtClass, A1.Division, A1.BodyWt, A1.Expr5
ORDER BY A1.Expr5 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kb9prt]

PHV - You are the greatest! I have to let you know that I tried that modification as I tried to work through it myself but it didn't work. When I first tried your version it didn't work either then...Duhh - the underling query was hiding some of the lifters that scratched but the query was still ranking them! I fixed the query and the ranking is perfect! It's the Holiday season - have a star!