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

Local time
Today, 17:44
Joined
Nov 22, 2019
Messages
31
Hi Team,

I have a ask wherein i have table1 wherein i have 100000 record and i am trying to get 3% of record as per the job_id
I would like to use job_id as ran function.

So far i have written Sql Code in MS Access as

SELECT top 22 *
FROM Table1
ORDER BY Rnd(mid(job_id,2,18));

I don't want to use Select top 22*

I would like to use random 3% record from the table1.
 

cheekybuddha

AWF VIP
Local time
Today, 12:14
Joined
Jul 21, 2014
Messages
2,237
Hi,

Try:
Code:
SELECT TOP 3 PERCENT *
FROM Table1
ORDER BY Rnd(mid(job_id,2,18));

You probably don't need the expression in the Rnd() statement, just using ORDER BY Rnd(TableID) should do
 

ebs17

Well-known member
Local time
Today, 13:14
Joined
Feb 7, 2020
Messages
1,883
SQL:
SELECT TOP 3 Percent * ...

Eberhard
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:14
Joined
Feb 28, 2001
Messages
27,001
The easiest way to do this is to remember the old programmer's rule: Access won't tell you anything you didn't tell it first. (It is like a talking parrot in that respect.)

If you have a requirement for randomness, dedicate a SINGLE field in the table in question and update it with a random number. Then select your top 3%. Sounds like it is difficult but it isn't.

Code:
RANDOMIZE
CurrentDB.Execute "UPDATE tbl SET randfield = RND;"

Now if you have a query like "SELECT TOP n FROM tbl ORDER BY randfield;" you will get what you want.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:14
Joined
May 21, 2018
Messages
8,463
One thing to keep an eye on is that the percent rounds up as far as I can tell
so if I want top 10% and have 90 records I will get 9. If I have 91 it returns 10 records.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:14
Joined
Feb 19, 2002
Messages
42,981
If you can't live with the rounding, you will have to create the query on the fly with a fixed number. The n of TOP n is not a variable so when you need it to be variable, your only option is to create the SQL on the fly. In your code use a dCount() to count the records in the set and calculate the top 10% yourself where you can truncate rather than round the result.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:14
Joined
Feb 28, 2001
Messages
27,001
Good point, Pat. The syntax of the TOP n predicate doesn't allow a variable. Although there IS such a thing as building the SELECT TOP n .... on the fly using substitution and concatenation followed by updating a recordsource or updating a querydef. But that might be a little tedious for folks unfamilar with the process.
 

Dreamweaver

Well-known member
Local time
Today, 12:14
Joined
Nov 28, 2005
Messages
2,466
I use this
SQL:
SELECT TOP 1 tblQuestions.QID, tblQuestions.Question, tblQuestions.Answer, tblQuestions.CategoryID, tblQuestions.LevelID, tblQuestions.Qstatus, tblQuestions.Used
FROM tblQuestions
ORDER BY Rnd([QID]);
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:14
Joined
May 21, 2018
Messages
8,463
If you do not randomize you will get a unique record, but not a unique series. If you return 100 unique records and close out the DB and then return you will get the same 100 unique records. This is a pseudo random variable with a staring point. The seed is the starting point. Once in the app you will continue farther down the pass.

There is a very good reason for this. You want to be able to repeat your experiment. You need random numbers, but you want to come back and verify your results.
 

Dreamweaver

Well-known member
Local time
Today, 12:14
Joined
Nov 28, 2005
Messages
2,466
I use that for some Custom ID'S But it's not needed for getting a random dataset of x records but it's not truely random
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:14
Joined
May 21, 2018
Messages
8,463
So if you want to do a drug test and get 10 random people on monday then you better randomize. If not the following week the same 10 random people are going to be pissed.
 

Dreamweaver

Well-known member
Local time
Today, 12:14
Joined
Nov 28, 2005
Messages
2,466
Just tried my query I posted with a top of 5 I opened it 3 times without a dup record I have 500 enties in the table
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:14
Joined
Oct 29, 2018
Messages
21,358
If you do not randomize you will get a unique record, but not a unique series. If you return 100 unique records and close out the DB and then return you will get the same 100 unique records. This is a pseudo random variable with a staring point. The seed is the starting point. Once in the app you will continue farther down the pass.

There is a very good reason for this. You want to be able to repeat your experiment. You need random numbers, but you want to come back and verify your results.
Hi MajP. I get that, but I wasn't talking about the "Randomize" statement. Rather, I was referring to the Randomizer() function Dev added to the query as a WHERE Clause. Can you see what it's doing? Is it basically what you're saying here? Thanks!

Edit: I assumed Dev wrote the function, but it was credited to Joe Foster. Cheers!
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:14
Joined
May 21, 2018
Messages
8,463
but it's not truely random
All software is pseudo random. You can make it good but not perfect. Software can not make a true RN. They are all based on a starting point and algorithm. Real random numbers rely on hardware. They use noise from either biological, chemical, or thermal processes.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:14
Joined
May 21, 2018
Messages
8,463
just tried my query I posted with a top of 5 I opened it 3 times without a dup record I have 500 enties in the table

Yes as long as the db is open you will continue to start from a new position. Now close the db. You will get the same series. If not I can give you databases that will show this.
This is not a debateable issue. MS will clearly explain this. I did not make this up and it makes total sense. If you want a new series you have to randomize.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:14
Joined
May 21, 2018
Messages
8,463
Not only is this explainable, it is absolutely done on purpose. Doing otherwise would be a huge mistake.
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:14
Joined
Jan 23, 2006
Messages
15,364
Agree that RANDOMIZE is key to getting rando records in subsequent tries.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:14
Joined
Oct 29, 2018
Messages
21,358
Not only is this explainable, it is absolutely done on purpose. Doing otherwise would be a huge mistake.
I'm sure it is explainable and absolutely done on purpose; otherwise, they would have probably given a simpler solution on the website. However, they neglected to provide that explanation, and I just never thought to ask about it before. So, can you break it down for us? Thanks again!

So, basically, my question is was it necessary to call the Randomize() function for every record in the table? And the bonus question is, what was up with the InNull() use in the ORDER BY clause? Can it be simplified? For example, Mick basically simply uses Rnd(ID). Why did Dev decide to use the IsNull() function with some arithmetic expression?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:14
Joined
Oct 29, 2018
Messages
21,358
Agree that RANDOMIZE is key to getting rando records in subsequent tries.
Hi. I don't disagree, and sorry if the topic is getting derailed, but my confusion is with the RANDOMIZER() user-defined function. Not the RANDOMIZE statement. Can you help explain it? Thanks!
 

Users who are viewing this thread

Top Bottom