Solved How to get random record from MS Access database

[akbarmahfuzalam]

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.

 

[MajP]

SELECT TOP 3 PERCENT
 Queue1.Raters_name
FROM
 Queue1
GROUP BY
 Queue1.Raters_name
ORDER BY
 First(Rnd([id]));

 

[akbarmahfuzalam]

SELECT TOP 3 PERCENT
Queue1.Raters_name
FROM
Queue1
GROUP BY
Queue1.Raters_name
ORDER BY
First(Rnd([id]));

Let me give a shot on the above SQL code

 

[MajP]

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.

 

[akbarmahfuzalam]

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.

 

[cheekybuddha]

Hi, see here on Allen Browne's site about how to get Top N per group

hth,

d

 

[MajP]

To make sure I am clear. You want for each rater, a random 3% of their records?

 

[MajP]

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;

 

[akbarmahfuzalam]

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.

 

[MajP]

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]

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.

 

[akbarmahfuzalam]

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.

 

[MajP]

Just change the query to top 3 percent.

 

[akbarmahfuzalam]

Just change the query to top 3 percent.

I did that way as well

 

[MajP]

What were the results. It is 3 percent so should get 1 record each.

 

[akbarmahfuzalam]

What were the results. It is 3 percent so should get 1 record each.

At least one record if it is not meting 3% criteria