How can I pull a random percentage of records from a table?

[Guest]

I would like to have Access give me a user entered percentage of total
records from a simple table, but would like the report to be based on a
random sampling of the total records. How would I be able to do this?

 

[Allen Browne]

Save the report unbound, and assign its RecordSource in its Open event.

You can build the SQL string to ask for a percentage of records. You can
order the records randomly to get a random selection.

The following example assumes a table named Table1, with an AutoNumber
primary key named ID.

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage as string
Dim strMsg As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT Table1.* FROM
Table1 ORDER BY Rnd([ID]);"
Else
strMsg = "Percentage must be between 1 and 100."
End If
Else
strMsg = "No percentage entered."
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, vbExclamation, "Report not opened."
Else
Randomize
Me.RecordSource = strSql
End If
End Sub