Results 1 to 15 of 15
  1. #1
    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. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote 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
    ...
    George
    Home | Blog

  4. #4
    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. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Was my post not in the right direction?
    Paul

  6. #6
    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. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    Quote Originally Posted by csf01
    Note that some accounts can have anywhere from one to fourteen records
    fourteen JOINs.
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And Teddy; nice solution, seems very elegant.
    George
    Home | Blog

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    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. #14
    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. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

Posting Permissions

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