How to weight randomization (1 Viewer)

stillsarah · Jan 26, 2019

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 · Jan 26, 2019

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

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

isladogs · Jan 26, 2019

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

Micron · Jan 26, 2019

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.

Micron · Jan 26, 2019

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 · Jan 26, 2019

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

Code:

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 · Jan 26, 2019

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

Code:

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

Code:

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 · Jan 26, 2019

Code:

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.

Code:

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

stillsarah · Jan 28, 2019

Sorry,
I meant 1 - 30%**

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

stillsarah · Jan 28, 2019

MajP said:
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

Code:

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 · Jan 28, 2019

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

MajP · Jan 28, 2019

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

stillsarah · Jan 28, 2019

MajP said:
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 · Jan 28, 2019

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 · Jan 28, 2019

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 · Jan 28, 2019

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

Code:

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

Code:

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 · Jan 28, 2019

Sorry I messed the table up on the bottom range

Code:

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

stillsarah · Jan 28, 2019

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 · Jan 28, 2019

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.

Code:

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 · Jan 28, 2019

Screenshots attached

How to weight randomization (1 Viewer)

Registered User.

Banishment Pending

MVP / VIP

AWF VIP

AWF VIP

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Registered User.

Registered User.

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

Registered User.

Attachments

Similar threads

Users who are viewing this thread