By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

Select 5 rows with criteria 1 and 2 rows with criteria 2

P: 2
Hi all. I have been trying to write a code in a query that selects 4 questions defined as Electrical and 2 questions defined as safety and 2 questions defined as tools. These questions are in the same table. The pickel is, i also want to random select those questions from my database (table). Here is my code to random select each one from DataBase table,
DataBase = Table with all questions. ( ID | Type | Question | Answer | )

SELECT TOP 4 DataBase.*
FROM DataBase
WHERE (Type In (Electrical))
ORDER BY Rnd(IsNull(DataBase.ID)*0+1);

Now i also want to select the rest and also have them random selected.
I dont seem to be able to do so in one query.
Is there any one that has any suggestions for me?
Any help much appreciated, with regards Heimir
Jul 19 '07 #1
Share this Question
Share on Google+
2 Replies


JKing
Expert 100+
P: 1,206
Hi I think the best way to acheive this is to use multiple queries and then bring them together into one. I attempted to do this in a single query however the multiple subqueries crashed access for me. So I think this is the way to go.

qselElec
SELECT TOP 4 tblQuestion.ID
FROM tblQuestion
WHERE (((tblQuestion.Type) In ('Electrical')))
ORDER BY Rnd(IsNull(ID)*0+1);

qselSafe
SELECT TOP 2 ID
FROM tblQuestion
WHERE (Type In ('Safety'))
ORDER BY Rnd(IsNull(ID)*0+1);

qselTools
SELECT TOP 2 ID
FROM tblQuestion
WHERE (Type In ('Tools'))
ORDER BY Rnd(IsNull(ID)*0+1);

qselQuestions
SELECT DISTINCT tblQuestion.Type, tblQuestion.Question, tblQuestion.Answer, tblQuestion.ID
FROM tblQuestion, qselElec, qselSafe, qselTools
WHERE (((tblQuestion.ID) In ([qselElec].[ID],[qselSafe].[ID],[qselTools].[ID])));
Jul 19 '07 #2

P: 2
Thank you so much!

You gave me the hint to realize what i needed to do.
Use multiple queries, that was the solution.
Thanks
Jul 19 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.