Tutorial :N Top Record Selection Based on Own SQL Statement in MS-Access



Question:

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.OpenForm strExamName, , , "tblExam.ExamID = " & strExamID  


Solution:1

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »