N基于MS-Access中自己的SQL语句的最佳记录选择 [英] N Top Record Selection Based on Own SQL Statement in MS-Access

问题描述

我正在重新编写一个小的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.

如果每项考试都有一定数量的问题，我可以将数字粘贴在TOP语句中并用它来完成，但是每项考试都有可变数量的问题，因此我想替换常数TOP旁边的数字以及查询中的字段.

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);

当我打开报告时，我将为每个考试课程的查询提供新的ExamID，因此这可能会给您带来麻烦.

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

推荐答案

我认为您将必须动态构建查询.

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

这篇关于N基于MS-Access中自己的SQL语句的最佳记录选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！