N Top Record Selection Based on Own SQL Statement in MS-Access
I'm re-writing a small ms-access application to take examinations on.
What they want is for the tests to grab a set of random questions based on how large the exam's size is.
If each exam was a set number of questions, I could just stick the number in the TOP
statement and be done with it, but there are a variable number of questions for each exam, so I want to replace the constant number next to the TOP
with a field from the query.
What I basically want is like this:
SELECT TOP tblExam.[ExamSize] *
FROM tblExamQuestions INNER JOIN tblExam
ON tblExamQuestions.ExamID = tblExam.ExamID
WHERE tblExam.ExamID = 10
ORDER BY Rnd(tblExamQuestions.ExamQuestionID);
I'm supplying the new ExamID
to this query for each exam session when I open the report, so this will probably get in the way.
DoCmd.O开发者_运维知识库penForm strExamName, , , "tblExam.ExamID = " & strExamID
I think you would have to build the query dynamically.
sSQL="SELECT TOP " & DlookUp("ExamSize","tblExam","ExamID = 10") _
& " FROM tblExamQuestions INNER JOIN tblExam " _
& "ON tblExamQuestions.ExamID = tblExam.ExamID " _
& "WHERE tblExam.ExamID = 10 " _
& "ORDER BY Rnd(tblExamQuestions.ExamQuestionID)"
'' Permanently change an existing query
CurrentDB.QueryDefs("MyReportQuery").SQL=sSQL
精彩评论