Results 1 to 6 of 6
  1. #1
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100

    How to return random records based on a table?

    I have a table (normally about 15-20k records) that is a quarter worth of data for employees travels. I am in the process of starting an audit (1st time) of their expense reports.



    I would like to randomly select number of records in the 'Data' table but based on the 'Purpose' table 'Rec_to_Return' field BUT the County could only be returned once per 'Purpose'

    Example, return 2 random rows for 'Pick-up' for 2 different counties or 2 'Delivery' rows for 2 different counties. Its okay if the county is repeated as long as the purpose is different.

    Date Employee ID County Purpose
    1/4/2021 4 Clatsop Pick-up
    1/8/2021 3 Curry Pick-up
    1/29/2021 1 Clatsop Delivery
    1/9/2021 5 Columbia Delivery


    I was wanting to use the link below but this doesn't take into the account the extra criteria of one county.

    https://www.accessforums.net/showthr...043#post485043
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Advise not to use space in naming convention, nor reserved words as names - Date is a reserved word.

    Why not using Autonumber as key field for Purpose and save into Data as foreign key instead of text? Same goes for County - save ID instead of County name.

    Will need a unique ID field in Data table. Add an Autonumber field.

    SELECT PKID, TripDate, County, Data.Purpose, DCount("*","Data","Purpose='" & Data.[Purpose] & "' AND County<>'" & [County] & "' AND PKID<" & [PKID])+1 AS GrpSeq
    FROM [Purpose] INNER JOIN [Data] ON [Purpose].Purpose = Data.Purpose
    WHERE (((DCount("*","Data","Purpose='" & Data.Purpose & "' AND County<>'" & [County] & "' AND PKID<" & [PKID])+1)<=[Purpose].[Rec_to_Return]));

    However, as already noted in other thread, same records return each time query runs. So need to incorporate VBA.

    Trying to incorporate the County restriction and pull dynamic N records has me stumped. But still looking at.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    I added the fields you suggested but I'm getting an error because of the PKID field.

    As far as the VbA, I think I will use the one you provided in the other post but tweak it a bit.
    Attached Files Attached Files

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As already noted, have to add a unique ID field to Data table - autonumber should serve. I called it PKID.

    This is what I came up with. Build a 'temp' table to save records for audit.

    Code:
    Sub RandomSeq()
    Dim db As DAO.Database, rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset, x As Integer, strP As String
    CurrentDb.Execute "DELETE FROM AuditRecs"
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Purpose")
    Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM [AuditRecs]")
    Set db = CurrentDb
    Do While Not rs.EOF
        Set rs1 = db.OpenRecordset("SELECT Data.*, Purpose.Rec_to_Return FROM Data INNER JOIN Purpose ON Data.Purpose = Purpose.Purpose " & _
                                        " WHERE Data.Purpose='" & rs!Purpose & "' ORDER BY Data.Purpose, Rnd([PKID])")
        x = 1
        Do While x <= rs1!Rec_to_Return And Not rs1.EOF
            rs2.AddNew
            If DCount("*", "AuditRecs", "Purpose='" & rs1!Purpose & "' AND County='" & rs1!County & "'") = 0 Then
                rs2!DataPKID = rs1!PKID
                rs2!Purpose = rs1!Purpose
                rs2!County = rs1!County
                x = x + 1
            End If
            rs2.Update
            rs1.MoveNext
        Loop
        rs1.Close
        rs.MoveNext
    Loop
    End Sub
    Alternatively, add a Yes/No field in Data table to 'flag' record as selected for audit and modify code. If only one user at a time will run this procedure, this would be satisfactory.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    It worked thank yo

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 12-08-2021, 11:21 PM
  2. Query Random records based on criteria
    By Grant Shea in forum Access
    Replies: 31
    Last Post: 09-09-2016, 03:10 PM
  3. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  4. Return Random Records
    By cbrsix in forum Programming
    Replies: 1
    Last Post: 01-11-2013, 06:13 PM
  5. Select Random Records Based on Conditions
    By access123 in forum Queries
    Replies: 1
    Last Post: 10-27-2010, 10:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums