I have a table being used as a question bank. When I started using it I was only generating test on one subject at a time so I used a query that looks like this
Rnd([ID]) generates a random number and sorts in ascending order then it outputs 10 random questions for the selected topic from the question bank. My problem comes when I want to combine two topics, both with 10 random questions from the question bank.
When this query is run only, the second category of the top 10 questions are changed based on Rnd([ID]). I am very much a novice when it comes to dealing with this type of stuff, any help or advice on how to achieve the desired end result.
Code:
SELECT TOP 10 Questions.ID, Questions.CAT, Questions.Job, Questions.Department, Questions.Question, Questions.Answer1, Questions.Correct1, Questions.Answer2, Questions.Correct2, Questions.Answer3, Questions.Correct3, Rnd([ID]) AS Expr1
FROM Questions
WHERE (((Questions.CAT)="Daily Check") AND ((Questions.Job)="Manager") AND ((Questions.Department)="HR"))
ORDER BY Rnd([ID]);
Rnd([ID]) generates a random number and sorts in ascending order then it outputs 10 random questions for the selected topic from the question bank. My problem comes when I want to combine two topics, both with 10 random questions from the question bank.
Code:
SELECT TOP 10 Questions.ID, Questions.CAT, Questions.Job, Questions.Department, Questions.Question, Questions.Answer1, Questions.Correct1, Questions.Answer2, Questions.Correct2, Questions.Answer3, Questions.Correct3, Rnd([ID]) AS Expr1
FROM Questions
WHERE (((Questions.CAT)="Daily Check") AND ((Questions.Job)="Manager") AND ((Questions.Department)="HR"))
ORDER BY Rnd([ID]);
UNION SELECT TOP 10 Questions.ID, Questions.CAT, Questions.Job, Questions.Department, Questions.Question, Questions.Answer1, Questions.Correct1, Questions.Answer2, Questions.Correct2, Questions.Answer3, Questions.Correct3, Rnd([ID]) AS Expr1
FROM Questions
WHERE (((Questions.CAT)="Month Check") AND ((Questions.Job)="Manager") AND ((Questions.Department)="HR"));
When this query is run only, the second category of the top 10 questions are changed based on Rnd([ID]). I am very much a novice when it comes to dealing with this type of stuff, any help or advice on how to achieve the desired end result.
Last edited: