Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    33

    How to return random records based on table field?

    Hello all - I have a "MasterQ" where I would like to return random records for each type of ID (120, 123, 456 & 789). The issue I'm encountering is that the number of records returned for each "ID" should be the number under the "Test_Num" field. I can't find any examples that would help me with this.

    When the query is run, I should get a total of 12 records (3 records for ID 120, 5 records for ID 123, 2 records for ID 456 and two records for ID 789).

    Any suggestions? Database2.zip

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,608
    Cross post https://stackoverflow.com/questions/...06350#69706350

    As explained in that thread, need a unique identifier field for that query - add autonumber field to Values table.

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


    However, this will not return random set of records each time query runs. For that, use the other method that employs VBA.
    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.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,865

  4. #4
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    33
    I get a "The specified field "Values.PKID" could refer to more than one table listed in the FROM clause of your SQL statement" error. Not sure why since I don't see it a second time.

  5. #5
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    33
    Thanks Orange, I will look into it and test it out.

  6. #6
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,498

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,143
    @MsAxess, that sort of behavior will result in fewer responses to your posts in all forums. I suggest you read up on this
    Cross Posting Message

    If that has been pointed out to you in those other threads I won't notice it, so sorry for the repeat reminder.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    33
    Apologies, I had no clue, honestly.

    I figured someone in a different area would know. It won't happen again.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,608
    The database you posted does not have the PKID field in Values table. So I would have expected different behavior than that error message.

    I cannot get the error. The queries and code I provided work for me.
    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.

  10. #10
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    33
    That's strange June7, I'm using the same sample I posted.

    Any chance you can post the DB and see if that works for me? I don't expect it to but at this point, I'll try anything.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,608
    Well, you might download the file from your post and see what's in it. I just downloaded it again. There is no autonumber field named PKID in Values table. I add it and the query works.
    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.

  12. #12
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    33
    Yeah I downloaded the file and tried that SQL but still get the error message. I will research and come back to post when I find something.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,608
    This time I did not add the PKID field and ran the query I suggest. Now I get the error message.

    Again, MUST ADD PKID AUTONUMBER FIELD TO VALUES TABLE for suggested query to work.

    Otherwise, suggested VBA approach will require adding a different field that can be updated. Code was provided for that.

    Another VBA solution would involve writing records to a temp table.
    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.

  14. #14
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    33
    First, I appreciate your patience, the query worked. Second, I now know why it's important that I don't cross post messages in other forums and I won't do it (first time) and finally, I'll be sure to login to other forums and update that the query works w/the solution.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,143
    Quote Originally Posted by MsAxes View Post
    Apologies, I had no clue, honestly.

    I figured someone in a different area would know. It won't happen again.
    I have to assume you didn't visit the link. If you did you would understand that cross posting is acceptable - if you do it properly. If a site doesn't allow you to post links because you're new there, then put the info in as commentary. Could even parse the link so that it can be used/figured out; e.g.

    w w w accessforumsDotNet/showthread.php?t=84671
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-25-2017, 08:50 AM
  2. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  3. Return Random Records
    By cbrsix in forum Programming
    Replies: 1
    Last Post: 01-11-2013, 06:13 PM
  4. Return field value based on the same table
    By snoopy2003 in forum Queries
    Replies: 2
    Last Post: 03-05-2011, 02:45 AM
  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