Randomize records then select TOP (x)

[banem2]

Task is simple, but it appear as very though to complete.

Table:

Table1, IDRecord (may be AutoNumber)

record values:
1, 2, 3, 4, 5

This numbers needs to be randomized Rnd(), then extract TOP(n) where
"n" is number on form (Form1!Field1).

To select TOP(n) I am using workaround like this:

SELECT Table1.IDRecord, DCount("IDRecord","Table1","IDRecord <= " &
[IDRecord]) AS TopNum, Table1.*
FROM Table1
WHERE (DCount("IDRecord","Table1","IDRecord <= " &
[IDRecord])<=Val(Forms!Form1!Field1[]));

All works fine until I put RND(RecordID) command and sort by this
number.

If I first randomize records and use second query, it won't create
record number in query in order they appear on screen.

Any solution to randomize records, to create record numbers in query
and filter by TOP(n)?

Goal:

IDRecord, RecNo
1, 1
5, 2
3, 3
4, 4
2, 5

Filter on RecNo by form to have only first "n" records.

Thanks!

 

[Douglas J. Steele]

See http://www.mvps.org/access/queries/qry0011.htm at "The Access Web"

 

[banem2]

Seehttp://www.mvps.org/access/queries/qry0011.htmat "The Access Web"

Sorry, but this is not what I am looking for. I know to randomize
records, but I need to return TOP(n). "n" is variable value on form as
noted.

 

[banem2]

Sorry, but this is not what I am looking for. I know to randomize
records, but I need to return TOP(n). "n" is variable value on form as
noted.

To be more precise: these 2 actions cannot work together.

- If I make a query to return TOP(n) records, then they are not
randomized
- If I randomize records, then I cannot return TOP(n) records

Dirty workaround is to create temporary table and then use solution
with TOP(n), but I don't like this solution.

 

[Douglas J. Steele]

Sorry, I missed the fact that you wanted Top n to be parameterized.

As far as I know, it cannot be done in Access. You'd have to rewrite the SQL
for a stored query, and then run the stored query.

 

[banem2]

Sorry, I missed the fact that you wanted Top n to be parameterized.

As far as I know, it cannot be done in Access. You'd have to rewrite the SQL
for a stored query, and then run the stored query.

Yes, you are right... but how to do that?

 

[Douglas J. Steele]

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

If Len(Forms!Form1!Field1 & vbNullString) > 0 Then
strSQL = "Select Top " & Forms!Form1!Field1 & _
" MyTable.* From MyTable Where Randomizer() = 0 " & _
"Order By Rnd(IsNull(MyTable.Question) * 0 + 1)"
Set qdfCurr = CurrentDb.QueryDefs("MyRandomQuery")
qdfCurr.SQL = strSQL
End If

 

[banem2]

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

If Len(Forms!Form1!Field1 & vbNullString) > 0 Then
strSQL = "Select Top " & Forms!Form1!Field1 & _
" MyTable.* From MyTable Where Randomizer() = 0 " & _
"Order By Rnd(IsNull(MyTable.Question) * 0 + 1)"
Set qdfCurr = CurrentDb.QueryDefs("MyRandomQuery")
qdfCurr.SQL = strSQL
End If

Thanks, this will help!