Randomizing records retrieved from a query

[JenniferM]

Here's the basic idea of what I'm doing. I'm creating a database with a list of practice questions for a class. I've got a table with a list of questions. Each has a field QstID (primary key, numeric), QstQuestion (string, question being asked), and QstTag (string, the text tag provided by the user)... There are other fields as well, but they are irrelevant to what I want to do.

Let's say a user wants to do a quiz of all the questions they have tagged "Review." I would like to add these records with this value for QstTag to another table called TblTestBank. I know how to do this via constructing an SQL statement with INSERT INTO. Where I'm running into a problem is here:
I want to be able to add these questions to TblTestBank in a random order so that the questions are not presented the same each time the user takes the quiz. Is this something that can be achieved by adjusting the ORDER BY clause? Or is there some other mechanism of doing this that I have not considered?

[NeoPa]

Interesting question Jennifer.

I don't believe there is anything inbuilt in SQL that provides that functionality.

However, there are some clever little ideas out there and I think I have managed to get one of them to work (I don't know the name of your table so I've used [tblQuestion], and I've also assumed ten questions are required for the test.) :

SELECT   TOP 10 *
FROM     [tblQuestion]
ORDER BY Rnd(Timer()*[QstID])

No intermediate table is required for this.

[ADezii]

@JenniferM:
Here is a more complex Version of NeoPa's Logic which I use to generate Random Questions in a couple of Databases.

Dim strSQL As String
Dim qdf1 As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
Dim strTag As String
Dim intNumOfRecords As Integer
Dim intNumOfQuestions As Integer
 
'******************** Enter Your Own Values Here ********************
strTag = "Review"
Const conQUERY_NAME As String = "qryRandom"
intNumOfQuestions = 10
'*******************************************************************
 
intNumOfRecords = DCount("*", "tblQuestions", "[QstTag] = '" & strTag & "'")
Select Case intNumOfRecords
  Case 0
    MsgBox "No Records for the [" & strTag & "] Tag!"
      Exit Sub
  Case Is < intNumOfQuestions
    intNumOfQuestions = intNumOfRecords     'RESET
      MsgBox "There are only " & intNumOfQuestions & " questions that you can test " & _
             "on. The Number of Questions has been Reset"
  Case Else
    'We are OK, so just fall through
End Select
 
'If the Query already exists, then DELETE it
For Each qdf1 In CurrentDb.QueryDefs
  If qdf1.Name = conQUERY_NAME Then
    CurrentDb.QueryDefs.Delete conQUERY_NAME
      Exit For
  End If
Next
 
'If conNUM_OF_QUESTIONS is > the actual Number of Records in the Query, all Records
'in the Query will be returned
strSQL = "SELECT TOP " & intNumOfQuestions & " tblQuestions.QstID, tblQuestions.QstTag, " & _
         "tblQuestions.QstQuestion FROM tblQuestions WHERE tblQuestions.QstTag = '" & strTag & "' " & _
         "ORDER BY Rnd(Timer()*[QstID] );"
 
Set qdf2 = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL)
 
DoCmd.OpenQuery conQUERY_NAME, acViewNormal, acReadOnly
DoCmd.Maximize

[clvandyke]

Jennifer,

Not sure which version of SQL you are using but in SQL Server 2008 there is a function NEWID. It can be used in the ORDER BY clause:

SELECT *
FROM
TblTestBank 
WHERE
Tag = 'Review'
ORDER BY NEWID()
 

This returns a list in a different order each time it is run.

[NeoPa]

This is in the Access forum so the type of SQL used would generally be Jet SQL (unless a pass-thru is used). The NEWID() facility provided by T-SQL supports something which is handled quite differently in Access (so I'm afraid isn't available). Nice thinking though :-)