Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Grant Shea is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    30

    Query Random records based on criteria

    I have a table that has thousands of trivia questions, answers, categories, subcategories and difficulty level. I would like to build a report that contains 10 "easy" questions and 10 "hard" questions without repeating any subcategory. Is there any possible way to do this?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Grant Shea is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    30
    How would i limit it so that it doesn't repeat a subcategory though?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What are your table design(s)? Fields, relationships???
    How do category, subcategory, easy, hard relate/get identified?

  5. #5
    Grant Shea is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    30
    Its pretty simple:
    Round has 4 values: "1 - Current Events", "2 - Easy", "3 - Theme", "4 - Hard"
    Click image for larger version. 

Name:	Tables_Relationships.JPG 
Views:	16 
Size:	33.0 KB 
ID:	25710

    Ultimately I would like to create a report that has 10 random easy questions, 10 theme questions, and 10 hard questions. The challenge with the Theme rounds is that they must all be from the same theme this could be accomplished by selecting all 10 "3 - Theme" questions from a date that already exists in the table questions (though I am not sure how to do that).

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It sounds like you want to insert a random Date into the criteria of a query.

    Here is one example of a function you could call from a query. However, as mentioned by Orange, you need to Seed the Rnd() function. IIRC, if you run the Rnd() function really quickly, as in an iteration that is processed in microseconds, you will not get truly random umbers. This is because the Random function use the system clock.
    https://support.microsoft.com/en-us/kb/304085
    Also, you need to determine if your Date field is storing a time value. If it is, you will need to use the BETWEEN operator in your query to include the morning hours and the evening hours.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Just out of curiosity why are you using a multi value field and what are the contents of that field for?

    And I think you may want to invest some time in rethinking your structure.

    For instance your 'round' is a mix of difficulty level and categories as far as I can tell

    For each question you want to be able to identify

    1. Difficulty Level (Easy, Medium (maybe), Hard)
    2. Theme (Geography, Pop Culture, Current Events, World History, etc.) Where a particular question could fit multiple themes
    3. Category (Countries, World Capitals, Rivers, Mountains, etc.) Where a particular question could fit multiple categories
    4. Sub Category (European, North American, Asian, African, etc.) Where a particular question could fit multiple sub categories

    Then when you run your code it should be much easier to pick out your possibles than your current structure (though yours is close)

    In terms of a table structure you'd need a table to hold:

    Question Theme - A Junction table between your question set and the theme(s) of each question
    Question Category - A Junction table between your question and the category(ies) of each question
    Question Sub Category - A junction table between your question and the sub cateogry(ies) of each question

    So for instance let's say you had this in your QUESTIONS table

    Code:
    tblDifficulty
    D_ID  D_Text
    1     Easy
    2     Medium
    3     Hard
    
    Q_ID  D_ID  Q_Text  
    1     2     What US state borders only one other?
    You would set the difficulty of this question on the QUESTIONS table
    in your QUESTIONTHEME table you might have

    Code:
    tblTheme
    T_ID  T_Text
    1     Geography
    2     US States
    3     I'm the Only One
    4     European
    5     African
    
    tblQuestionTheme
    QT_ID  Q_ID  T_ID
    1      1     1
    2      1     3
    3      1     2
    Where the question may fit geography, US States, I'm the only one themes.

    A similar arrangement for both the category and subcategory. This would give you the most flexibility in your question gathering.

    Lastly, are you going to put a moratorium on asking a question for x amount of days, months, weeks (whatever) after you use it to prevent the same question from being used on consecutive sessions?

  8. #8
    Grant Shea is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    30
    Thanks, This is close but i can not re-structure the database. The theme round is a category of it's own. It is something like a picture round or the questions all fit a theme like Olympics. The easy and hard rounds are general knowledge questions.

    The multi-value field is attachments if needed for the Theme Round such as a hand out with pictures on it.

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Did you solve this? If your 'theme' questions are further categorized into subcategories it really should not be hard to pick 10 random questions as long as you have well defined selection criteria.

  10. #10
    Grant Shea is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    30
    No i haven't solved this and i'm still hopelessly lost. What i really want is 10 Random Easy Round Questions, 10 Random Hard Round Questions where no subcategory repeats and 10 Theme questions(they all have to be from the same original date).

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    can you supply a sample database with a portion of your questions

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    When you get Random records, I don't think you can say with "no subcategory repeated".
    You could constrain the result, but I think you are then redefining random to suit your purpose.

    I think part of your still hopelessly lost feeling is because of your table structure and relationships as rpeare mentioned.

  13. #13
    Grant Shea is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    30
    See the data in excel format.
    Attached Files Attached Files

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok. You are definitely going to have problems with this database if this is your source data unless you have data entry that limits your category, subcategory and round to a defined list, otherwise access will show 'architecture' and 'architecure' as two different categories. That being said.

    I am assuming these are your selection criteria
    You want 10 questions from the '2 - Easy' Round from 10 different subcategories (literature, food, theory, architecture, etc.) and you want a random question within the subcategory chosen
    You want 10 questions from the '4 - hard' round from 10 different subcategories and you want a random question within the subcategory chosen
    You want 10 questions from the '3- Theme' round, but all the questions must be from the same category as noted in the NOTES field but you want to randomly select which theme is chosen

    Is this correctly stated?

  15. #15
    Grant Shea is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    30
    Yes, that is correct. but for the theme round you can use the "Date Used" field instead of notes if that makes it easier.

    Also I do have data entry that limits the category and subcategory to a defined list

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-31-2012, 01:56 PM
  2. Replies: 8
    Last Post: 03-19-2012, 08:50 PM
  3. Delete Duplicate Records Based on Criteria
    By chadd in forum Queries
    Replies: 2
    Last Post: 02-07-2012, 04:24 PM
  4. Select Random Records Based on Conditions
    By access123 in forum Queries
    Replies: 1
    Last Post: 10-27-2010, 10:25 AM
  5. Counting records based on specified criteria
    By GrnISSO in forum Queries
    Replies: 2
    Last Post: 10-04-2007, 03:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums