Top 2 per Group

[hlock]

Access 2007 - I'm trying to randomly select 2 claims per examiner. My first
query successfully creates a RandomSeed field that contains a unique number
for each Examiner/Claim record. However, I'm having trouble with my second
query:

SELECT tblRandomRec.Examiner, tblRandomRec.Claim, tblRandomRec.RandomSeed
FROM tblRandomRec
WHERE (((tblRandomRec.RandomSeed) In (SELECT TOP 1 RandomSeed from
tblRandomRec as RR WHERE rr.claim=tblrandomrec.claim GROUP BY randomseed)))
ORDER BY tblRandomRec.Examiner, tblRandomRec.Claim;

What I want to end up with is 2 claims for each examiner, such as:

Examiner Claim
Gary 12345
Gary 98762
Tom 34567
Tom 83903
Joyce 58439
Joyce 20899

With my query not working correctly, I'm still getting all examiners and all
claims. Is there something wrong with the query? Thanks for your help!

 

[Allen Browne]

You want 2 claims per examiner?

SELECT tblRandomRec.Examiner,
tblRandomRec.Claim,
tblRandomRec.RandomSeed
FROM tblRandomRec
WHERE tblRandomRec.RandomSeed IN
(SELECT TOP 2 RandomSeed
from tblRandomRec as RR
WHERE rr.Examiner = tblrandomrec.Examiner
ORDER BY randomseed)
ORDER BY tblRandomRec.Examiner, tblRandomRec.Claim;

 

[hlock]

Perfect!!!! Thank You!! I knew that it was a sub-query that I had to do - I
just wasn't getting the parts.