Access Query to Random select 10 Record By Analyst

reggiete

Registered User.
Local time
Today, 01:45
Joined
Nov 28, 2015
Messages
56
Hello All,

I am needing some help on a query i am trying to build. I am needing to select 10 random records by each Assigned Analyst. See below code

SELECT TblEmailReview.[Assigned Analyst], TblEmailReview.ManagerAssigned, TblEmailReview.[Testing Month], TblEmailReview.Status
FROM TblEmailReview
WHERE (((TblEmailReview.[Testing Month])="Test") AND ((TblEmailReview.Status)="Email Review Completed") AND ((TblEmailReview.EmailTestID) In (Select TOP 10 EmailTestID
From TblEmailReview As Dupe
Where Dupe.[Assigned Analyst] = TblEmailReview.[Assigned Analyst]
Order BY Dupe.[Assigned Analyst] DESC, Dupe.EmailTestID DESC)) AND ((TblEmailReview.CE1)="Pass") AND ((TblEmailReview.TILA1)="Pass") AND ((TblEmailReview.TILA2)="Pass") AND ((TblEmailReview.TILA3)="Pass") AND ((TblEmailReview.NPI1)="Pass") AND ((TblEmailReview.NPI2)="Pass") AND ((TblEmailReview.RegB1)="Pass") AND ((TblEmailReview.RegB2)="Pass") AND ((TblEmailReview.RegB3)="Pass") AND ((TblEmailReview.RegB4)="Pass") AND ((TblEmailReview.SafeAct1)="Pass"))
ORDER BY TblEmailReview.[Assigned Analyst], TblEmailReview.ManagerAssigned, TblEmailReview.[Testing Month];

This code works, but it only selects the top 10 records. For example,

I have a analyst named Joe. I run the query it only pulls 9 records for Joe, due to the top 10 records for Joe are not all in the status of " Completed ". So the query only pull the 1st 9 records because those are in the right status based on the critera above.
 
Im aware that the query is calling for the TOP 10 records by Analyst, so how can i have the query pull 10 records that are in the " Completed " status?
 
In the case you named, you can't pull what ain't there. If Joe doesn't have 10 completed records, you can't pull 10 completed records no matter WHAT your criteria.

Now, let's address the actual subject topic - random select. It is actually quite easy. First, add a field to the table, type SINGLE would do for this purpose; call it Randy. Precede your selection with a query that sets all [Randy] to 0.0, then perform a second query that sets all [Randy] to RND(some number x where 0 < x < 1 million). Any number will do, "million" is just arbitrary on my part. You can omit the first query (setting to zero) if the second query is not filtered by a WHERE clause.

NOW make your query to select on your assigned analyst and take the top 10 [Randy].
Further comment - you have this clause: Where Dupe.[Assigned Analyst] = TblEmailReview.[Assigned Analyst] - which is a less than perfect choice. Having embedded spaces in a field name is a raw invitation to confusion, because Access and some other libraries of callable functions really don't like spaces in a field name. Just run the words together without spaces. It will be just as easy to recognize and will allow you to omit brackets in many cases, thus making it easier to type.
 
then perform a second query that sets all [Randy] to RND(some number x where 0 < x < 1 million). Any number will do, "million" is just arbitrary on my part.

The number does nothing. The same result occurs with no parameter.

Moreover, it is important to run the Randomize command before Rnd() or the same sequence of random numbers may be returned.

https://support.office.com/en-us/article/Rnd-Function-503cd2e4-3949-413f-980a-ed8fb35c1d80

Adding fields to tables and updating is a slow clumsy solution.

It can be done more directly using a recordset then choosing a random record via its AbsolutePosition.

Code:
Randomize
rs.MoveLast
rs.AbsolutePosition = CLng(Rnd() * rs.RecordCount)
The MoveLast is required in DAO recordsets to ensure all the records are loaded before getting the count.
 
Code:
rs.AbsolutePosition = CLng(Rnd() * rs.RecordCount)

Actually that would bias the first and last records.

Better to use
Code:
rs.AbsolutePosition = CInt(Rnd() * rs.RecordCount + 1)
 
Moreover, it is important to run the Randomize command before Rnd() or the same sequence of random numbers may be returned.
I have always had a bugbear with this, surely forcing it to not return the same numbers twice makes it less random:confused:
 

Users who are viewing this thread

Back
Top Bottom