MS Access Query Random Sample with Priority - in VBA

[Hudas]

Good day!

I need help in creating a query:

I have a table with three columns the ID(PK), Date, BatchNumber. In the BatchNumber column there are entries like HP, MP, LP. What I need is to get a random sample of 10% of the population. But random samples should have priority based on the HP (with the highest priority),MP( medium priority), LP(low priority). The 10% of the population should be maximized first on HP before moving to the MP and MP also needs to be maximzed first before moving down to LP.

Example1:

Total Population = 120
10% of the population = 12

There are 10 entries for HP
There are 90 entries for MP
There are 20 entries for LP

The samples should have a random sample of
10 from HP
2 from MP

Example2:

Total Population = 120
10% of the population = 12

There are 50 entries for HP
There are 40 entries for MP
There are 30 entries for LP

The samples should have a random sample of
12 HP

Thank you
Hudas

 

[jdraw]

Hudas,

See if this helps.

 

[Hudas]

Thank you jdraw... the link you suggested is not quite what I need. It gives the suggested percentage from a population depending on the desired grouping. But what I'm hoping is to get the suggested percentage of the population but the samples should have emphasis on priority. Highest priority sample should be maximize first before going to the medium then to the lowest. If the 10% is already maximized at the highest priority then there is no need to get a sample from the others.

I will do my best to maybe edit the solution on the link you provided. Thank you very much!

 

[jdraw]

Do you have some sample data?

 

[Hudas]

Hello jdraw,

Attached is a sample data. lets say my sample size is 10% of the 189 records that means 19 samples should be selected. Since there are only 10 high priority then all of them will be included in the sample, there are only 9 remaining and since there is a total of 108 medium priority the 9 remaining will need to be randomly selected from the 108 medium priority. We wont have to get a sample from the low priority because our 10% target which is 19 has been achived already.

Thankyou
Hudas

 

[jdraw]

I think you would have to know (or calculate):
-your sample size
-the number of High priority items

If number of High priority >= 10% of sample size then
ignore the medium priority items
else
use 10% of sample size - number of high prioirty as X
and do a query like

Select id,field1,field2,xtrafield from table1 as B
WHERE field2="High Priority"
UNION
SELECT TOP 9 c.ID, c.field1,c.field2, c.xtrafield '<<<<<<I used 9 for X
FROM table1 AS c
WHERE (((c.[field2])="Medium Priority"))
ORDER BY xtrafield ASC;

Note: xtrafield is same as in the link I provided

 

[Hudas]

Thank you! I'll see what I can do.. Thank you again jdraw