Random Sorting Query Access

Random sorting query Access

You just need to "salt" your random generator:

Rs.Source = "SELECT pk FROM TABLE ORDER BY Rnd(-Timer() * [pk]) Asc"

random sorting each time query is run

Normally, your SQL would contain a random function of some sort (it looks like Access has the rnd() function for this).

So you could use:

select num, weight, rnd(num)
from tbl
order by weight desc, 3

This will let you see r for testing purposes. In a real query, you might just want to use something like:

select num, weight
from tbl
order by weight desc, rnd(num)

From this page:

When value is greater than 0, Rnd() returns the next random number.

When value is less than 0, Rnd() returns the same random number, based on value. If value occurs only once, you won’t notice this behavior. Access also resets seed, which means the sequence starts all over again.

When value is equal to 0, Rnd() returns the most recently generated random number

Update 1: I'm unsure as to whether rnd() is executed once in the following queries or once per row - the docs aren't clear. Comments seem to indicate the same results are being received for all rows which indicates it may be the latter. It may be that changing it to rnd(num) or rnd(abs(num)+1) will fix that problem. I'll have to check when I get to a box with Access installed.

Update 2: I've now tested this in Access 2007 and it does indeed give the same random value for every row when you use rnd(1). It does give a different value for rnd(num) each time you run the query and the individual rows get different values. So the query you need is:

select num, weight from tbl order by weight desc, rnd(num);

If you create a table with two Number fields and then run that query over it, you'll see that continual refreshing (with F5) will swap around the 2, 7 and 4 rows at random but leave the 1 and 6 rows in the same place since the weights of the first three are all 13 and the weights of the last two are 12 and 9 respectively.

I've updated the queries above to match this new information.

How to get random record from MS Access database

The following will get a random questionID from your table

MySQL

SELECT questionID FROM questions ORDER BY RAND() LIMIT 1

MS Access

SELECT top 1 questionID from questions ORDER BY rnd(questionID)

Query for retrieving random records from MS Access

Assuming that in a table MyTable you have a primary key ID field in a that is an autoincrement integer, you can do something like this to retrieve, say 10 random records from MyTable:

SELECT Top 10 *
FROM   (SELECT *,
           Rnd(ID) AS RandomValue
        FROM   MyTable)
ORDER  BY RandomValue 

Edit:
Found another similar answer: How to get random record from MS Access database

How to make Access query to search for multiple keywords in random order

What you can do is to build up the SQL depending on whether the user has entered anything into each text box. Something like:

Private Sub cmdSearch_Click()
    On Error GoTo E_Handle
    Dim strSQL As String
    If Len(Me!txtSearch1) > 0 Then
        strSQL = strSQL & " AND ((keyword1 LIKE '*" & Me!txtSearch1 & "*') OR (keyword2 LIKE '*" & Me!txtSearch1 & "*') OR (keyword3 LIKE '*" & Me!txtSearch1 & "*')) "
    End If
    If Len(Me!txtSearch2) > 0 Then
        strSQL = strSQL & " AND ((keyword1 LIKE '*" & Me!txtSearch2 & "*') OR (keyword2 LIKE '*" & Me!txtSearch2 & "*') OR (keyword3 LIKE '*" & Me!txtSearch2 & "*')) "
    End If
    If Len(Me!txtSearch3) > 0 Then
        strSQL = strSQL & " AND ((keyword1 LIKE '*" & Me!txtSearch3 & "*') OR (keyword2 LIKE '*" & Me!txtSearch3 & "*') OR (keyword3 LIKE '*" & Me!txtSearch3 & "*')) "
    End If
    If Left(strSQL, 4) = " AND" Then strSQL = Mid(strSQL, 5)
    If Len(strSQL) > 0 Then
        strSQL = "SELECT hyperlink " _
            & " FROM tblKeyword " _
            & " WHERE " & strSQL _
            & " ORDER BY Hyperlink ASC;"
        Me!lstSearch.RowSource = strSQL
    End If
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "frmKeyword!cmdSearch_Click", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

In this example, I'm using the SQL to populate a list box.

Alternatively, if you wanted to use just one text box, with keywords separated by commas, then you can use a similar piece of code, but use Split to put the values into an array and then loop the array to build the SQL string:

Private Sub cmdAllSearch_Click()
    On Error GoTo E_Handle
    Dim strSQL As String
    Dim aData() As String
    Dim lngLoop1 As Long
    aData = Split(Me!txtAllSearch, ",")
    For lngLoop1 = LBound(aData) To UBound(aData)
        strSQL = strSQL & " AND ((keyword1 LIKE '*" & Trim(aData(lngLoop1)) & "*') OR (keyword2 LIKE '*" & Trim(aData(lngLoop1)) & "*') OR (keyword3 LIKE '*" & Trim(aData(lngLoop1)) & "*')) "
    Next lngLoop1
    If Left(strSQL, 4) = " AND" Then strSQL = Mid(strSQL, 5)
    If Len(strSQL) > 0 Then
        strSQL = "SELECT hyperlink " _
            & " FROM tblKeyword " _
            & " WHERE " & strSQL _
            & " ORDER BY Hyperlink ASC;"
        Me!lstSearch.RowSource = strSQL
    End If
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "frmKeyword!cmdAllSearch_Click", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Regards,

---

Related Topics