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" <scrawf...@colo nialfirststate. com.au>
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
InventmentAmoun t Currency

IID CID PID InvestmentAmoun t
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

qryRankInvestme ntsByPortfolio:
SELECT InvestmentAmoun t, PID, CID, CStr((SELECT COUNT(*) FROM
tblInvestments AS A WHERE A.PID = tblInvestments. PID AND
A.InvestmentAmo unt tblInvestments. InvestmentAmoun t)+(SELECT COUNT(*)
FROM tblInvestments AS A WHERE A.PID < tblInvestments. PID AND
A.InvestmentAmo unt = tblInvestments. InvestmentAmoun t)+1) & IIf((SELECT
COUNT(*) FROM tblInvestments AS A WHERE A.IID <tblInvestments .IID AND
A.PID = tblInvestments. PID AND A.InvestmentAmo unt =
tblInvestments. InvestmentAmoun t)=0,'',' (tie)') AS RankWithinPortf olio
INTO tblSeamless FROM tblInvestments GROUP BY InvestmentAmoun t, PID,
CID, IID ORDER BY PID, CID;

!qryRankInvestm entsByPortfolio =tblSeamless

InvestmentAmoun t PID CID RankwithinPortf olio
$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********@Fort uneJames.com