Random numbers that are weighted (1 Viewer)

I have made a database in access to randomly pick numbers. I just need to weight the numbers based on the types of numbers. After a lot of research, I feel I need to make a macro that weights product number types. Some products would be A, B, C and D and A would weighted at a high percentage of being picked at 90% for example, and the other number types would be picked less likely. Maybe B types would be picked 70% of time and C and D types around 30%. Can anyone help guide me on this?

I would create a table called something like Weighted which holds the Weighted Categories (A, B, C, D) in the percentages you want. Something like this:

WeightedID, WeightedCategory
1, A
2, A
3, A
4, A
5, A
6, B
7, B
8, C
9, C
10, D

Then generate a random number from 1-10 (or however large the Weighted table is) which you would use to lookup a category in the Weighted table. Based on the above example A would be picked 50%, B would be 20%, C would be 20% and D would be 10%.

Once you've determined a category, generate another random number and use it to pick a specific record in your product table that is in the category you've found.

What if I have:

1, A
2, A
3, A
4, A
5, A
6, C
7, C
8, C
9, C
10, D

And I want use some kind of code in a macro or module to pull 90% of product numbers with A's and 20% of C's and D's.

I understand the manual database of weighting items where I need to add more to obtain a better chance of getting something. In the example I made, I have a 50% of getting an A, but can I use a macro to make it 90%?

What table is that sample data from that you posted?

I don't think you are understanding my method. My method is a two step one, that first selects a category (A, B, C, D) which are weighted per your specifications. And then using that category randomly selects a product which is in that category.

They key to this process is to create a table (called Weighted) which properly weighs the categories. To make Category A have a 90% chance of being selected, you Weighted would look like this:

WeightedID, WeightedCategory
1, A
2, A
3, A
...
89, A
90, A
91, B
92, B
93, B
94, B
95, B
96, C
97, C
98, C
99, C
100, D

Then you randomly generate a number 1-100, whichever WeightedID it corresponds to in the Weighted table you make that the category you choose in your products table. Next, you generate another random number and use it to select a specific record in your products table.

Additionally, this could also be done via code entirely without the Weighted table. However, even then the key is to generate 2 random numbers. The first determines the category, the second would select the specific record.

I made up the sample in my last reply.

My table has a ID number(primary key), a class and a product number.

product number example = 3442-dh319h-10

I understand your method, but I do not want to change the tables unless it is my last option.

How is it possible to have A types weighted at 90%, B types weighted at 70% and C types weighted at 30% using your method. Your method makes all the percentages have to add up to 100%