Random Sampling

[dwight1]

Is there anyway i could get a random sampling out of a population of records for audit purposes?

Example, If i have 100 records in a table i want 10 from the table to be picked for auditing.

I would appreciate any input?

Dwight

 

[Remou]

This may help:
LIMIT parameter
thread701-1355456

 

[PortyAL]

Hi

I an auditor and use the following code in one of my sampling databases.

Private Sub Command2_Click()
Dim strsql
Dim TopN

Set db = CurrentDb
Set qdf = db.QueryDefs("qry sample")

TopN = InputBox("How many samples?")

strsql = " Select Top " & TopN & " [qry Invoices (no threshold)].ID, [qry Invoices (no threshold)].Type, [qry Invoices (no threshold)].Date, [qry Invoices (no threshold)].[Account Code], [qry Invoices (no threshold)].Year, [qry Invoices (no threshold)].Supplier, [qry Invoices (no threshold)].[Reference 3], [qry Invoices (no threshold)].Reference, [qry Invoices (no threshold)].Period, [qry Invoices (no threshold)].Gross, [qry Invoices (no threshold)].Random, [qry Invoices (no threshold)].From, [qry Invoices (no threshold)].To FROM [qry Invoices (no threshold)]WHERE ((([qry Invoices (no threshold)].Gross) Between [Lower Limit] And [Upper Limit] Or ([qry Invoices (no threshold)].Gross) Between -[Lower Limit] And -[Upper Limit]))ORDER BY [qry Invoices (no threshold)].Random;"

qdf.SQL = strsql

Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery "qry sample"

End Sub [/color red]

The population is in a query [qry Invoices (no threshold)]. This query also has a [random] field which is a randomly generated number, which changes each time any command is run. The code below brings up a dialog box asking how many samples you want to take (TopN), and then creates a query ([qry sample]) which sorts the data by the random number field and displays the top (TopN) number of records.
Hence a user definable random sample.

The code can also be amended to give a percentage of the total population. Insert the word "percent" (no speech marks) after strsql = " Select Top " & TopN & " [/color red] in the code above.

Hope this helps.

AL

 

[dwight1]

Thank you for your response.

PortYal, I tried your sql and it works wonderful. But you know in access i had various filters such as auditor, Date ranges. It gives me an error if i keep those filters. If i remove than it executes properly. Mainly it looks like it has a problem with "as".

The reason i need that is for example: I have some 10 auditors who audit. I need to be able to pull the sample for a specific auditor for the specific week by the auitcompleted volume.

If auditor A had completed 100 audits and i pick up 10% of it, that is 10 samples based on some date ranges.

i am still tweaking your sql and may get it to work.

Thank you

Dwight

 

[PortyAL]

Hi Dwight

What error does it give?

There are filters built in to the query above and it works OK. If I run the above, it first asks me for the number of records I want, and then asks for an "Upper Limit" and "Lower Limit" (which a filter built into the query). It then gives me the number of random records between the upper and lower limits.

All the above code is doing is picking the top N records of the query and letting you input what N is.

Try looking at the SQL of the query for your sample population with the filters included, then paste it into the code after strsql= " and amend the start of it to " Select Top " & TopN & "

Alternatively post the SQL on here.

Good luck

AL

 

[dwight1]

Here is my code.

I am trying to put this for a button on acces form. The name of the query is KATE_RANDOM_BASE.


Private Sub Command168_Click()
Dim strsql
Dim TopN

Set db = CurrentDb
Set qdf = db.QueryDefs("Kate_Random_Base")

TopN = InputBox("How many samples?")

strsql = " Select Top " & TOPN & " Z_data_Dump_Step3_WithoutSBOFields.DB_SIDE, Z_data_Dump_Step3_WithoutSBOFields.Loan_Number, Z_data_Dump_Step3_WithoutSBOFields.Reason_For_Review, Z_data_Dump_Step3_WithoutSBOFields.[Review Category], Z_data_Dump_Step3_WithoutSBOFields.Recommends_Claim_Review AS Recommendation, Z_data_Dump_Step3_WithoutSBOFields.Review_Complete_Date, Z_data_Dump_Step3_WithoutSBOFields.Review_Status, Z_data_Dump_Step3_WithoutSBOFields.[Assigned Staff Last Name], Z_data_Dump_Step3_WithoutSBOFields.[Completed Staff Last Name], Z_data_Dump_Step3_WithoutSBOFields.File_Location, Z_data_Dump_Step3_WithoutSBOFields.File_Location_Date, Format([Review_Complete_Date],"mmm yy") AS [Month], Rnd([loan_number]) AS Expr1
FROM Z_data_Dump_Step3_WithoutSBOFields
WHERE (((Z_data_Dump_Step3_WithoutSBOFields.DB_SIDE) = "vn"))
ORDER BY Rnd([loan_number]) DESC;

qdf.SQL = strsql

Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery "Kate_Random_Base"


End Sub