Sampling based on the ratio of the whole population

[ldashev]

I have a population of records...let's say 10,000 athletes, grouped by sports, where (numbers below would be variable):


4,000 are from NBA
2,000 are from NHL
3,000 are from MLB
1,000 are from NFLHow can I build a sample query that will sample 100 records based on the population(meaning calculate how much NBA/NHL/MLB and NFL components are with respect to the total population and then apply that to the sample) not fully random but pull out:

NBA/Whole Population=X
Select Top X * From MainTable Where league= 'NBA' (something like this)


40 names are from NBA
20 names are from NHL
30 names are from MLB
10 names are from NFL

This is just a sample of the population, logic here is to calculate what the ratios are with regard to the whole population and then apply them to the sample size.
Regards

[andy49]

I fear this may be vba only solution

Design a query to find how many of each (and total) then use vba to:

Calculate quantity of each one (ensuring they add up to total)

SQL = "select top " & n
Firstname, lastname
From tblpopulation
Where sports = "NBA" etc

Happy to help more should you need it but not sure of your expertise in vba





Sent from my iPad using Tapatalk

[ssanfu]

And what do you want to do with these records? Display in a query, write them to a table, ...???


If you use

"SELECT top 40....

you will get the same records (athletes). To pull random athletes, see these two sites (there are a lot more sites):
http://www.techrepublic.com/blog/how...rosoft-access/
http://stackoverflow.com/questions/9...from-ms-access

[orange]

You may get some ideas from this material.