Can you create Weighted Options for an MS Access Query?

Question

I would like to create a query that generates a randomized set of values for particular fields. For example, let's say eye color. When run, the query would return a randomized eye color.

I know how to create a table with eye color values, and how to randomize and select one value. (How to get random record from MS Access database)

However, I would also like to determine how likely that eye color is to be pulled as a value. For example, blue eyes might have a likelihood of 15/100, where brown eyes might have a factor of 60/100.

Is it possible to create a field and/or query/SQL formula to achieve this? The only thing I can think of is to actually add each value X number of times into the table, but that goes against normalization and it feels like there's probably a more elegant solution.

Google 'access vba weighted randomization' and see what pops up. From what I have read, generate random number between say 1 and 100 then select eye color based on that value. something like If it is => 60 brown, => 50 blue, etc. https://stackoverflow.com/questions/7349421/microsoft-access-vb-random-generator — June7, May 29 '21 at 18:56
I did a test. Don't think that is the answer either. Why would you need this? Maybe eye color is not the best example. If you want one value selected by probability then needs to be pulled from a dataset that has records distributed by that probability of occurrence. — June7, May 29 '21 at 19:38

score 1 · Answer 1 · answered May 30 '21 at 07:42

You could create a simple function like this:

Public Function RandomEyeColour() As String

    Dim EyeColours      As Variant
    Dim EyeColourRange  As Variant
    Dim EyeColour       As String
    Dim EyeColourValue  As Single
    Dim index           As Integer
    
    EyeColours = Array("Brown", "Blue", "Grey", "Hazel", "Green", "Amber")
    ' Eye colour distribution:
    ' 83%, 8%, 3%, 2%, 2%, 2%.
    ' Add up the values.
    EyeColourRange = Array(0.83, 0.91, 0.94, 0.96, 0.98, 1)
    
    Randomize
    EyeColourValue = Rnd()
    
    For index = LBound(EyeColours) To UBound(EyeColours)
        If EyeColourValue < EyeColourRange(index) Then
            EyeColour = EyeColours(index)
            Exit For
        End If
    Next

    RandomEyeColour = EyeColour
    
End Function

Can you create Weighted Options for an MS Access Query?

1 Answers1