Help-Test with questions from diff categories

[Ahmangbay30]

Hi all,

I'm new to the forums and new to Access. I was given a database and told to basically figure it out any way possible. Got to love it.

Here's my problem:

I have a database of 1000 questions, all multiple choice. Each line is broken down by ID#, Category, Question, Answer A, B, C, D, Correct Answer, and Reference of the Answer.

I've been able to so far create a query that makes a 50 Question test with random questions each time I run it, but I need to be able to create the same 50 Question test using a certain percentage of each Category.

To break it down, say I was creating this test for a High Schooler where I had 1000 questions comprised of the Categories: Math, English, History, Science, and Art. I would like to be able to create the 50 Question test with 10% of each category every time I run the query.

If I didn't word this well, please let me know and I'll try to explain further. Thank you guys for the help in advance!

 

[Cronk]

You could use a UNION query to combine the results of several queries, with each query being for one of the categories. There's plenty of information on union queries if you search.

Basically it would be
(SELECT * From qryCat1) union (SELECT * From qryCat2) union (SELECT * From qryCat3) union ...

or alternatively

(SELECT TOP 10 * FROM tblQs Where CatID=1) union (SELECT TOP 10 * FROM tblQs Where CatID=2) union ((SELECT TOP 10 * FROM tblQs Where CatID=3) union .....

 

[Ahmangbay30]

Thank you for the quick response Cronk. I'm going to try to do a little more research on the Union Queries like you suggested. With the codes that you provided, will that still randomize those percentage of questions? Also, where would I even go to input these?

I'm sorry for my ignorance on the matter. Like I've said, I'm new to Access with no training what so ever (formal or informal).

 

[Cronk]

I read your words "I've been able to so far create a query that makes a 50 Question test with random questions each time I run it," that you had organised the randomising bit done.

I have not had need to randomly select records from a table. There is a random number generator in Access and you could generate a string of random table IDs and use in the query WHERE TableID IN (random string)

 

[Ahmangbay30]

Yea, sorry if I worded it poorly. I've been able to randomize 50 questions from the 1000 questions, but I needed it to randomize 50 questions with a certain percentage of the questions being from a different category each time.

 

[Ahmangbay30]

Ok, I've tried using this line, with no luck:
(SELECT TOP 10 * FROM tblQs Where CatID=1) union (SELECT TOP 10 * FROM tblQs Where CatID=2) union ((SELECT TOP 10 * FROM tblQs Where CatID=3) union .....

If one my columns in my database is labeled "Subjects" and it has all the "Math", "Science", "History", etc subject titles down that column, where would I type "Subjects" as the column to pull from and "Math" as the category to look for in this code? Or am I completely off on this?

 

[Cronk]

Knowing what your category field is now, I would have written
(select top 10 * from tblQs where subject='Math') union ......

That is going to give you the same sets of 10 questions each time.

To get the randomisation there is going to have to be some coding the generate the random set.

 

[Ahmangbay30]

I still have not been able to figure this out. I've even gone to the point of buying Access for Dummies haha.

Instead of leaving all the questions from different categories in 1 table, I now created a table for each subject. I'm sure this will help simplify it. How can I now randomly pull a certain amount of questions from each subject into one test? Maybe this will help make it easier.

 

[Clownfish1980m]

I am wanting to get my hands on a database like what you created. Could you send me a copy? I am wanting to use it to competency assess new personnel. Thanks

 

[MSAccessRookie]

Knowing what your category field is now, I would have written
(select top 10 * from tblQs where subject='Math') union ......

That is going to give you the same sets of 10 questions each time.

To get the randomisation there is going to have to be some coding the generate the random set.

I agree with Cronk that the Query you showed would provide the same list of Questions each time. It also is not the way that you want to use a Union Query. Instead, it would be better to select each required Fields by name.

You could also create Aliases for the Queries to use in case any of the compatible Fields has a different Name. That way, the result will be a set that has the Field names that you want.

It might be best to start with a single Query that gets a RANDOM set of 10 questions of a particular type. Once you have that working it should be a simple process to create a Union Query to process all 5 types.

-- Rookie

 

[Cronk]

Ahmangbay30

If you want randomness, you need to have use the random number generator in VBA. Insert a new module and add the following function.

Function RndOrderGenerator(ID)
RndOrderGenerator = Rnd(ID)
End Function

If you want 5 random records from each of 10 subjects, the following will work.
(SELECT TOP 5 * FROM tblQs Where CatID=1 order by RndOrderGenerator([ID#])) union (SELECT TOP 5 * FROM tblQs Where CatID=2 order by RndOrderGenerator([ID#])) union......

Warning: It might be quite slow in running. There's quite a bit of processing.