Ranking records

[Legends77]

hope I describe this right...
In the query: qry2Somes
Field: TeamNumber
Field: OorU

Example:
Team --- OorU
Team 1 = -2 for a ranking of 1
Team 5 = -1 for a ranking of 2
Team 3 = 0 for a ranking of 3
Team 7 = 1 for a ranking of 4
Team 2 = 3 for a ranking of 5
Team 6 = 5 for a ranking of 6
Team 4 = 6 for a ranking of either 7 or 8
Team 8 = 6 for a ranking of either 7 or 8 (depends on rank given to team 4)

What I need to do is add a field that will "rank" them so that I can correctly "group" in a report. For example, in the above, team 1 = 1, team 5 = 2, team 3 = 3 and so on to team 8 = 8. I am not concerned with ties, as with team 4 and team 8, one needs 7 and the other 8.

Reason for this is that this query is linked to by another query that lists the individual team members names and the report needs to list their names. I have tried grouping by OorU and team number in the report and using headers / footers, but the spacing messes up when there are ties. Therefore the best solution I can think of is to have the teams "ranked" in the query and grouped by that ranking in the report.

Any ideas? Again, hope I described that well enough for someone to help and thanks in advance!

 

[dhookom]

Have you googled "access ranking query"? Does this work for you?

SELECT TeamNumber, OorU, 
(SELECT Count(*) FROM qry2Somes q WHERE q.OorU <= qry2Somes.OorU) as Ranking
FROM qry2Somes;

Duane
Hook'D on Access
MS Access MVP

 

[Legends77]

Thank you.
I ran the search you suggested (thanks for the tip), but just not sure where or how to use the code. Does it go in the query?

Here is the current sql view from the query.
SELECT DISTINCTROW [2SomeSunday].Team, Sum([2SomeSunday].[OorU]) AS [SumOfOorU]

FROM 2SomeSunday

GROUP BY [2SomeSunday].Team;

 

[dhookom]

Did you try the query I suggested?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

As you want different ranking for ties, you may try this query (SQL code)

SELECT A.Team, A.SumOfOorU, Count(*) AS Ranking
FROM qry2Somes AS A INNER JOIN qry2Somes AS B ON (A.SumOfOorU>B.SumOfOorU) OR (A.SumOfOorU=B.SumOfOorU AND A.Team>=B.Team)
GROUP BY A.Team, A.SumOfOorU

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Legends77]

Sorry for the delayed response... but thanks to all. Only thing is that I think I am really lost now. I have tried multiple versions of code in a new query and keep getting syntax errors.

I have gone back and renamed a few things in tables and updated my existing queries to help me "follow the flow" and perhaps this will help with my question.

This is the code from the first query, that gives me the sum of [OverorUnder]:

SELECT DISTINCTROW [2SomeSunday].Team, Sum([2SomeSunday].OverorUnder) AS SumOfOverorUnder
FROM 2SomeSunday
GROUP BY [2SomeSunday].Team;



This is then used in the second query (code below) that gives me the names for each of the teams:

SELECT [2SomeSunday].Team, [2SomeSunday].Player, [2SomeSunday].Handicap, [2SomeSunday].Score, [2SomeSunday].Net, [2SomeSunday].OverorUnder, [2SomeSundayTotals].SumOfOverorUnder
FROM 2SomeSunday INNER JOIN 2SomeSundayTotals ON [2SomeSunday].Team = [2SomeSundayTotals].Team;


Guess my questions are... where do I use the code(s) that have been suggested?... in a new query or in one of the existing queries?


Again, thanks in advance for the help... I know the suggestgions would work if I just knew where to use them

 

[PHV]

Create a query named, say, qryRank2SomeSundayTotals:

SELECT A.Team, A.SumOfOverorUnder, Count(*) AS Ranking
  FROM [2SomeSundayTotals] AS A
 INNER JOIN [2SomeSundayTotals] AS B
    ON (A.SumOfOverorUnder>B.SSumOfOverorUnder) OR (A.SumOfOverorUnder=B.SumOfOverorUnder AND A.Team>=B.Team)
 GROUP BY A.Team, A.SumOfOverorUnder

And now your final query:

SELECT A.Team, A.Player, A.Handicap, A.Score, A.Net, A.OverorUnder, T.SumOfOverorUnder, T.Ranking
  FROM [2SomeSunday] AS A
 INNER JOIN qryRank2SomeSundayTotals AS T
    ON A.Team = T.Team

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Legends77]

okay...
Created query
qryRank2SomeSundayTotals
used the above code for that query and the following error appears:
Microsoft Access can not represent the join expression (A.SumOverorUnder>B.SumOverorUnder)OR (A.SumOverorUnder=B.SumOverorUnder and A.Team>=B.Team) in design view

When I run the query a prompt for B.SumOverorUnder appears and if I click "OK", the query runs and all are given the rank of "1" with the exception of the one that is a tie, which is given "2".

The same prompt appears for the last query and same result if "OK" is printed.

This looks so very close, just that "B.SumOverorUnder" just is not wanting to work.

should I have replaced the A. and B. with table or query names or something?

 

[Legends77]

My Goof!!!! Sorry... all working! in one of the lines I had B.SsumoverorUnder...... Too many "S"'s.

Thanks again!

This has really helped me out and hope this starts to flow smoothly now.

thanks again!!!