Select 10 Random Records For Each Record In Another Table

What is the key between tblUser and tblLocations?


Regards

What do you mean by Key?  The only relation between the two is this;  The users (records) listed in tblUsers each need 10 random locations from tblLocations to be posted to the tblAudits table.  I already have the random, query, and write setup for the process.  I am stuck on making this process cycle through each time for every user listed in tblUsers.  Also, each user listed in tblUsers is an Observer, as indicated in tblUsers.  This portion along with the Zones are being "hard coded" into the function as of right now because I can't figure out how to make it cycle through each user and each zone.
Each user requires 10 locations from only one zone per month each.
i.e.;
John Doe - is Assigned 10 random locations (records) from Zone1 from tblLocations
Minnie Mouse - is assigned 10 random locations (records) from Zone2 from tblLocations
...and so on and so on.
 

Got it. Try the code below

Public Function Select10()
 
    Dim RSUsers, RSLocations
    Dim varBookmark
    Dim TotalLocations As Long
    Dim SelectedLocation As Long
    Dim i As Integer
    
    Set RSUsers = CurrentDb.OpenRecordset("Select ID, First, Last from tblUsers where Observer = true")
    Set RSLocations = CurrentDb.OpenRecordset("Select Location from tblLocations")
    
    RSLocations.MoveLast
    TotalLocations = RSLocations.RecordCount
    
    While Not RSUsers.EOF
        For i = 1 To 10
            Randomize
            SelectedLocation = CLng((TotalLocations * Rnd) + 1)
            RSLocations.MoveFirst
            varBookmark = RSLocations.Bookmark
            RSLocations.Move SelectedLocation
            'Here you can also write in a table or do whatever you want. I just printed in the debug window.
            Debug.Print RSUsers!First, RSUsers!Last, "Location - ", RSLocations!Location
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Next i
        RSUsers.MoveNext
    Wend
    
End Function

Select allOpen in new window

That looks good for the 10 selections for each user, but they can only select 10 from a zone.  For example, John Doe would only be able to select 10 random records from Zone1 and Minnie Mouse would only be able to select 10 random records from Zone2.  The Zone assignment can be random but must not repeat between users; in other words, John Doe and Minnie Mouse can not both have selections from Zone1.

Just brainstorming here; Something like concept;

Dim sZoneNumber1 As String, Dim sZoneNumber2 As String
Dim sZoneNumber3 As String, Dim sZoneNumber4 As String
Dim sZoneNumber5 As String, Dim sZoneNumber6 As String
Dim sZoneNumber7 As String, Dim sZoneNumber8 As String
Dim sZoneNumber9 As String

...THEN randomly assign 1-9 to each String with no repeats
...THEN utilizing the already defined "i" loop, build that into the statement;
Set RSLocations = CurrentDb.OpenRecordset _
("Select Location, Zone _
WHERE tblLocations.Zone="Zone=" & Chr(34) & sZoneName1 & Chr(34) & " "
FROM tblLocations")

The following code does the random assigning of zones.

    Dim sZoneNumber(9) As String
    Dim r(10) As String
    Dim s As String
    Dim i As Integer
    Dim x As Integer
    
    s = "123456789"
    i = 0
    Do While Len(s) > 0
        x = CStr(Int(Rnd(10) * 10))
       If InStr(1, s, x) Then
            i = i + 1
            r(i) = x
           s = Replace(s, x, "")
            Debug.Print r(i)
        End If
    Loop
    
    sZoneNumber1 = r(1)
    sZoneNumber2 = r(2)
    sZoneNumber3 = r(3)
    sZoneNumber4 = r(4)
    sZoneNumber5 = r(5)
    sZoneNumber6 = r(6)
    sZoneNumber7 = r(7)
    sZoneNumber8 = r(8)
    sZoneNumber9 = r(9)

Select allOpen in new window

My fault. Try this code

Public Function Select10()
 
    Dim RSUsers, RSLocations
    Dim varBookmark
    Dim TotalLocations As Long
    Dim SelectedLocation As Long
    Dim i As Integer
    Dim j As Integer
    
    Set RSUsers = CurrentDb.OpenRecordset("Select ID, First, Last from tblUsers where Observer = true")
     
    While Not RSUsers.EOF
        For j = 1 To 9
            Set RSLocations = CurrentDb.OpenRecordset("Select Location from tblLocations where zone='Zone" & j & "'")
            RSLocations.MoveLast
            TotalLocations = RSLocations.RecordCount
            For i = 1 To 10
                Randomize
                SelectedLocation = CLng(((TotalLocations - 1) * Rnd) + 1)
                RSLocations.MoveFirst
                varBookmark = RSLocations.Bookmark
                RSLocations.Move SelectedLocation - 1
                'Here you can also write in a table or do whatever you want. I just printed in the debug window.
                Debug.Print RSUsers!First; RSUsers!Last, "Zone - "; j, "Location - "; RSLocations!Location
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            Next i
        Next j
        RSUsers.MoveNext
    Wend
    
End Function

Select allOpen in new window

That code gives each user 10 locations from each zone.  Each user can only have one zone and 10 locations total from that zone.  We're getting closer...  :P  I really appreciate your help!

I was reviewing your last statement "The Zone assignment can be random but must not repeat between users; in other words, John Doe and Minnie Mouse can not both have selections from Zone1.".

What is going to happen if we have more users than zones?


Regards

Possibly a msgbox stating that there are xx number of zones and only yy number of users...  Not sure yet, but as of right now that number matches; 9/9.

Ok. Try this code.

Public Function Select10()
 
    Dim RSUsers, RSLocations
    Dim varBookmark
    Dim TotalLocations As Long
    Dim SelectedLocation As Long
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim UsedZones(9) As Variant
    
    Set RSUsers = CurrentDb.OpenRecordset("Select ID, First, Last from tblUsers where Observer = true")
    j = 1
    While Not RSUsers.EOF
        Do
            Randomize
            k = CLng((9 * Rnd) + 1)
        Loop While InStr(1, vbNullChar & Join(UsedZones, vbNullChar) & vbNullChar, vbNullChar & k & vbNullChar) > 0
        UsedZones(j) = k
        j = j + 1
        Set RSLocations = CurrentDb.OpenRecordset("Select Location from tblLocations where zone='Zone" & k & "'")
        RSLocations.MoveLast
        TotalLocations = RSLocations.RecordCount
        For i = 1 To 10
            Randomize
            SelectedLocation = CLng(((TotalLocations - 1) * Rnd) + 1)
            RSLocations.MoveFirst
            varBookmark = RSLocations.Bookmark
            RSLocations.Move SelectedLocation - 1
            'Here you can also write in a table or do whatever you want. I just printed in the debug window.
            Debug.Print RSUsers!First; RSUsers!Last, "Zone - "; k, "Location - "; RSLocations!Location
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Next i
        RSUsers.MoveNext
    Wend
End Function

Select allOpen in new window

I ran the function three times in a row, and not all zones are being used each time.. sometimes 8 and sometimes 9.

Is there a way to fix the random so repeats (of the locations) are not so common?  I see repeats if I run it like three times.

Well... it is random, if you change not to repeat, it will not be random.

Anyway you can give a new seed for the randomize function.

Just Replace

            Randomize

by

            Randomize (CDbl(Now))

I may need to take another look at the way it picks them, random vs "pick random - write to temp list - compare for repeats with past picks - pick more if needed" type of concept, but this is a good start... Thank you!  Great work!