How to weight randomization

[MajP]

I added a field in your table for the random assignment.

query 1a and 1b work together.
1a demos how to do the select query
1b uses the select query to do an update query

query 2 does it all in one step.
So you can run 1b or 2 and it will update your table.

I did another query to verify the data. It is an aggregate

Random_Assignment	CountOfRandom_Assignment
0	                502
1	                303
2	                92
3	                103

results are as expected 50, 30, 10, 10

 

[stillsarah]

Thank you so much! I appreciate it

 

[MajP]

Not sure of your final purpose, but if you are going to expand this, most often data is pulled from common distributions. For example a bell curve normal distribution. The theory is the same. You sample from a uniform 0,1 and run that value through the inverse CDF. You can call all of these inverse functions from Access using the worksheetfunction.
This example pulls a random number from a normal distribution with a given mean and standard deviation.

Public Function GetRandFromNormal(Mean As Double, StdDev As Double, Optional ID As Long, Optional Repeatable As Boolean = False) As Double
  Dim rtn As Double
  If Not Repeatable Then Randomize
  GetRandFromNormal = Excel.Application.WorksheetFunction.Norm_Inv(Rnd(), Mean, StdDev)
End Function

This format can be modified for any of the excel inverse cdf functions.

 

[Deleted member 147267]

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

Not sure I understand the question because that field is right in the post. Note that the order of random values in the table is in the order of random weights. The first cumulative value should be zero, the rest are the sums of the cumulative value and the random weight from the previous record. The research I did on this indicates that's how it's set up. In the example I posted, if rnd returns 0.14 (X) the SELECT returns 3 because .1 is the highest cumulative value that is less than X. If rnd returns a value from .4 to .99 the selected value would be 0. If rnd returns .21 the value would be 1.

AFAIK, if your cumulative values don't add up to 1, your results will be skewed as I pointed out. If their sum exceeded 1, your rnd results will also skew the results if rnd isn't multiplied by a factor large enough to exceed your highest cumulative value. In the example I provided, rnd only needs to be from just over zero to 1. Lastly, I overlooked that for the approach I posted, X cannot be coerced to Int type.

 

[stillsarah]

I'm back

If you get the first part working then we can use that to write back to your table.

@MajP I'm trying to find in your code how you did this, but can't.

Assuming you didn't copy and paste?

 

[MajP]

OP has continued this thread in a new thread
https://www.access-programmers.co.uk/forums/showthread.php?t=303564
Please respond to new thread.