Random row selection without selecting twice

[timosilver]

Hello,

I have below statement that select row from my table at random.

 sql = "SELECT TOP 1 * FROM Question1to5table ORDER BY Rnd(-10000000*TimeValue(Now())*[id])"

how do make the row selection unique.



:::: maybe mark a selected row on the dataset, if possible how do i do that ::::

Thanks for help and idea.

[John_G]

I think you would need a field in your table that indicated a record had been selected. Then you would have to update the record selected by your SQL, and add a where clause to the SQL to select only from unused records, for example:


sql = "SELECT TOP 1 * FROM Question1to5table WHERE NOT Selected ORDER BY Rnd(-10000000*TimeValue(Now())*[id])"John

[timosilver]

thanks for your reply.. now i have added a 'Selected' field on my table.
how do i update/mark the selected field?
secondly i don't understand the 'John' at the end of the sql statement.

[orange]

See http://www.techrepublic.com/blog/how...oft-access/149 for more info on selecting random records.

If you have to select say 20 random records, why not do it all at once? If you must do one at a time, then, in pure random selection, selecting the same record more than once may make sense.
If you want one at a time, and no repeats, then carry on by ignoring records that have already been selected.

[John_G]

Hi -

The "John" is my signature, not part of the statement - somewhere a line break got missed. Sorry for that bit of confusion.

You can use two methods to mark the selected record, depending on how you use that SQL statement. If you make a recordset with it, just update the record. Assume the recordset is called rst:

rst.edit
rst!selected = True
rst.update

Or, you can use an Update SQL statement:

SQL = "Update Question1to5table set Selected = True where ID = " & record_ID
currentdb.Execute SQL, dbfailonerror

where Record_ID contains the record number (ID)

HTH

John

[timosilver]

ok. thanks alot guys. really helpful

[timosilver]

i am using dataset is there a way to update selected field using the ds.

Dim con As New OleDb.OleDbConnection Dim dbProvider As String
Dim dbSource As String
Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter
Dim sql As String






dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
dbSource = "Data Source = C:\Users\Timosoft\Documents\wwtbamDB.mdb"
con.ConnectionString = dbProvider & dbSource


con.Open()


sql = "SELECT TOP 1 * FROM Question1to5table ORDER BY Rnd(-10000000*TimeValue(Now())*[id])"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "QuestionsandAnswers")

con.Close()

[timosilver]

using data set can i do something like this
to update the selected field at item 6 with 'True'

ds.Tables("QuestionsandAnswers").Rows(0).Item(6) = "True"

and then use the below sql

sql = "SELECT TOP 1 * FROM Question1to5table WHERE NOT Selected ORDER BY Rnd(-10000000*TimeValue(Now())*[id])"