I have a table with 2 fields : School Name; Student ID.
I want to randomly display a predetermined number of Student ID per school.
For example, 2 random Student IDs from School A; 3 random Student IDs from School B.
I would also like the query to know how many random records (Student Ids) to generate, based on another query or table (School A: 2 records; School B: 3 records; School C: 1 record, etc.)

Ultimately, those will be shown in a report. 1 page per School Name, with the randomly selected student IDs underneath the School Name.

So far, I've only been able to generate Student IDs randomly, but I can't tell the query to generate those for each school.

The two links below show an example of the original table, and how I would like the report to look like.

Table: https://drive.google.com/open?id=1luFuH9zha92qUhvx5kxpJinyiGYCn9fQ
Report: https://drive.google.com/open?id=1ZHwQkOqHy2xJ2mbbphCG-4xwuiOeLh72

Any help would be greatly appreciated!

Thank you!

1 Answers

Strawberryshrub On

If you want to generate the random student ID´s for each school use a recordset with a loop. Here is an idea how you could do it (Note: the code is not tested):

Dim rs as Recordset
'Select all school names
set rs = CurrentDb.OpenRecordset("SELECT SchoolName FROM SchoolTable") 


'Loop through all school names
Do While Not rs.EOF 

  'Execute your student ID randomly for the actual school name in the loop or do other stuff
  CurrentDb.Execute "YourQueryNameWhichYouAlreadyHave"