Solved How to get random record from MS Access database

[akbarmahfuzalam]

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]

Hi,

Try:

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]

SELECT TOP 3 Percent * ...

Eberhard

 

[The_Doc_Man]

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.

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]

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]

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]

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]

I use this

SELECT TOP 1 tblQuestions.QID, tblQuestions.Question, tblQuestions.Answer, tblQuestions.CategoryID, tblQuestions.LevelID, tblQuestions.Qstatus, tblQuestions.Used
FROM tblQuestions
ORDER BY Rnd([QID]);

 

[theDBguy]

I don't understand it, but I've always used this from The Access Web. Could somebody explain to me why it's necessary to call the Randomizer() function and what's up with all that stuff with the IsNull() function? How can it be simplified? Thanks!

Queries: Create a query that returns Random records

 

[MajP]

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]

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]

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]

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]

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!

 

[MajP]

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]

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]

Not only is this explainable, it is absolutely done on purpose. Doing otherwise would be a huge mistake.

 

[jdraw]

Agree that RANDOMIZE is key to getting rando records in subsequent tries.

 

[theDBguy]

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]

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!