top n random results from a query

[Guest]

I am a beginner at Access. This site has been ALOT of help. I am trying to
create a database to make a 200 question test. The test are divided into 15
subects and each subject having a different number of questions. For example
subject 1 has 15 questions, subject 2 has 10 questions and so on to add up to
200. I have a question bank of over 1000 question. Is it possible to make a
query to do this or do I have to have a single query for each subject than
merge the queries. Appreciate any help to get me on the right track.

 

[Marshall Barton]

I am a beginner at Access. This site has been ALOT of help. I am trying to
create a database to make a 200 question test. The test are divided into 15
subects and each subject having a different number of questions. For example
subject 1 has 15 questions, subject 2 has 10 questions and so on to add up to
200. I have a question bank of over 1000 question. Is it possible to make a
query to do this or do I have to have a single query for each subject than
merge the queries. Appreciate any help to get me on the right track.

It might be possible if you have an appropriate table
structure. Would you care to explain the relevant tables
and their fields?

 

[Guest]

I have a main table with with the following fields: QuestionNum
(Autonumber), Question, Option1, Option2, Option3, Option4, Subject , and
CorrectAns. I also have a form to enter the new questions. That's all I
have now. Willing to redo anything that needs to be changed. Thanks.

 

[Marshall Barton]

You probably will want a table of Subjects with at least
these fields:
Subject
NumQuestions
other fields related specifically to a subject.

This table could also be used in a combo box to select the
subject on your enter/edit questions form.

After spending a couple of hours on your scenario, I have
not been able to come up with a way to select a different
number of random records for each subject.

I could get the same number of random records for each
subject using the TOP predicate, but that can not be
parameterized to a different number for each subject.

I could easily get a random selection of records using the
Rnd function, but no matter what I did, I could not organize
the records because every reference to the randomized field
re-evaluated the function, which changed the relationship
between the records. Seems like its some kind of a catch22.

At this point, I think this problem will require either a
temporary table or another field in the Questions that can
be updated with a random value during this sequence of
queries. In other words the whole issue is getting very
messy.

I will continue to think about this, but in the meantime, I
hope someone else will jump in with a solution or at least
some useful comments.

 

[Marshall Barton]

I have a main table with with the following fields: QuestionNum
(Autonumber), Question, Option1, Option2, Option3, Option4, Subject , and
CorrectAns. I also have a form to enter the new questions. That's all I
have now. Willing to redo anything that needs to be changed. Thanks.

Well, I think I figured out a sequence of queries to do
this. Using the Subjects table I described earlier and a
temporary table, I came up with these queries:

Query MakeTemp
SELECT Rnd(Y.Question) AS QID, Y.Question, Y.Subject
INTO Temp
FROM maintable AS Y

Query Sequence
SELECT Q.QID, Q.Question, Q.SubJect,
(SELECT Count(*)
FROM Temp As C
WHERE Q.SubJect = C.SubJect
And C.QID <= Q.QID
) As Seq
FROM Temp As Q

Query Test
SELECT Q.Question, Q.SubJect, Q.Seq
FROM QFRrnd2 As Q INNER JOIN Subjects As S
ON Q.Subject = S.Subject
AND Q.Seq <= S.NumQuestions

To use this mess, you first have to make sure table Temp
does not exist, then execute query MakeTemp.

Then you can open query Test to see the randomly selected
questions. If you want a report to print the test
questions, then you can use command button a form to
automate the whole process:

Set db = CurrentDb()
db.TableDefs.Delete "Temp"
db.Execute "MakeTemp"
DoCmd.OpenReport "nameoftreport"

where the report's record source would be the query Test.

I do want to add that I really hate using temporary tables
so I am not thrilled with this solution. In your case,
without multiple users and a very small mdb file, I think
you can get away with it by doing the occasional Compact.

As I said earlier, you could avoid that by adding the QID
field (Double) to the main table and using an Update query
to set the field to the random number instead of deleting
and recreating the table Temp, but that sounds kind of
kludgy too.

 

[Guest]

Thanks for all your time. I am getting started on your solution right now.
Thanks again for your help.

 

[Marshall Barton]

I just noticed a typo in query Test. The From clause should
be:
FROM Sequence As Q INNER JOIN Subjects As S

 

[Guest]

Thanks again. Like I said I am a beginner, it toke almost all day to get the
code in. But I did get it to work. Your solution will work great for my
needs. There is one error that I get in the Query Test. When I switch frm
the SQL view to the design view I get an error msg "MS Access can't represent
the join expression Q.Seq<=S.NumQuestions in Design view." I can run the
query and it works, but I can't go to the design view. Is there something I
can do or should I not worry about it?
Thanks again

I just noticed a typo in query Test. The From clause should
be:
FROM Sequence As Q INNER JOIN Subjects As S
--
Marsh
MVP [MS Access]

Thanks for all your time. I am getting started on your solution right now.
Thanks again for your help.

 

[Marshall Barton]

There are quite a few things you can do in SQL that the
query design grid can not represent. The non-equi Join is
one of them.

You can worry about if you like, but, since you can't do
anything about it, worrying will not make a difference ;-)

Don't forget to keep an eye on the size of your mdb file and
Compact it when it grown too much. And make lots of backup
copies too.

 

[Guest]

I won't worry about it. Thanks for all the help and the advice. I will
definately use it.

There are quite a few things you can do in SQL that the
query design grid can not represent. The non-equi Join is
one of them.

You can worry about if you like, but, since you can't do
anything about it, worrying will not make a difference ;-)

Don't forget to keep an eye on the size of your mdb file and
Compact it when it grown too much. And make lots of backup
copies too.
--
Marsh
MVP [MS Access]

Thanks again. Like I said I am a beginner, it toke almost all day to get the
code in. But I did get it to work. Your solution will work great for my
needs. There is one error that I get in the Query Test. When I switch frm
the SQL view to the design view I get an error msg "MS Access can't represent
the join expression Q.Seq<=S.NumQuestions in Design view." I can run the
query and it works, but I can't go to the design view. Is there something I
can do or should I not worry about it?
Thanks again