Solved How to get random record from MS Access database (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:18
Joined
Feb 28, 2001
Messages
26,999
No, my reference to Timer() is what Randomize does behind the scenes. RND doesn't look at anything in its argument except the condition of its argument as negative, zero, or positive.
 
Local time
Today, 23:48
Joined
Nov 22, 2019
Messages
31
Hi Team,

I am having hard time understanding the logic and not getting the answer right.

My query was how do i get 3% of the Raters_name from the table unique.
 

Attachments

  • QM.zip
    31.2 KB · Views: 121

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:18
Joined
May 21, 2018
Messages
8,463
Code:
SELECT TOP 3 PERCENT
 Queue1.Raters_name
FROM
 Queue1
GROUP BY
 Queue1.Raters_name
ORDER BY
 First(Rnd([id]));
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:18
Joined
May 21, 2018
Messages
8,463
To make sure I interpreted this correctly you want the top 3% of the distinct names. If correct there are 13 names so top 3% returns a single name. I did not add randomize to this. You can put that in the autoexec to ensure each time you open the db, you do not redo the series.
 
Local time
Today, 23:48
Joined
Nov 22, 2019
Messages
31
To make sure I interpreted this correctly you want the top 3% of the distinct names. If correct there are 13 names so top 3% returns a single name. I did not add randomize to this. You can put that in the autoexec to ensure each time you open the db, you do not redo the series.
I think you caught me wrong here. I want 3% of each Rater_Name in the list.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:18
Joined
May 21, 2018
Messages
8,463
To make sure I am clear. You want for each rater, a random 3% of their records?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:18
Joined
May 21, 2018
Messages
8,463
Code:
SELECT
  A.*
FROM queue1 AS A
WHERE (((A.id) IN (SELECT TOP 3 PERCENT
  B.ID
FROM Queue1 AS b
WHERE A.Raters_Name = B.Raters_Name
ORDER BY Rnd([id]))
))
ORDER BY A.Raters_name;
 
Local time
Today, 23:48
Joined
Nov 22, 2019
Messages
31
Code:
SELECT
  A.*
FROM queue1 AS A
WHERE (((A.id) IN (SELECT TOP 3 PERCENT
  B.ID
FROM Queue1 AS b
WHERE A.Raters_Name = B.Raters_Name
ORDER BY Rnd([id]))
))
ORDER BY A.Raters_name;

I tried the above code but failed to get 3% off individual Raters_Name Wise.
For example, Mr. A has 7 Entry, he should at least 3% of data against his name.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:18
Joined
May 21, 2018
Messages
8,463
Now that I think Of it I have ran into this problem before. If you are doing the subquery you will get wacky results using a top and sorting by rnd. The only workaround I know is to first export to a temp table a random sorted list of ids. Then use that table in your subquery. So if you do this off a button click you run the make table query first.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:18
Joined
May 21, 2018
Messages
8,463
If you run the maketable query and use that in the query it seems to work. I did top 2 records for demo. I do not know why this does not work in a subquery, but have seen this before.
 

Attachments

  • QM2.zip
    81.4 KB · Views: 120
Local time
Today, 23:48
Joined
Nov 22, 2019
Messages
31
If you run the maketable query and use that in the query it seems to work. I did top 2 records for demo. I do not know why this does not work in a subquery, but have seen this before.

I am bit confuse MajP How do i get the end result.
Its not giving me the data in percentage.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:18
Joined
May 21, 2018
Messages
8,463
Just change the query to top 3 percent.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:18
Joined
May 21, 2018
Messages
8,463
What were the results. It is 3 percent so should get 1 record each.
 

Users who are viewing this thread

Top Bottom