You are here:

Using MS Access/Qualified Random Selection

Advertisement


Question
Relationships
Relationships  
Random Query
Random Query  
QUESTION: Hi Scott-

Windows 7, Access 2010

I have a database I created that I need to return a random set of records from.  The tables are relational.

I have the following tables:
Competencies
 Elements
   Sections
     Examples

I created a query with an expression that numbered the records  and I was able to randomly select x number of records.  

The issue is:
Within the Elements, I need to RANDOMLY select x number of Sections within EACH element.  So with my current set up I get x number of sections but it does not include at least one from each Element.

i.e.
Element:  7.1, 7.2, 7.3, 7.4
Section: 7.1.1, 7.1.2, 7.1.3: 7.2.1, 7.2.2, 7.3.3 etc

My query is returning randomly but not from EACH element.  So I might have all from element 7.1, 1 from 7.2 and nothing from 7.3 or 7.4.

Is there a way to do this?

I have googled everything I can think of and I'm not getting anywhere.

Any help would be greatly appreciated!

Thank you.

Lisa

ANSWER: Sorry for the delay in responding. The way I would do this is with multiple loops. So I would loop once through the Elements, randomly selecting a Section from each Element. Then I would do a global loop to select Section without regard to element. This way you get one for each Element, but then a random distribution.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi Scott-
I am not familiar with 'looping'.  Can you explain in more detail what I'd need to do?  Is it like a nested query of sorts?  Do I need vba code for that?  I am not familiar with it but can learn it if I need to.

Thank you.

Lisa

Answer
Yes, this would require VBA code. If you search in VBA help for Looping you will find examples of code.

About 75% of programming is covered in the concepts of If...Then...Else and looping. A loop allows you to move through a set of records and perform some action each time through.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.