Find 2 Percent of Each Also Randomly

  • Thread starter Thread starter Sweeds
  • Start date Start date
S

Sweeds

So I have a database that has Examiners and Claims. Each examiner is
to audit multiple claims. Over the course of a day, they may audit
more than one claim. For example, for my table, Examiner Betty
audited 1 claim but Examiner Johnny audited 872 claims. For business
reasons, I need to pull randomly 2 percent of each Examiner's claims.
Meaning that for Examiner Betty, I would have zero because she only
audited 1 claim, but for Examiner Johnny I would return 17.

I have appended unique IDs to each of the Examiners in the table in
attempt to use the ID to pull the random amount, but it is not
working. Any more info please let me know.

Thanks in advance.
 
In your form, put one textbox and make the control source =sum([your
field]*.02)
Click to expand...

Ummm... that would multiply the field by 2%, not return 2% of the records and
then sum them!
 
So I have a database that has Examiners and Claims. Each examiner is
to audit multiple claims. Over the course of a day, they may audit
more than one claim. For example, for my table, Examiner Betty
audited 1 claim but Examiner Johnny audited 872 claims. For business
reasons, I need to pull randomly 2 percent of each Examiner's claims.
Meaning that for Examiner Betty, I would have zero because she only
audited 1 claim, but for Examiner Johnny I would return 17.

I have appended unique IDs to each of the Examiners in the table in
attempt to use the ID to pull the random amount, but it is not
working. Any more info please let me know.

Thanks in advance.
Click to expand...

You'll need a Subquery to do this. Assuming that your Claims table has a
ClaimID and taking your "randomly" comment seriously, you'll need to do
several steps.

1. Create a new Module named basRandom. Put into it this function:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Select Debug... Compile, correct any errors, and save; close the VBA editor.

2. Create a Query

SELECT Claims.ExaminerID, Claims.<whatever other fields you want to see>
FROM Claims
WHERE ClaimID IN
(SELECT TOP 2 PERCENT ClaimID FROM Claims AS X
WHERE X.ExaminerID = Claims.ExaminerID
ORDER BY RndNum(X.ClaimID))
ORDER BY ExaminerID;
 
Back
Top