Add rank to a Make Table Query & then use for calculation

[Bob]

Hi all,
I want to incorporate several types of different rankings to a Make
Table Query I have then use the rank for calculations.

I want to calculate rankings based on how much each client has in a
portfolio, so lets say there are 4 portfolios, so each client would
have 4 rankings, dependant upon how much they had in each portfolio
compared to their peers. I then want to use each of those rankings to
run some calculations. I want all of this to go into my table
seamlessly...

Please help!

[CDMAPoster]

On Jan 24, 11:56 pm, "Bob" <[e-mail]>
wrote:

Hi all,
I want to incorporate several types of different rankings to a Make
Table Query I have then use the rank for calculations.

I want to calculate rankings based on how much each client has in a
portfolio, so lets say there are 4 portfolios, so each client would
have 4 rankings, dependant upon how much they had in each portfolio
compared to their peers. I then want to use each of those rankings to
run some calculations. I want all of this to go into my table
seamlessly...

Please help!

tblPortfolios
PID AutoNumber
PortfolioName Text

PID PortfolioName
1 P1
2 P2
3 P3
4 P4
5 P5

tblClients
CID AutoNumber
ClientName Text

CID ClientName
1 C1
2 C2
3 C3

tblInvestments
IID AutoNumber
CID Long
PID Long
InventmentAmount Currency

IID CID PID InvestmentAmount
1 1 1 $100,000.00
2 1 2 $50,000.00
3 1 3 $150,000.00
4 2 1 $20,000.00
5 2 3 $3,300,000.00
6 2 4 $80,000.00
7 2 5 $30,000.00
8 3 2 $60,000.00
9 3 4 $20,000.00
10 4 1 $100,000.00
11 4 2 $30,000.00
12 4 3 $5,000,000.00
13 4 4 $145,000.00
14 4 5 $80,000.00

qryRankInvestmentsByPortfolio:
SELECT InvestmentAmount, PID, CID, CStr((SELECT COUNT(*) FROM
tblInvestments AS A WHERE A.PID = tblInvestments.PID AND
A.InvestmentAmount tblInvestments.InvestmentAmount)+(SELECT COUNT(*)
FROM tblInvestments AS A WHERE A.PID < tblInvestments.PID AND
A.InvestmentAmount = tblInvestments.InvestmentAmount)+1) & IIf((SELECT
COUNT(*) FROM tblInvestments AS A WHERE A.IID <tblInvestments.IID AND
A.PID = tblInvestments.PID AND A.InvestmentAmount =
tblInvestments.InvestmentAmount)=0,'',' (tie)') AS RankWithinPortfolio
INTO tblSeamless FROM tblInvestments GROUP BY InvestmentAmount, PID,
CID, IID ORDER BY PID, CID;

!qryRankInvestmentsByPortfolio =tblSeamless

InvestmentAmount PID CID RankwithinPortfolio
$100,000.00 1 1 1 (tie)
$20,000.00 1 2 3
$100,000.00 1 4 1 (tie)
$50,000.00 2 1 2
$60,000.00 2 3 1
$30,000.00 2 4 3
$150,000.00 3 1 3
$3,300,000.00 3 2 2
$5,000,000.00 3 4 1
$80,000.00 4 2 2
$20,000.00 4 3 4
$145,000.00 4 4 1
$30,000.00 5 2 3
$80,000.00 5 4 2

Note that the first two subqueries can be combined into one. I left
them as two separate subqueries so that the individual components can
be seen more easily. This is the only data I tried.

Also, c.f.:

http://groups.google.com/group/micro...120969c917495a

James A. Fortune
CD********@FortuneJames.com