Combining Queries

[E.Q.]

We have a database to track training. One table, tblQuestions, contains
questions to test employee knowledge at the end of training. So far we have
327 questions in that table.
We'd like to tag a few questions that will appear on every test and randomly
select about 30 more for a 50-question test.
I've added a boolean field to the table to tag the "Universal" questions. I
have two queries that work properly. Each outputs a two-field dataset
(lngSkillSetID and chrQuestion). qryUniversal selects questions for which the
boolean is true; the other, qryRandomQuestions, uses "top 30" with the
random feature.
I can fudge the 50-question test by creating a blank table and doing append
queries on that table using qryUniversal and qryRandomQuestions.
I would like to have a single query to combine the output from
qryRandomQuestions and qryUniversal and use a report to generate the test.
Would this be a case for the "Make Table" query option? What would be the
best way to set this up so my fellow supervisors that are less
Access-litterate can create a test?
Any help will be appreciated.
Peace.
EQC

 

[vbasean]

We have a database to track training.  One table, tblQuestions, contains
questions to test employee knowledge at the end of training.  So far we have
327 questions in that table.
We'd like to tag a few questions that will appear on every test and randomly
select about 30 more for a 50-question test.
I've added a boolean field to the table to tag the "Universal" questions.  I
have two queries that work properly. Each outputs a two-field dataset
(lngSkillSetID and chrQuestion). qryUniversal selects questions for which the
boolean is true; the other, qryRandomQuestions,  uses "top 30" with the
random feature.
I can fudge the 50-question test by creating a blank table and doing append
queries on that table using qryUniversal and qryRandomQuestions.
I would like to have a single query to combine the output from
qryRandomQuestions and qryUniversal and use a report to generate the test.
Would this be a case for the "Make Table" query option? What would be the
best way to set this up so my fellow supervisors that are less
Access-litterate can create a test?
Any help will be appreciated.
Peace.
EQC

All fields being equally the same and in the same order then:

SELECT qryRandomQuestions.*
FROM qryRandomQuestions
UNION ALL SELECT qryUniversal.*
FROM qryUniversal;

fields not the same but or in the same order

SELECT qryRandomQuestions.fieldname1, qryRandomQuestions.fieldname2,
etc
FROM qryRandomQuestions
UNION ALL SELECT qryUniversal.fieldname1(that matches field name 1
above), qryUniversal.fieldname2(that matches field name 2 above), etc
FROM qryUniversal;

order of fields is key

 

[vbasean]

All fields being equally the same and in the same order then:

SELECT qryRandomQuestions.*
FROM qryRandomQuestions
UNION ALL SELECT qryUniversal.*
FROM qryUniversal;

fields not the same but or in the same order

SELECT qryRandomQuestions.fieldname1, qryRandomQuestions.fieldname2,
etc
FROM qryRandomQuestions
UNION ALL SELECT qryUniversal.fieldname1(that matches field name 1
above), qryUniversal.fieldname2(that matches field name 2 above), etc
FROM qryUniversal;

order of fields is key- Hide quoted text -

- Show quoted text -

maybe I should be a little more explicite

create a new query in SQL mode

do as above stated

save it, maybe call it qryTestQuestions

create a report (maybe with the report wizard) use this new query you
created "qryTestQuestions"

Hope that helps

 

[Dale Fye]

Or, you could create a single query that looks something like:

SELECT Top 50 *
FROM tbl_Questions
ORDER BY [Universal], RND([ID])

If you use Randomize before this to randomize your number seed, you should
get all your universal questions and however many more it takes to get to 50
(allows you to change the number of universal questions without having to
rewrite the qryRandomQuestions.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[E.Q.]

Thank you, the UNION operand did exactly what I was hoping to find.
And the single query Dale suggested is quite elegant. I've tried both. Both
work.
I think I'm going to use the UNION method this time because I will likely
use qryUniversal for another report.
Thank you both, I've learned two viable approaches to this one question.
Peace.
EQC