How to weight randomization

[stillsarah]

I am trying to build a query that randomly assigns each observation (ID) a number between 0-3.

To do so, I used: X: Int(Rnd([Randomization Practice]![ID])*4)

However, now I want to edit it so that different values have different weights (0 - 50%, 1 - 30%, 2 - 10%, 3 - 10%)

Can anyone help with how to do this?

Thanks,
Sarah

 

[plog]

Build a table that correlates the random value with a weight:

TblWeights
Rvalue, Rweight
0, .5
1, .2
2,.1
3,.1

 

[isladogs]

Welcome to AWF

Perhaps I'm not understanding this properly but you seem to be wanting to apply rules when creating random numbers.
By definition they will no longer be totally random!

Perhaps just a tiny bit like Schrodinger's cat

 

[Deleted member 147267]

Colin, there is such a thing. The random number generation still applies, but whether or not it gets picked is governed by the weighting. Unfortunately, I don't know the math/approach that governs the probability that a number gets picked. Maybe I can find something, or for that matter, so can the OP, for which a link could be posted here so we can figure out how to apply the approach in Access if it's not specifically for Access.

 

[Deleted member 147267]

maybe something like this

tblRandValues
RandVal.... RandWt....Cumulative
---2 --------.1 -------- 0
---3 ------- .1 ---- --- .1
---1 ------- .2 ----- -- .2
---0 --------.5 -- -- ---.4

X = Int(Rnd([Randomization Practice]![ID]))
SELECT RandVal FROM tblRandValues WHERE Max(Cumulative) < X
Note that the *4 multiplier isn't used. Could also use DLookup perhaps.

Real shame that you can't put a table in your post
No doubt you will get a lot of zeros because of your weighted values.

 

[MajP]

This is actually a very common method to generate random numbers from a given probability distribution. You need to build the CDF (cumulative density function) and pull the inverse.

Your table is additive probability

bottomRange TopRange ReturnValue
0           .5             0
.5          .7             1
.7          .8             2
.8           1              3

so you draw a random number 0 to 1. Then see what range it is in. The top column has to end at one. You only added up to .9

Here is the theory.
http://pj.freefaculty.org/guides/stat/Distributions/DrawingRandomSamples/DrawingSamples.pdf

 

[MajP]

So as a check I generated a thousand random numbers from my CDF

ID	BottomRange	TopRange	ReturnValue
1	0	0.5	0
2	0.5	0.7	1
3	0.7	0.8	2
4	0.8	1	3

Public Sub GeneratRandom()
  
  Dim myRand As Double
  Dim myVal As Integer
  Dim i As Integer
  Dim strWhere As String
  Randomize
  For i = 1 To 1000
    myRand = Rnd
    strWhere = "BottomRange <= " & myRand & " AND TopRange >" & myRand
    Debug.Print strWhere
    myVal = DLookup("ReturnValue", "tblCDF", strWhere)
    CurrentDb.Execute "Insert into tblRandDistribution (RandomNumber) Values (" & myVal & ")"
  Next i
End Sub

I got
0: 516 ~.5
1: 183 ~.2
2: 102 ~.1
3: 199 ~.2

 

[MajP]

Public Function GetRandFromDis(Optional ID As Long) As Integer
  Dim myRand As Double
  Dim myVal As Integer
  Dim strWhere As String
  Randomize
  myRand = Rnd
  strWhere = "BottomRange <= " & myRand & " AND TopRange >" & myRand
  myVal = DLookup("ReturnValue", "tblCDF", strWhere)
  GetRandFromDis = myVal
End Function

If you call this function from a query you need to pass in a unique value (PK) for each record.

SELECT GetRandFromDis([ID]) AS Calc
FROM tblRandDistribution;

 

[stillsarah]

Sorry,
I meant 1 - 30%**

@Micron what would the “cumulative” column look like in this case?

 

[stillsarah]

This is actually a very common method to generate random numbers from a given probability distribution. You need to build the CDF (cumulative density function) and pull the inverse.

Your table is additive probability

bottomRange TopRange ReturnValue
0           .5             0
.5          .7             1
.7          .8             2
.8           1              3

so you draw a random number 0 to 1. Then see what range it is in. The top column has to end at one. You only added up to .9

Ok so assuming I change the ranges to add up to 1, how do I build an expression that pulls from this? Thanks!

 

[MajP]

I thought I provided that in #8. It works for me.

 

[MajP]

And in #7 I demonstrated the it pulls the correct distribution of random numbers.

 

[stillsarah]

I thought I provided that in #8. It works for me.

Apologies - I'm completely new to Access and SQL. Can you please spell out exactly what steps to take?

Where in this code is the reference to the specific table? If I copy and paste this into a query in sql view, it comes up with a "invalid statement" error

:banghead:

 

[MajP]

What are you starting with and where do you want the numbers displayed? Do you have a query that you are starting with and want to return a random number from the distribution back to the query or do you simply want to put these into a table.

 

[stillsarah]

I want them in a table.

The table you created (adapted to reflect the 50 - 30 - 10 -10 distribution I meant to have initially) is named "Complicated" and the table where I have the individuals IDs is "Randomization Practice". Ideally the randomized treatment arms (0 - 3) would show up in this table.

Hope this answers your questions.

Thanks again!

 

[MajP]

So if this table name is "Complicated. The field names are below

bottomRange TopRange ReturnValue
0           .5             0
.5          .8            1
.7          .9             2
.8           1              3

Public Function GetRandFromDis(Optional ID As Long) As Integer
  Dim myRand As Double
  Dim myVal As Integer
  Dim strWhere As String
  Randomize
  myRand = Rnd
  strWhere = "BottomRange <= " & myRand & " AND TopRange >" & myRand
  'the table name is the second argument
  myVal = DLookup("ReturnValue", [COLOR="DarkRed"]"Complicated"[/COLOR], strWhere)
  GetRandFromDis = myVal
End Function

One thing is do yourself a favor and do not name anything in Access with a space in the name. This causes big problems.

Now here is the thing. You can make a query that shows the numbers assigned to an ID. But my guess is you need to persist these values.
So make this query. I am removing your space, you should do the same. If you do not know how to do this in design view, select SQL view and paste.

Select [YourIDNameHere] GetRandFromDis([YourIDNameHere]) from Randomization_Practice

If this works then you save this query. Then open this query and either use it to do an update query into Randomization_Practice or a make table query.

If you do not have to persist the values you are done, but every time you run the query you will get new values.

 

[MajP]

Sorry I messed the table up on the bottom range

bottomRange TopRange ReturnValue
0           .5             0
.5          .8            1
.8          .9             2
.9           1              3

 

[stillsarah]

I'v gotten rid of the space and I'm getting the same error message as before.

Sorry for being dense, but I'm copying this code into sql view of a new query, correct?

You're correct that I need them to persist.

If this works then you save this query. Then open this query and either use it to do an update query into Randomization_Practice or a make table query.

I don't understand what you mean here

 

[MajP]

Any chance you can post a copy of your db or just the two tables? If the tables have personal information you can delete those fields. I really just need the ID field.

I don't understand what you mean here

The query I provided would only show results, but would not write them to a table. If you get the first part working then we can use that to write back to your table.

 

[stillsarah]

Screenshots attached