1

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
Nitrodist
  • 1,585
  • 5
  • 24
  • 34
  • The N in TOP N cannot be parameterized. You'll have to write the recordsource at runtime. The OnOpen event of the form would be the appropriate place to do it. – David-W-Fenton Jul 28 '10 at 18:53
  • I ended up with a slightly ugly hack. I store the exam size into a form level integer with a `DLookUp` and then keep a counter that increments with each question answer and then exiting the exam after the counter exceeds the stored exam size. – Nitrodist Jul 28 '10 at 19:22

1 Answers1

2

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
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Good suggestion, but I would make it so that it's just inserting the `TOP` into the `SELECT` statement. Get the query, then get substring(0,5) + " TOP " + DlookUp(ExamSize) + substring(5, strLength) as the SQL query, that way we can just get the top 5 results of whatever SELECT query that we're using, should we change it. – Nitrodist Jul 30 '10 at 04:02