Rank contest scores in Access

[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]'

 

[KARL DEWEY]

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

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.

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]'

 

[KARL DEWEY]

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.

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

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.

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]'

 

[Rmynhier]

Thank you. Works great.
Changed >= to <= to rank highest to lowest.

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

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.

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]'