Struggling with random ratings

[EBDoom]

Hello,

I'm currently working on a tool in Access but I'm stuck. Here's a short description of what I want it to do: I want to be able to take a huge list of items that have certain ratings (for example AA, B, D) and when I run a query, I want it to randomly produce new ratings for the items based on certain probabilities and once a new rating is calculated, there will be certain new data generated, lets say x, y and z and then I add those three together to create my final data point, for each item.

The issue is, I can create the random new rating, and the calculation once I have x,y,z works, but I'm having trouble connecting x,y,z to that new random rating.

Currently, the code I use to create the new ratings is:
RandomNew: IIf(Rnd()<=[P of C to AA],"AA",IIf(Rnd(0)<=([P of C to AA]+[P of C to B]),"B","D")) ; where those are probabilities of a rating changing to AA, B or D.

This works fine but when I try to create something that is dependent on this new rating, things mess up and even a simple 'IF function' doesn't do what I need it to.


Like if I write in, if NewRating = AA, make x = 0.03, if it's B make x=0.2 else x=0.5, it doesn't work and gives me 0.03, 0.2 and 0.5 at random.

I would highly highly appreciate any help regarding this issue. I'm pretty new to Access so sorry if this is a stupid question.

Thank you so much in advance for anyone that read this.

-EB

[orange]

Can you clarify what you are trying to do?
When you say

I want it to randomly produce new ratings for the items based on certain probabilities

I think you are really "selectively" producing a new rating based on the use of probabilities. What's random about the production of the new rating? You somehow select item 50 and do some arithmetic. I think you're making something sound difficult, perhaps unnecessarily.
A sample would help us understand.

[EBDoom]

How's this?

I'm using Access 2003 and the purpose is actually pretty excel-like but since the amount of data that I'll be inputting is huge, it can't be done in excel. Basically I have a table of items (say 100k) each with a 'name', a 'current rating' (AA, B, or D), and probabilities that the item will change (or remain) to AA, B or D (all 3 probabilities add to 1).

Each rating has some associated values, there's 4 variables let's say w,x,y and z. So when the rating changes, based on the new (random) rating, there's an associate w, an associate x, y and z. So if the new rating is AA, lets say w=0.03, x=0.35, y=12 and z = 3. For B there are some other values and something else for D. These values aren't in the table, I just put them into the query later.

Now I run a query where I take the probabilities discussed earlier and using rnd() in Access and an Iff function, I create the new (random) rating. I also use iff functions to calculate the w,x,y,z based on what the new rating is and then use those w,x,y,z to do some calculations. The issue arises when I try to use the iff statements to calculate the w,x,y,z based on what the 'new rating' is. They don't give the values as they should, but rather just provide random values like they're doing the random generating of the new rating themselves in the background.

The screenshots:
http://i.imgur.com/0OnFh.jpg - this is the table
http://i.imgur.com/MPYq5.jpg - this is the query

As for the code, except for what I've already given before, it's just very simple iff functions straight from expression builder and I have put the code below in the module so I can use some excel functions in expression builder.

Public Function ExcelNORMSDIST(z As Double) As Double
ExcelNORMSDIST = Excel.WorksheetFunction.NormSDist(z)
End Function

Public Function ExcelNORMSINV(prob As Double) As Double
ExcelNORMSINV = Excel.WorksheetFunction.NormSInv(prob)
End Function

EXPLANATION OF SS: when I was talking about w,x,y,z: that is PD, LGD, EAD and Maturity in the Query and the rest are the calculations that I do. The calculations work fine when I input the w,x,y,z myself, the problem is how to make the w,x,y,z dependent on the 'random new rating'.

[orange]

Is this constant

So if the new rating is AA, lets say w=0.03, x=0.35, y=12 and z = 3.

How did you come up with these values for w,x,y,z?
You said these are associated. How are they associated? That's the key to automating this.

[EBDoom]

These are values that I know, they are constant per rating.
So AA will always have w(or PD) of 0.03, x(or LGD) = 0.35, y(EAD) = 12 and z(Maturity) =3
for B it will always be 4 other values and so on.

So basically it's like:
- put in 100k items with current rating and prob. of changing to other ones.
- randomly get new ratings and the w,x,y,z related to those
- calculate the other stuff

[orange]

I would suggest you create a new table call it whatever makes sense to you.

(Id,Rating,w,x,y,z) or ( Id, Rating, PD, LGD, EAD,Maturity)

01,AA,.03,.35, 12, 3
02, B.....

I don't fully understand how the w,x,y,z get applied,but you could do it with a little vba, or possibly even from a query.

Also, for your info here is an article on Access Random
http://www.techrepublic.com/blog/how...oft-access/149