# Thread: Novice Access User - Need Guidance

1. Registered User
Join Date
Apr 2008
Posts
13

## Unanswered: Novice Access User - Need Guidance

In my example below you see I have two fields: AccountNumber and PercentEquityRisk. This is generated through a query that performs calculations to return me this value (there are other fields but they're hidden as they are not relevant to a calculation I'm trying to perform here).

My Table:

AccountNumber PercentEquityRisk
1111 1.94%
1111 1.94%
1111 0.97%
1111 0.97%
1111 0.49%
1111 0.49%
2222 1.60%
2222 1.06%
2222 1.06%
2222 0.53%
2222 0.53%

If I want to calculate an accounts total risk I simply add all the risks for the specified account number...i.e.

1111 1.94%
1111 1.94%
1111 0.97%
1111 0.97%
1111 0.49%
1111 0.49%

Risk for account 1111 is (1.94+1.94+.97+.97+.49+.49) = Overall Risk of 6.8%

That's an easy result to obtain, what I'm trying to do:

Obtain an "adjusted risk value" by taking the highest risk for each account and multiplying it by 100%, then selecting the next highest risk amount for the same account and multiplying it times 90%, then next highest times 80%, and next highest times 70%, and then no more adjustments...any remaining risks would all calculate times 100%. Note that some accounts can have anywhere from one to fourteen records (and this could increase as positions are added in the database).

1111 1.94% * 100% = 1.94%
1111 1.94% * 90% = 1.746%
1111 0.97% * 80% = .776%
1111 0.97% * 70% = .679%
1111 0.49% * 100% = .49%
1111 0.49% * 100% = .49%

So for this account 1111 while my risk above was 6.8%, my adjusted risk is 6.121%. Then I use this value to perform other calculations...

but how in the world do attack this...just a little guidance to put me in the right direction is really all I need. Thanks for your help.

2. Registered User
Join Date
May 2005
Location
Posts
2,888
Off hand I can't think of a pure SQL way to accomplish this. I think you're going to need a VBA function. Perhaps you pass it an account number, it opens a recordset to loop through the records in the correct order. You'd need a variable to track which record it was to be able to know which percentage to apply, and another to accumulate the percentage. The function passes back that accumulated percentage.

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Originally Posted by csf01
If I want to calculate an accounts total risk I simply add all the risks for the specified account number
Code:
```SELECT account_number
, Sum(risk)
FROM   a_table
GROUP
BY account_number```
To reduce results to a single account, add a WHERE clause
Code:
```...
FROM   a_table
WHERE  account_number = 101
...```

4. Registered User
Join Date
Apr 2008
Posts
13

## That's the easy part...

Thanks George, got that part down...read the last two paragraphs...that's my goal...any ideas?

5. Registered User
Join Date
May 2005
Location
Posts
2,888
Was my post not in the right direction?

6. Registered User
Join Date
Apr 2008
Posts
13

## no

no offense, but it was of no direction for me...I already indicated that was a very simple result to achieve...the guidance I was looking for was how to obtain the "adjusted risk value":

Obtain an "adjusted risk value" by taking the highest risk for each account and multiplying it by 100%, then selecting the next highest risk amount for the same account and multiplying it times 90%, then next highest times 80%, and next highest times 70%, and then no more adjustments...any remaining risks would all calculate times 100%. Note that some accounts can have anywhere from one to fourteen records (and this could increase as positions are added in the database).

1111 1.94% * 100% = 1.94%
1111 1.94% * 90% = 1.746%
1111 0.97% * 80% = .776%
1111 0.97% * 70% = .679%
1111 0.49% * 100% = .49%
1111 0.49% * 100% = .49%

So for this account 1111 while my risk above was 6.8%, my adjusted risk is 6.121%.

7. Registered User
Join Date
May 2005
Location
Posts
2,888
No offense taken. No offense meant to you, but it sounds like you're replying as if I was George. I gave you an outline of how to get what you wanted in post 2. I'll be surprised if you solve it any other way.

8. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
I think you'll need a second table to apply a "rank" which you can then line up with a risk modifier. You could also do this within a report by using grouping levels and using a running sum.

However you do it, the idea is to create an artificial rank for each grouping of records, so you would end up with:

AccountNumber PercentEquityRisk Rank
1111 1.94% 1
1111 1.94% 2
1111 0.97% 3
1111 0.97% 4
1111 0.49% 5

Then create another table that gives you your weighted modifier:

RiskMod
=========
rank modifier
1 100
2 90
3 80
4 70

This allows you to arbitrarily define how and what modifiers are applied to which risk according to ordinal position by using a query like this:

SELECT AcctNo, EquityRisk, nz(rm.modifier, 1) * rat.EquityRisk AS AdjuRisk
FROM yourRankedAcctTable rat LEFT JOIN RiskMod rm ON rat.Rank = rm.rank

You can accomplish this a variety of ways, but it all boils down to you need to give access some concrete way to identify those first four records, then provide a way to know what risk is to be applied to each of those records.

You could do it with static SQL too, but I generally advocate flexibility over ease.

9. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Point two was indeed answered by Paul, I merely covered the remaining points.

You can achieve what you want using pure SQL, but it would involve
Originally Posted by csf01
Note that some accounts can have anywhere from one to fourteen records
fourteen JOINs.

10. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
And Teddy; nice solution, seems very elegant.

11. Registered User
Join Date
May 2005
Location
Posts
2,888
That is a nice solution Teddy. I'd probably still use a function, but that's me. You'd have to make sure the ranking method handled ties properly, as most methods assign the same rank in case of a tie, which wouldn't work here.

12. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
In this case "rank" is more like an identity field that reseeds for each grouping level, not so much a "true" rank.

The more I think about it, the more I think using grouping levels and a running sum within a report may prove to be the more elegant approach in this case.

13. Registered User
Join Date
Apr 2008
Posts
13
wow, thanks for all the replies. I'll try it out and let you know my success. Thanks again.

14. Registered User
Join Date
Apr 2008
Posts
13

## Arghh

I've spent days trying to figure out this simple rank; I love the idea teddy had to tie it to a table with the risk modifier, but let's back up a step...
I'm trying to calculate the rank in my database and have researched numerous rank queries/SQL and can't seem to get the resuls I'm looking for.

I'm trying to get my query to return the rank Teddy indicated in his post:

AccountNumber PercentEquityRisk Rank
1111 1.94% 1
1111 1.94% 2
1111 0.97% 3
1111 0.97% 4
1111 0.49% 5

I think of key note is that I don't have a unique field in this query. I do have an InvestorID field that is not currently in the query. When I add it in, my rank is all over place based on the InvestorID...here's my latest attempt...

my SQL:

SELECT R_RiskNoZeroes.AccountNumber, R_RiskNoZeroes.PercentEquityRisk
(SELECT Count(*)
FROM R_RiskNoZeroes As X
WHERE X.PercentEquityRisk <= R_RiskNoZeroes.PercentEquityRisk
) AS Rank, R_RiskNoZeroes.PercentEquityRisk
FROM R_RiskNoZeroes;

My Results:

AccountNumber Rank PercentEquityRisk
1111 1495 1.94%
1111 1495 1.94%
1111 901 0.97%
1111 901 0.97%
1111 381 0.49%
1111 381 0.49%
2222 1219 1.60%
2222 951 1.06%
2222 951 1.06%
2222 460 0.53%
2222 460 0.53%

Of note, I have about 1690 records in this query, which is why the rank is so high, but it is ranking in order, just not using 1,2,3,4...instead using higher numbers, and it is treating a tie as the same rank...any thoughts???

15. Registered User
Join Date
May 2005
Location
Posts
2,888
For starters, you'd need to add accountnumber to the subquery, so the results reset for each account. Second, to use his method you'd need to reverse it so the first record was 1. Third, you'd need to eliminate ties.

If you post a sample db with some data and your expected results based on that data, I'll play with a function and Teddy may have time to post his method. His is intriguing, so I'm interested to see it in action.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•