How to return random records in a table based on query results?-CodePudding

I have a table named Values with thousands of records (sample is a lot less). The data itself is unique except that some records share the same ID.

So I need a query to return random records depending on the total count of TestQ1. For example, the query has a total of 9 records for ID 120 so there should be 3 random records each time this query is run because that's what the Test table indicates (this table changes the "Test" numbers weekly).

Pictured:

The "Values" table is the raw data. The "TestQ1" query has a total count for that specific "ID" and to the right, is the number of records that should be returned.

This is as far as I've been able to get:

SELECT TOP 5 Values.ID, Values.Test, Values.State, Rnd([Values]![ID]) AS [Random No], * FROM [Values] ORDER BY Rnd([Values]![ID]);

CodePudding user response：

TOP N cannot be dynamic in a query object.

Can calculate a group sequence ID and use that in criteria to return a number of records for each ID group. That requires a unique identifier field which can be provided with an autonumber. Set it as random so the selection can be different as records are added to dataset.

Consider SQL:

SELECT Values.PKID, Values.ID, Values.Test, Values.State, DCount("*","Values","ID=" & [Values].[ID] & " AND PKID<" & [PKID]) 1 AS GrpSeq
FROM TestQ2 INNER JOIN [Values] ON TestQ2.ID = Values.ID
WHERE (((DCount("*","Values","ID=" & [Values].[ID] & " AND PKID<" & [PKID]) 1)<=[TestQ2].[Test]));

However, if you truly need to return randomized set of records with each query run, I expect VBA will be needed. Either to populate a field in Values table with a randomized sequence number for each ID group or to populate a temp table with randomized dataset. Consider VBA to populate field:

Sub RandomSeq()
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset, x As Integer
Set rs1 = CurrentDb.OpenRecordset("SELECT DISTINCT ID FROM [Values]")
Do While Not rs1.EOF
    Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM [Values] WHERE ID=" & rs1!ID & " ORDER BY Rnd([ID]);")
    Do While Not rs2.EOF
        rs2.Edit
        x = x   1
        rs2!GrpSeq = x
        rs2.Update
        rs2.MoveNext
    Loop
    rs2.Close
    x = 0
    rs1.MoveNext
Loop
End Sub

Now run query:

SELECT Values.ID, Values.Test, Values.State, Values.GrpSeq
FROM TestQ2 INNER JOIN [Values] ON TestQ2.ID = Values.ID
WHERE (((Values.GrpSeq)<=[TestQ2].[Test]));

Be aware this will not be practical in a split database with multiple simultaneous users. Then a temp table (located in frontend) approach may be needed.