Trying to Insert Random Selections into a Random History Table but getting incorrect data

[Glenley]

I have a query that generates a top percentile random order followed by an Append query to insert those selections into a History Table. my problem is that because I'm returning a random order, the data that's appended to the history table does not match the data from my random query. The code is as follows:

Private Sub btnTest2_Click()

    Dim StrSql As String
    Dim qDef As QueryDef
    Dim Cust As Long
    Dim AppQ As String
    
    Randomize
    StrSql = "SELECT TOP " & Nz(Me.PercentCombo, 100) & " PERCENT DonorT.DonorID, DonorT.DonorFirstName, DonorT.DonorLastName, DonorT.DonorGroupID, DonorT.DonorIsActive, Rnd([DonorID]*Timer()*-1) AS X, CustomerT.OrganizationID"
    StrSql = StrSql & " FROM OfficerEmployerT RIGHT JOIN (CustomerT RIGHT JOIN (GroupT INNER JOIN DonorT ON GroupT.GroupID = DonorT.DonorGroupID) ON CustomerT.OrganizationID = DonorT.OrganizationID) " _
            & "ON OfficerEmployerT.OfficerID = DonorT.OfficerID"
    StrSql = StrSql & " WHERE CustomerT.OrganizationID=[Forms]![RandomF]![CustomerCombo] AND DonorT.DonorGroupID=[Forms]![RandomF]![GroupCombo] AND DonorIsActive=True"
    StrSql = StrSql & " ORDER BY Rnd([DonorID]*Timer()*-1);"
        
    Set qDef = CurrentDb.QueryDefs("RandomQ")
    
    qDef.SQL = StrSql
    qDef.Close
    Set qDef = Nothing
    Randomize
    DoCmd.OpenQuery "RandomQ"
    
    AppQ = "INSERT INTO RandomT(DonorID, DonorFirstName, DonorLastName, GroupID, OrganizationID) " & _
            "SELECT DonorID, DonorFirstName, DonorLastName, DonorGroupID, OrganizationID " & _
            "FROM RandomQ"
    
    If MsgBox("Are these the random selections that will be tested?" & vbNewLine & "If not, select NO and run again", vbYesNo, "Official Random Selections") = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL AppQ
        DoCmd.SetWarnings True
    Else
        DoCmd.Close acQuery, "RandomQ"
    End If


End Sub

Any help would be greatly appreciated. Thanks

Also, some help on how to better randomize these selections would be awesome

[June7]

Maybe don't save RandomQ query object.

"FROM (" & StrSQL & ") AS RandomQ"

Remove semi-colon at end of StrSQL build.

[Glenley]

Thanks June7. I tried that but still getting the same results. The appended records are completely random and not the records retrieved from the StrSQL build. Dealing with randomization has been challenging to say the least

[June7]

Why should they match? My suggestion is to eliminate RandomQ object.

Save random set directly into history table (RandomT ?). Use the history records as source for testing. Filter on GroupID, assuming it is a unique value for each test dataset.

[Glenley]

I think I understand what you are saying. The RandomQ object is not what is causing me trouble though. We do drug testing for companies and have to make random selections monthly and quarterly for each group within the company form a Donor Table (DonorT). The groups are DOT and nonDOT certified employees. We run the groups seperately because the requirements for each group are different. The RandomT is only there for record keeping purposes so we can see a history of which employees have been selected in the past. The trouble is after I run RandomQ and get my selections, When I go to append those records into the history table (RandomT), it's another random selection that does not match the records that were selected in the RandomQ. So it's essentially a false history. For example, RandomQ might return John D, and Sally F but when I append those records into RandomT it might add Joe P and Chris T instead of John and Sally. it seems like it is randomizing the records again when i run the append query.

[June7]

Yes, that will happen because you are executing the randomization every time RandomQ is referenced. You need to commit the random selection to a table, not rely on the query for a persistent dataset. If not history (RandomT ?) then a 'temp' table (table is permanent but data is not). Then maybe save the temp dataset to final history table once testing is completed.

[Glenley]

Okay, I see what you're saying. That makes sense now. I'll have to play around with that idea and see what I can come up with. I wonder if i could use a recordset to randomly select records???...hmm, I have some more work to do on this. Thanks for the help

[June7]

Yes, a recordset can be opened with random query. I just helped someone with that (https://www.accessforums.net/showthread.php?t=85707). But again, need to save to table if you want result to persist. So what is benefit of recordset in your case?

[Glenley]

I'm just brainstorming out loud mostly. I will check out that thread though

[orange]

This older thread may help with brainstorming Random selections.

[Glenley]

Yes, I saw that. Thank you