Getting 2 Groups of 10 Random Records, Same Field

[aslietman]

I have a database of words of different levels for tutoring.

I want to get 10 random words of one level as CurrentWords, and another 10 random words (same field as first) of different levels as ReviewWords.

I tried this:

SELECT TOP 10 tblWords.WordName AS CurrentWords, tblWords.WordID
FROM tblWords
WHERE tblWords.Substep="1.4"
UNION
SELECT TOP 10 tblWords.WordName AS ReviewWords, tblWords.WordID
FROM tblWords
WHERE tblWords.Substep="1.3"
ORDER BY Rnd([WordID]);
but it doesn't work.

I can get 10 random words fine for one level only.

Thanks,
Amanda

 

[PHV]

You may try something like this:
SELECT *, 'CurrentWords' As TypeOfWord
FROM (SELECT TOP 10 WordName, WordID FROM tblWords WHERE Substep="1.4" ORDER BY Rnd([WordID]))
UNION ALL SELECT *, 'ReviewWords'
FROM (SELECT TOP 10 WordName, WordID FROM tblWords WHERE Substep="1.3" ORDER BY Rnd([WordID]))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PCLewis]

Also..

SELECT TOP 10 First(WordName) as Word, True AS CurrentWords, False as ReviewWords
FROM tblWords
WHERE tblWords.SubStep="1.3"
Group By Rnd([ID])
union all
SELECT TOP 10 First(WordName) AS Word, False AS CurrentWords, True as ReviewWords
FROM tblWords
WHERE tblWords.SubStep="1.4"
Group By Rnd([ID]);

PHV's solution looks nicer to me though but note that we both suggest you have to create another field in order to distinguish the word types. Field names in a UNION will have the field names from the first select statement so unles syou included the SubStep in the output, you'd be hard pressed to distinguish the word type.

 

[aslietman]

Thank you PH and PCLewis. I tried the first suggestion first, and it worked beautifully! I am very grateful.

Amanda