Randomization with dynamic probability

[amkp711]

Hello! I am relatively new to access and definitely new to VBA. I am working on a database that will randomly assign records to one of 3 groups. I have code set up to do this with a 1:1:1 probability, but I need it to be more complex than that. I am hoping to set this up for the probability to base off of specific fields in the database (i.e. if the person is over age 60, they will have a higher probability of being assigned to group 3). There are multiple specific guidelines like this to assess the probability on a person by person basis. The code I have so far is as follows:

Public Function Rand(ByVal Low As Long, _

ByVal High As Long) As Long

Rand = Int((High - Low + 1) * Rnd) + Low

End Function

Private Sub Study_ID_AfterUpdate()

'Randomization Code

If IsNull(Me.Randomizer) Then

Me.Randomizer = Rand(1, 3)

End If

From reading online, it looks like the best way to do this may be to use cases in the public function, but I am really not sure how to or even if what I have is on the right track. I would really appreciate any help I can get on this as I am sort of crunched for time (it is for work and deadlines are approaching).

Thank you!!!

[orange]

Please provide some sample data and a more thorough description. If you can show a few records (the input) and some records in output, it would help readers to understand your requirement. Readers will respond to issues they understand.

This link may be helpful re Randomize

[jas0501]

Not sure what to formula would look like but given 3 groups each person would have a probability of being assigned to the groups.
So the range would be 0 --> x ---> y ---> z. Group1 0:x , Group2 x:y, and Group3 y:z.
0 and z can be constant.
You just need to formulas for x and y based on the individuals data.
Generate a random number between 0:1 and multiply by z

If number > y then
Group = 3
elseif number > x then
Group = 2
else
Group = 1
endif

[amkp711]

Thanks for the quick replies! To give a bit more information, the groups are for a study and randomization has to be done as weighted by various numbers which are distinct for each patient. So basically, the database needs to calculate the probability for each patient to be put in each group and then automatically randomize them.

Splitting it up that way, I am wondering if maybe I can do this in 2 steps, sort of like jas0501 suggested. Step 1 would be to calculate the probability (is there a good way to code mathematical operations in VBA? I am really very new to this). Step two would then be to randomize with those probabilities (how do I code that? I am lost...). Would that work? Am I making sense?

Thanks!!!

[orange]

Not really clarifying much.

Do you have a clear view and an example of your weighted probability? I'd like to see an example to make sure readers understand what you are trying to do.
The link I provided earlier will deal with Random records.

[amkp711]

Sorry, I am trying, I am a little unclear about how to explain it and I do not have a specific example to use. Basically the records in the database are patients in a research study. The database is going to track each patients progress through the study, etc (that is all set up already). Part of it though, is that the study needs each person to be randomly assigned into one of three groups (lets call them groups A, B, and C). As of now I have it set up so that when you enter an ID number for the patient, a hidden field called "Randomizer" auto-fills with a number (either 1, 2, or 3). Currently this is being done with a 1:1:1 probability (I think, the code I am using is in the first post). Separately, I have an if then sequence set up so that if a the randomizer field generates a 1, the person is labeled as group A. If it generates a 2 they are labelled as B. If it generates a 3, they are labeled as C. As of now, all that is set up. The issue is that I need the "randomizer" to fill based on a dynamic probability of sorts. So, if their "control level" (for example) is X then they have a different probability of being placed in a given group than if their "control level" is Y. I do not know how to set up the randomization to first calculate the probability for each group and then assign a random number between 1 and 3 (to assign the group) with that probability. What is the best way to handle this? I really have no idea.