Random records put into a table (1 Viewer)

Db-why-not

Registered User.
Local time
Today, 10:07
Joined
Sep 17, 2019
Messages
159
Hello,

I am assigning random records for auditing to a team of people who updated the records. I have the file that shows what records everyone updated and who updated the record. I created a query (Generate_random) to assign a randomID number to each record that was updated.
SELECT FixedRecords.ien1, FixedRecords.ien2, FixedRecords.Name, Rnd([IEN1]) AS RandomID
FROM FixedRecords;

I am trying to pull random records from this query to make a table so it will automatically give me table with 10 random records that each person updated. Then I can assign 10 records to each team member to audit. There is a name Field it shows the names of people who updated the record, then there is a RandomID field. I am trying to write a query that will pull up the top 10 records for each Name. I want it to use the RandomID field to find the top 10 records. I need 10 records that were completed by each person in the name field.

Example:
Name: Sally, RandomID: 24545
Name: Sally, RandomID: 24445
Name: Sally, RandomID: 23545
"" Top 10 records with sally
Name: Mary, RandomID: 54545
Name: Mary, RandomID: 44545
"" top 10 records with Mary

Name: roger, RandomID: 14545
Name: roger, RandomID: 12545
"" Top 10 records with roger

How would I write this second query to pull this data. I was going to have it make a table because once the table is made I dont want the RandomID to keep changing anymore.
When I use this SQL code it only gives me 10 records. I want 10 records from each unique Name Field

SELECT TOP 10 Generate_random.RandomID, Generate_random.Name
FROM Generate_random
GROUP BY Generate_random.RandomID, Generate_random.Name
ORDER BY Generate_random.RandomID;

How would I rewrite this SQL to give me what I need? Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:07
Joined
Oct 29, 2018
Messages
21,358
Hi. We've had previous discussions on pulling random records here. Maybe try the related threads links below or do a search. I'll see if I find one for you.
Sent from phone...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:07
Joined
May 21, 2018
Messages
8,463
I can never figure out this issue; however, your query is not correct. That is not a Select Top N by Group query. It should look something more like
Code:
SELECT qryRand.ID, qryRand.Group
FROM qryRand
WHERE qryRand.ID In
 (Select Top 2 A.ID from qryRand as A where A.Group = qryRand.group order by rnd([id]))
ORDER BY qryRand.Group;

This query works except for the Rnd part. If you take out the rnd you will get 2 per group, with the rnd you get all kinds of results. You can hit the refresh and see a different amount of records returned.
 

Attachments

  • TopRandom.accdb
    768 KB · Views: 293

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:07
Joined
May 21, 2018
Messages
8,463
You may get some ideas from this Random records per group.
In that case they kind of cheated since they wrote the random values to the table first. However, I wonder if this can be done in straight SQL. If not this is a good workaround.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,169
using vba to pick random record. does not need additional field.
qry_Final is your final query. as much as possible use qry_Final (datasheet form)
so that the function will reset each time you open the form.
tested on small dataset. may or maynot work on large dataset.
 

Attachments

  • TOP 5 IN GROUP.zip
    35.9 KB · Views: 300

Db-why-not

Registered User.
Local time
Today, 10:07
Joined
Sep 17, 2019
Messages
159
using vba to pick random record. does not need additional field.
qry_Final is your final query. as much as possible use qry_Final (datasheet form)
so that the function will reset each time you open the form.
tested on small dataset. may or maynot work on large dataset.
I downloaded the file you posted and tried to do what you did with the VBA code and the query, and the form but I am getting a runtime error 457. "This key is already associated with an element of this collection" I have attached screenshot of the debug error. It highlights this section of the vba code : d.Add !Name & "|" & !IEN1, lngRand
I have attached full screenshot. In my table my EIN1 is not an autonumber but it is still a long integer, just wondering if that might be causing any errors or problems. I also attached db, it only has fake data. My table name I changed to FixedData, the table has an extra field EIN2. I don't think that should cause the problem though.
 

Attachments

  • run time error 457.zip
    699.7 KB · Views: 156
  • debug error.PNG
    debug error.PNG
    589.6 KB · Views: 283

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,169
it could have been caused by 1 "blank" name.
i exclude it from the list.
 

Attachments

  • run time error 457.zip
    693.3 KB · Views: 298

Users who are viewing this thread

Top Bottom