Random Sample 1

[katrina11]

There is a list of about 1000 providers’ sites, from which from time to time user wants to retrieve a random sample of about 30 sites and then to get a random sample of about 25 patients from each of 30 sites.
Is there any specific technique for random sampling? How I can implement it in Access 2007?
Would appreciate any help!

Thank you!

Katrin

 

[PHV]

A starting point (SQL code):
SELECT TOP 30 * FROM tblSites ORDER BY Rnd([some numeric field])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[katrina11]

PHV, thank you for the quick response.

About numeric field...If my table from SQL Server which I am importing to Access contains just character fileds, should I generate primary key for this purpose?

Thank you again,

Katrin

 

[MajP]

For many uses that code will not the needs and can actually cause a lot of problems.

Here is why.
If you open the database and run the query you will get a pseudo random list. Then run it again and you will get another different pseudo random list. Lets say you do it 5 times in a session.
List1, List2, List3, List4, List5

If you close the database and come back in the next day and want a new random list. You will get the same random lists. So if you run it 5 times you will get the same 5 random lists. So if this is used to assign things on a daily basis then this does not work.

If you want to ensure every time you get a new list then build this UDF

Public Function myRnd(id As Variant) As Double
  If Not IsNull(id) Then
    Randomize 'This is the key that sets a new seed each time
    myRnd = Rnd(Now() + id)
  End If
End Function

http://msdn.microsoft.com/en-us/library/8zedbtdt(v=vs.80).aspx

You can pass any unique field into the function does not have to be PK.

 

[PWise]

if your table is in SQL SERVER

Use this Pass Thru Query

SELECT TOP 30 * 
FROM tblSites 
ORDER BY NEWID()