Needing to build a query to rank something by date order - please help!

[Kiz]

Hi all,

I am currently building a database to work out commission payable. I have hit a snag when it comes to working out some "commission kickers" (e.g. on the third settled deal commission goes from A% to B% etc).



I basically need to rank the deals by the date they were settled for each referring company... Below is a mock table of what i need as an outcome of a query... but i am unsure how to build the query itself! and i'm not exactly savvy with SQL views and coding. THANK YOU!

Referring Company	DealName	DateSettled	Rank
Introducer A	DealCat	09 Dec	4
Introducer A	DealDog	21 Feb	2
Introducer A	DealFish	10 Aug	3
Introducer A	DealHorse	06 Jan	1
Introducer B	DealCow	07 Oct	2
Introducer B	DealAnt	16 Dec	3
Introducer B	DealRabbit	11 Jul	1
Introducer C	DealShark	06 Dec	1
Introducer D	DealBird	08 Jan	1
Introducer D	DealElephant	25 Dec	2

[June7]

So you need to count how many records for each company and alter the commission for the records after some threshold?

This can get tricky. Criteria that is based on other records of same table seldom easy. For a start review http://allenbrowne.com/ranking.html

Also, DCount() might be an option but domain aggregate functions can be slow.

[Kiz]

Thanks for that! That has now enabled me to rank each deal based on settlement date (which is awesome) - not sure if I am pushing my luck asking this, but do you know how to now group the rankings by the introducing company?

Here is the current SQL view of the query if that helps?

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

[June7]

Your sample data does not show TotalValue. Is introducing company synonymous with referring company?

You want ranking within each group (company)? Maybe:

WHERE Dupe.TotalValue < qryDates.TotalValue AND Dupe.Company = qryDates.Company