I am trying to get random rows from the same table using a union query. If I run the first half of the query by itself self I get random rows on refresh but once I add my union for another category they are ordered by RandomValue but the query no longer pulls random rows. Any help is greatly appreciated.
Code:
SELECT Top 10 *
FROM (SELECT *,
Rnd(ID) AS RandomValue
FROM MyTable)
WHERE (((Question.CAT) IN ("topic1") AND ((Questions.Position)="Manager")
ORDER BY RandomValue
UNION ALL
SELECT Top 4 *
FROM (SELECT *,
Rnd(ID) AS RandomValue
FROM MyTable)
WHERE (((Question.CAT) IN ("topic2") AND ((Questions.Position)="Manager")
ORDER BY RandomValue