Separate Ties In Ranking Query

[lars7]

Hi Guys,
I'm using the code below to rank groups of records in a query:

SELECT A.[Pay Number], A.[NI Number], A.[Contracted Hours], Count(*) AS Rank
FROM tbl1 AS A LEFT JOIN tbl1 AS B ON (A.[Contracted Hours]<= B.[Contracted Hours]) AND (A.[NI Number] = B.[NI Number])
GROUP BY A.[Pay Number], A.[NI Number], A.[Contracted Hours]
ORDER BY 2, 3 DESC;

which produces the following results:

PayNo: NI: Hours: Rank
P00000021 NM0000011 37.5 1
P00000018 NM0000011 2 3
P00000019 NM0000011 2 3
P00000020 NM0000011 0.25 4

What I would like to see here is 1 2 3 4, is there any way to do this?

Thanks,

 

[dhookom]

You would need to determine the tie-breaking value/field.

Duane
Hook'D on Access
MS Access MVP

 

[lars7]

Hi Duane,
The only one I have is date started but that can be the same for some records too.

Thanks,

Tom.

 

[SkipVought]

That's your answer then.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lars7]

Hi Skip,
I gave it a go and here is the result:

Pay Number NI Number Contracted Hours Date Started Rank
P00000021 NM0000011 37.5 10/10/1999 1
P00000018 NM0000011 2 01/01/2011 2
P00000019 NM0000011 2 01/10/2012 1
P00000020 NM0000011 0.25 01/10/2012 2


Here is the code I used.


SELECT A.[Pay Number], A.[NI Number], A.[Contracted Hours], A.[Date Started], Count(*) AS Rank
FROM tblAssNo1 AS A LEFT JOIN tblAssNo1 AS B ON (A.[Date Started] <= B.[Date Started]) AND (A.[NI Number] = B.[NI Number]) AND (A.[Contracted Hours]<= B.[Contracted Hours])
GROUP BY A.[Pay Number], A.[NI Number], A.[Contracted Hours], A.[Date Started]
HAVING (((A.[Date Started]) Is Not Null))
ORDER BY 2, 3 DESC;


As I said, some of the contracted hours and start dates are the same for some posts.

Thanks,

Tom.

 

[lars7]

Hi,
I changed "(A.[Contracted Hours]<= B.[Contracted Hours])" to this "(A.[Date Started] >= B.[Date Started])" and got better results:

Pay Number NI Number Contracted Hours Date Started Rank
P00000021 NM0000011 37.5 10/10/1999 1
P00000018 NM0000011 2 01/01/2011 2
P00000019 NM0000011 2 01/10/2012 3
P00000020 NM0000011 0.25 01/10/2012 4


But if the dates are the same too I get this:


Pay Number NI Number Contracted Hours Date Started Rank
P00000000 NM0000001 15 01/01/2011 2
P00000001 NM0000001 15 01/01/2011 2

Thanks,

Tom.

 

[lars7]

Hi,

This should have been

I changed this "(A.[Date Started] <= B.[Date Started]))" to this "(A.[Date Started] >= B.[Date Started])" and got better results:

Sorry for the confusion.

Tom.