How to code randoms that capture each subset?

[bradley1]

I'm trying to run a "random" query in access03 from a table that has only two fields i.e claim# and Group#. The problem is that when I run the below code--it gives me the random amount of claims(360) needed however, its not capturing all groups within that random query each time I run the code…Thoughts

SELECT TOP 360 [table123].CLAIM_NUMBER, [table123].GROUP_NUMBER
FROM [table123]
ORDER BY Rnd([CLAIM_NUMBER]);

 

[Remou]

How about:

SELECT table123.GROUP_NUMBER, table123.GROUP_NUMBER
FROM table123
WHERE (((table123.CLAIM_NUMBER) In (
   SELECT TOP 2 CLAIM_NUMBER 
   FROM table123 t 
   WHERE t.GROUP_NUMBER=table123.GROUP_NUMBER 
   ORDER BY Rnd([CLAIM_NUMBER]))))
ORDER BY table123.GROUP_NUMBER;

 

[bradley1]

Remou,

The code did run however,after running it a few times--not all of the group #'s had claim examples in the random pull. For an example, I'm trying to get group#22222(1 claim) to show up each time I run the random sample query. See the below sample table:

CLAIM_NUMBER GROUP_NUMBER
111111111111 11111
121111111111 11111
131111111111 11111
141111111111 11111
151111111111 11111
211111111111 22222
311111111111 33333
411111111111 44444
421111111111 44444
431111111111 44444
441111111111 44444
451111111111 44444
461111111111 44444
471111111111 44444
481111111111 44444
491111111111 44444
492111111111 44444
511111111111 55555
521111111111 55555
531111111111 55555
541111111111 55555
611111111111 66666
621111111111 66666
611111111111 66666
621111111111 66666
631111111111 66666
641111111111 66666
711111111111 77777
721111111111 77777
731111111111 77777
741111111111 77777
751111111111 77777
761111111111 77777
771111111111 77777

 

[traingamer]

Then it's not random, is it?

What are you really trying to do?

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[bradley1]

For the above table,

I'm trying to run a query to capture claim #'s randomly from each Group# within the tbl.even if that group# only has one claim.

Note: I would need that groups one claim to show up every time I run the random query.

 

[Remou]

I have run it a number of times using your sample data and group 22222 has turned up each time.

SELECT x.GROUP_NUMBER, x.CLAIM_NUMBER
FROM table123 AS x
WHERE (((x.CLAIM_NUMBER) In (SELECT TOP 2 CLAIM_NUMBER FROM table123 t WHERE t.GROUP_NUMBER=x.GROUP_NUMBER ORDER BY Rnd([CLAIM_NUMBER]))))
ORDER BY x.GROUP_NUMBER;

However, because of the way Top works, you may get more that 2 'Top' items returned. Top returns the Top number and any equal numbers.

 

[bradley1]

Remou,

Thanks so much for the above code. It's capturing each group every time it runs and doing the random function. Due to the "Top" issue however, I guess there's no way to run this code and still give me a set amount of ramomized claims i.e 15 total each time this code runs?

Again, I want to thank you for your valuable time. I know this was a crazy request. I work for a QA dept.and they want a set # of claims randomized without excluding any groups each time this program runs.

 

[Remou]

Try:

SELECT x.GROUP_NUMBER, x.CLAIM_NUMBER
FROM table123 AS x
WHERE (((x.CLAIM_NUMBER) In (SELECT TOP 2 CLAIM_NUMBER FROM table123 t WHERE t.GROUP_NUMBER=x.GROUP_NUMBER ORDER BY Rnd([CLAIM_NUMBER])[red]+[Claim_Number][/red])))
ORDER BY x.GROUP_NUMBER;

 

[Remou]

Oops. Ignore that last post, I was not thinking.