Access Ranking - Problem with Ties 1

[mndiscer]

Hello, I have the following table
ID Store# Genre WOS
1 1000 Action 2
2 1001 Action 4
3 1002 Action 4
4 1003 Action 1
5 1000 RPG 3
6 1001 RPG 1

I would like to group the Genre and rank based off WOS, I have it ranking nicely but my problem is ties. For example the following code assigns the same rank for records within the same genre that have the same WOS the same rank, when I need it to rank in sequence. I think the ID column can break the tie, but not sure how/where to put that. My code is below...

SELECT [Master - Store - Genre].ID, [Master - Store - Genre].[STORE#], [Master - Store - Genre].GENRE, [Master - Store - Genre].[Actual WoS], (SELECT COUNT (*) FROM [Master - Store - Genre] AS [XX]
WHERE [Master - Store - Genre].Genre = [XX].Genre AND [Master - Store - Genre].[Actual WoS] > [XX].[Actual WoS] ORDER BY [Master - Store - Genre].[ID])+1 AS Rank
FROM [Master - Store - Genre]
ORDER BY [Master - Store - Genre].GENRE, [Master - Store - Genre].[Actual WoS];


Any help would great!

Thanks

 

[dhookom]

Depending on the ranges of your field values, you could try:

SELECT ID, [STORE#], GENRE, [Actual WoS], 
(SELECT COUNT (*)  
 FROM [Master - Store - Genre] AS [XX]
 WHERE [Master - Store - Genre].Genre = [XX].Genre AND
 [Master - Store - Genre].[Actual WoS] + [Master - Store - Genre].ID * 0.00000001 > [XX].[Actual WoS] + [XX].ID * 0.00000001
)+1 AS Rank
FROM [Master - Store - Genre]
ORDER BY GENRE, [Actual WoS],ID;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Replace this:
(SELECT COUNT (*) FROM [Master - Store - Genre] AS [XX]
WHERE [Master - Store - Genre].Genre = [XX].Genre AND [Master - Store - Genre].[Actual WoS] > [XX].[Actual WoS] ORDER BY [Master - Store - Genre].[ID])+1 AS Rank
with this:
(SELECT COUNT (*) FROM [Master - Store - Genre] AS [XX]
WHERE [Master - Store - Genre].Genre = [XX].Genre AND ([Master - Store - Genre].[Actual WoS] > [XX].[Actual WoS] OR ([Master - Store - Genre].[Actual WoS]=[XX].[Actual WoS] AND [Master - Store - Genre].[ID]>[XX].[ID])))+1 AS Rank

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

 

[mndiscer]

and there was much rejoicing...

Worked great. Thanks