How do I group rankings?? (1 Viewer)

Kiz

Registered User.
Local time
Today, 20:42
Joined
Nov 12, 2014
Messages
11
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!
 
Last edited:

namliam

The Mailman - AWF VIP
Local time
Today, 10:42
Joined
Aug 11, 2003
Messages
11,696
You dont have referrerID in your query at all, but simply add that to there where clause of your subselect
 

Kiz

Registered User.
Local time
Today, 20:42
Joined
Nov 12, 2014
Messages
11
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

The Mailman - AWF VIP
Local time
Today, 10:42
Joined
Aug 11, 2003
Messages
11,696
I would expect something like so
Code:
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

Registered User.
Local time
Today, 20:42
Joined
Nov 12, 2014
Messages
11
Oh my god! THANK YOU THANK YOU! It worked perfectly!

You, my friend, are awesome! :D
 

Users who are viewing this thread

Top Bottom