How do I group rankings??

[Kiz]

Hi all,

I have used Allen Browne's suggested method of ranking to rank by the date each client settled... Now I need to group the rankings by the referring company as below... so it ranks each client referred by referral company from earliest to latest - like so:

ReferrerID___ClientID___TotalValue___BeatenBy (Ranking)
Intro1_______CatCo_____6/11/14________4
Intro1_______DogCo_____8/3/14_________1
Intro1_______FishCo_____17/5/14________3
Intro2_______AntCo_____11/1/14________2
Intro1_______PigCo______2/4/14_________2
Intro2_______BirdCo_____12/8/14________3
Intro2_______FrogCo_____6/1/14________1

My current SQL view of my query looks like this:

SELECT qryDates.ClientID,
qryDates.TotalValue,
(SELECT Count([ClientID])
AS HowMany
FROM qryDates AS Dupe
WHERE Dupe.TotalValue < qryDates.TotalValue)+1
AS BeatenBy
FROM qryDates;


THANK YOU!

 

[namliam]

You dont have referrerID in your query at all, but simply add that to there where clause of your subselect

 

[Kiz]

I have tried dropping it in through design view.. but it comes up with an error.

I know I am being a pain, but I really suck at SQL! Can someone please drop it into my above SQL query so I can see where it goes?

Thank you!!!!

 

[namliam]

I would expect something like so

SELECT [B][U]ReferrerID,[/U][/B]
qryDates.ClientID,
qryDates.TotalValue, 
(SELECT Count([ClientID]) 
AS HowMany
FROM qryDates AS Dupe
WHERE Dupe.TotalValue < qryDates.TotalValue
[B][U] and dupe.ReferrerID = qryDates.ReferrerID[/U][/B])+1 
AS BeatenBy
FROM qryDates;

 

[Kiz]

Oh my god! THANK YOU THANK YOU! It worked perfectly!

You, my friend, are awesome!

 

[namliam]

That wasnt so hard now, was it?