Rank contest scores in Access

  • Thread starter Thread starter Rmynhier
  • Start date Start date
R

Rmynhier

I am trying to rank contest scores in a query

Here is query:
SELECT tbl_Bands.[Prelim Order], tbl_Bands.School, tbl_Bands.[Metro School],
tbl_Bands.Class, tbl_Bands.Division, tbl_Scores.[Judge #8 Color Guard]
FROM tbl_Bands INNER JOIN tbl_Scores ON tbl_Bands.BandID = tbl_Scores.BandID
ORDER BY tbl_Bands.[Prelim Order];

Trying to rank on 'tbl_Scores.[Judge #8 Color Guard]'
 
Try this --
SELECT tbl_Bands.[Prelim Order], tbl_Bands.School, tbl_Bands.[Metro School],
tbl_Bands.Class, tbl_Bands.Division, tbl_Scores.[Judge #8 Color Guard],
(SELECT Count(*) FROM tbl_Scores AS Q WHERE tbl_Scores.[Judge #8 Color Guard]
= Q.[Judge #8 Color Guard]) AS Rank
Click to expand...
FROM tbl_Bands INNER JOIN tbl_Scores ON tbl_Bands.BandID = tbl_Scores.BandID
ORDER BY tbl_Bands.[Prelim Order];

--
Build a little, test a little.


Rmynhier said:
I am trying to rank contest scores in a query

Here is query:
SELECT tbl_Bands.[Prelim Order], tbl_Bands.School, tbl_Bands.[Metro School],
tbl_Bands.Class, tbl_Bands.Division, tbl_Scores.[Judge #8 Color Guard]
FROM tbl_Bands INNER JOIN tbl_Scores ON tbl_Bands.BandID = tbl_Scores.BandID
ORDER BY tbl_Bands.[Prelim Order];

Trying to rank on 'tbl_Scores.[Judge #8 Color Guard]'
Click to expand...
 
What I posted will give ties in scores.
If you want tie breakers then this will break the tie --
SELECT tbl_Bands.[Prelim Order], tbl_Bands.School, tbl_Bands.[Metro School],
tbl_Bands.Class, tbl_Bands.Division, tbl_Scores.[Judge #8 Color Guard],
(SELECT Count(*) FROM tbl_Scores AS Q WHERE tbl_Scores.[Judge #8 Color Guard]
& tbl_Bands.[Prelim Order] >= Q.[Judge #8 Color Guard] & Q.[Prelim Order]) AS
Rank
FROM tbl_Bands INNER JOIN tbl_Scores ON tbl_Bands.BandID = tbl_Scores.BandID
ORDER BY tbl_Bands.[Prelim Order];


--
Build a little, test a little.


KARL DEWEY said:
Try this --
SELECT tbl_Bands.[Prelim Order], tbl_Bands.School, tbl_Bands.[Metro School],
tbl_Bands.Class, tbl_Bands.Division, tbl_Scores.[Judge #8 Color Guard],
(SELECT Count(*) FROM tbl_Scores AS Q WHERE tbl_Scores.[Judge #8 Color Guard]
= Q.[Judge #8 Color Guard]) AS Rank
Click to expand...
FROM tbl_Bands INNER JOIN tbl_Scores ON tbl_Bands.BandID = tbl_Scores.BandID
ORDER BY tbl_Bands.[Prelim Order];

--
Build a little, test a little.


Rmynhier said:
I am trying to rank contest scores in a query

Here is query:
SELECT tbl_Bands.[Prelim Order], tbl_Bands.School, tbl_Bands.[Metro School],
tbl_Bands.Class, tbl_Bands.Division, tbl_Scores.[Judge #8 Color Guard]
FROM tbl_Bands INNER JOIN tbl_Scores ON tbl_Bands.BandID = tbl_Scores.BandID
ORDER BY tbl_Bands.[Prelim Order];

Trying to rank on 'tbl_Scores.[Judge #8 Color Guard]'
Click to expand...
Click to expand...
 
Thank you. Works great.
Changed >= to <= to rank highest to lowest.

KARL DEWEY said:
Try this --
SELECT tbl_Bands.[Prelim Order], tbl_Bands.School, tbl_Bands.[Metro School],
tbl_Bands.Class, tbl_Bands.Division, tbl_Scores.[Judge #8 Color Guard],
(SELECT Count(*) FROM tbl_Scores AS Q WHERE tbl_Scores.[Judge #8 Color Guard]
= Q.[Judge #8 Color Guard]) AS Rank
Click to expand...
FROM tbl_Bands INNER JOIN tbl_Scores ON tbl_Bands.BandID = tbl_Scores.BandID
ORDER BY tbl_Bands.[Prelim Order];

--
Build a little, test a little.


Rmynhier said:
I am trying to rank contest scores in a query

Here is query:
SELECT tbl_Bands.[Prelim Order], tbl_Bands.School, tbl_Bands.[Metro School],
tbl_Bands.Class, tbl_Bands.Division, tbl_Scores.[Judge #8 Color Guard]
FROM tbl_Bands INNER JOIN tbl_Scores ON tbl_Bands.BandID = tbl_Scores.BandID
ORDER BY tbl_Bands.[Prelim Order];

Trying to rank on 'tbl_Scores.[Judge #8 Color Guard]'
Click to expand...
Click to expand...
 
Back
Top