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.
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.