Solved Union Query (1 Viewer)

swingline

Member
Local time
Today, 04:47
Joined
Feb 18, 2020
Messages
51
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

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:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:47
Joined
May 7, 2009
Messages
19,169
use one query and just alter the criteria:

...Questions.CAT in ("Daily Check", "Month Check") And ...
 

swingline

Member
Local time
Today, 04:47
Joined
Feb 18, 2020
Messages
51
use one query and just alter the criteria:

...Questions.CAT in ("Daily Check", "Month Check") And ...
I'm not sure how to make this work. If I just add "Month Check" I get syntax error (comma) in query expression
 

swingline

Member
Local time
Today, 04:47
Joined
Feb 18, 2020
Messages
51
Well that was my fault I changed it to what you actually sugested and it works great. Thanks for the help

Solution.
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) IN ("Month Check","Daily Check"))AND ((Questions.Job)="Manager") AND ((Questions.Department)="HR"));
 
Last edited:

swingline

Member
Local time
Today, 04:47
Joined
Feb 18, 2020
Messages
51
I spoke too soon I still need help with this.

If I run this query I end up with only 10 questions total not 10 questions from each category.

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) IN ("Month Check","Daily Check"))AND ((Questions.Job)="Manager") AND ((Questions.Department)="HR"));

I have the orginal problem when I run a union. Only the last 10 are sorted using Ran([ID])

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) IN ("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) IN ("Month Check")) AND ((Questions.Job)="Manager") AND ((Questions.Department)="HR"));

My goal is to create a quiz out from multiple categories and have the each category sort by Rnd([ID])
 
Last edited:

swingline

Member
Local time
Today, 04:47
Joined
Feb 18, 2020
Messages
51
If I removed
ORDER BY Rnd([ID]); from the union and use the it I. The property sheet I. The Order by section it works as intended.
 

Users who are viewing this thread

Top Bottom