Query Random records based on criteria

[rpeare]

Some of your '2 - easy' questions do not have a category and/or subcategory (3025, 2938, 2621, 2321, etc)


Some of your '4 - hard' questions do not have a category and/or subcategory (3839, 3322, 3204, 2599, etc)

[Grant Shea]

If possible have a form that allows the user to pick the 10 easy subcategories and 10 hard subcategories

[Grant Shea]

I am aware of that... Still working on categorizing these. I developed the database after 3000+ questions so still going through them.

[orange]

OK I saw the spreadsheet. I thought you wanted to do this in Access(database)??

What does column H represent? Seems to be at least 2 concepts involved.
In database tables represent a single "thing". There are rules--Normalization, Primary/Foreign keys....
In spreadsheet there are no such rules.

Some Categories, like Current Events, may need some editing/modifications to make them more "reusable/recyclable".
For example:

61 Current Events
What is the name of the openly gay player drafted into the NFL this week?

The wording would not allow that question to be repeated in future. Might be better as
What is the name of the openly gay player drafted into the NFL in 2014?

But that is a side issue. If you are going to use a relational database as a database, then some design adjustments are needed.
I see different "things" in your spreadsheet:

Question
Answer
ApprovedQuestionAnswer
Usage/Round/Event
Participant
ParticipantRoundResult

Here is a draft model that may be of some use:
-I did not relate Category and/or subcategory to the question/answer combo since not all of you questions have subcategories
-You could select the same questions and answers for Rounds where only the sequence numbers were different.
-You could add new questions and reuse a specific answer, or create a new answer to a specific question (perhaps based on another Category/subcat??)
-A Participant in a Round would/could have his/her Responses recorded for reference (if/when tested again)

[Grant Shea]

@Orange, I am sorry you seem to be speaking another language... I am a novice user here.

The excel spreadsheet was only for the data as @rpeare requested. I am doing this in access.

I also dont think you quite understand what I am trying to do. Furthermore, the data question wording and subcategories are still being updated.

Below is more data about what each field is for and a summary of what "Quizzo" is:

Quizzo is a 4 round trivia game. Round 1 is a mixed difficulty current events round. Round 2 is a general knowledge Easy difficulty round. Round 3 is a Theme Round where all the questions fit a theme. Round 4 is is a general knowledge Hard difficulty round.

In the database i have the following fields
Id Number - Primary Key - Auto Number
Category - The category in which the question falls
Subcategory - The subcategory in which the question falls
Question - The Question
Answer - The Answer
Date Used - The original date the question was used
Invalid - True/False field (if a question is poorly worded or not accurate after being used it is marked invalid)
Round - The round in which the question is used limited to the choices (1 - Current Events, 2 - Easy, 3 - Theme, 4 - Hard)
Question Order - The order in which the questions are asked (some theme rounds require the questions asked in a specific order)
Field1 - Attachments
Notes - Additional notes about the question including the Name of the theme or other information

[rpeare]

Example for selecting a random set of 10 questions from the easy category
Selection of 10 random categories
selection of 1 random question within each category

GrantShea.zip

you can modify this to choose your categories if you want to as well with something like a list box, then cycle through the categories from the list box to choose your questions.

The remaining ones are all virtually the same idea so you can run with this.

[rpeare]

orange is giving you a more normalized structure, similar to what I suggested in a previous post, your structure is NOT normalized and is very limited, to have a truly robust question set you would want to be able to categories a question in multiple ways, orange's model does not allow for categorization of questions but it does address something I hadn't thought of in that a question may have multiple possible answers and vice versa, orange is addressing that with the junction table approvedquesans. You said you had no control over the data structure so what's what I went with, if you DO have control of the data structure you likely want to consider how flexible you want this thing to be.

[Grant Shea]

Thanks rpeare - the "select easy questions" button is giving an error that i cant figure out.

[rpeare]

you will have to be more specific, it runs fine on my machine

[Grant Shea]

Run-time error '3086':
Could not delete from specified tables and the debug function points to this line of code

 CurrentDb.Execute ("DELETE * FROM tblSelectedQuestions")

[Grant Shea]

never mind it is because it wasnt saved on my machine

[Grant Shea]

One last question. I have built a form with combo boxes that select the subcategories for each question. I have a table TempEasyCategories that is identical to your tblCategoriesTemp. On the form i have a button "update" and i want the table to be updated with the selected subcategories when i click the button i wrote this code

 Private Sub Command6_Click()CurrentDb.Execute "UPDATE TempEasyCategories " _
        & "SET Subcategory = '[forms]![Category Select]![Combo2]' " _
        & "WHERE 'Question Number' = 1;"
End Sub

But this is not working. Not sure why

[rpeare]

you don't ahve to have a combo box for each subcategory you want, you can have a list box that lists all the subcategories available and use the 'multi select' value set to true and select the ones you want. Easier than combo boxes which you'd have to cascade to avoid having duplicate category picks.

[Grant Shea]

Sorry i typed all of that wrong:

I have 10 combo boxes (one for each question) the user will select the 10 subcategories they want. When they select the subcategory it updates the table with their selection. The problem is that the update isn't working. Not sure why.
The table is called TempEasyCategories and has 2 fields "Question Number" pre filled with 1-10 and "Subcategory" the field where i want the combo box selection to go.

 Private Sub Combo2_Change()CurrentDb.Execute "UPDATE TempEasyCategories " _
        & "SET Subcategory = '[forms]![Category Select]![Combo2]' " _
        & "WHERE 'Question Number' = 1;"
 End Sub

[rpeare]

CurrentDb.Execute "UPDATE TempEasyCategories SET Subcategory = '" & [Combo2] & "' WHERE 'Question Number' = 1;"