|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Random Records By Richard Rost 
5 years ago
Select Random Records. Top X Records. Randomize. In this video, I will show you how to select any number of random records using a simple query in your Microsoft Access database. Brad from Langley, Oklahoma (a Platinum Member) asks: I'm trying to figure out how to build a form to generate random lists from our clients. We do drug testing for companies, and we have to randomly pick 20% of each company's employees for testing. MembersMembers will learn how to work with random numbers using VBA. I will show a technique to make the random numbers more "truly" random based on external input instead of relying completely on the pseudo-random number generator in the computer. We'll also see how to get random numbers from a web-based API.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Formula
Links
Interesting Stuff
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, random numbers, rnd, randomize, pseudo random numbers, random number generator, top 10 records, top x records, top 20%, truly random numbers, vba timer function, seed, unique, each row, 3d6, mouse xy, mouse move
IntroIn this video, I will show you how to select any number of random records from a table in your Microsoft Access database. We will learn how random numbers are generated in Access, including the difference between pseudo-random and true random numbers, how the RND function works, and how to seed it using unique values like the customer ID and the system clock. I'll demonstrate how to use calculated query fields to assign random values to records, sort them, and pick a specific number or percentage for tasks such as random employee drug testing.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. In today's video, I will show you how to select any number of random records from a table in your Microsoft Access database.We will learn how random numbers are generated in Microsoft Access and will learn the difference between pseudo-random numbers and true random numbers. Today's question comes from Brad in Langley, Oklahoma, one of my platinum members. Brad says, "I'm trying to figure out how to build a form to generate random lists from our clients. We do drug testing for companies and we have to randomly pick 20 of each company's employees for testing." Yes, Brad, this is definitely possible in Microsoft Access. However, before I show you how to do it, let's talk a little bit about how Access generates random numbers. Now, in the world of random number generation, there are two kinds of random number generators. There's a pseudo-random number generator and a true random number generator. Microsoft Access and most computer programs use what's called a pseudo-random number generator. It basically uses a mathematical algorithm to determine random numbers. Most people don't know this, but random numbers in the computer aren't really truly random. They're based on complicated algorithms that kind of sort of generate random numbers, but a computer on its own can't really easily generate a true random number. So these algorithms need to be seeded to prevent duplicate results. You can seed it manually by telling it a number or you can tell Access to randomize the random number generator using the system clock timer. It'll take, for example, the number of seconds since midnight and use that number to seed the random number algorithm. What's an algorithm? Well, it's basically just a complicated formula that Access uses to kind of sort of generate the next random number. That's what a pseudo-random number generator is. This is as opposed to a true random number generator, which generally has to use external information outside of the computer to really generate a random number. These external stimuli can include things like user movements, like how the mouse moves on the screen, which generally is random. Things like atmospheric disturbance, radioactive decay, thermal noise. I've seen true random number generators calculate the actual temperature of the CPU. Things along those lines. Things that you really can't determine with just an algorithm; you have to actually measure something in the outside world. It's much more difficult to program and they usually require some kind of outside equipment. Like to do atmospheric disturbance, for example, you have to measure the movement of air particles and things of that nature. Usually, pseudo-random number generators are okay for most applications, games, business stuff, like picking 20 random employees. True random number generators are usually used for things like generating a lot of random numbers, security applications, cryptocurrency, that kind of stuff. So chances are, for most things you're going to be doing in Access, a pseudo-random number generator is just fine. So that's what I'm going to show you in this lesson. Members will talk more about true random number generation in the extended cut. For those of you who are interested in learning more about random number generation, I have some interesting links to some articles. I'll put them in the links section down below in the description. Go ahead and click on that. Just one prerequisite before we get started today. If you don't know how to make calculated query fields, then go watch this video. It's free. It's on my website. Calculated query fields are where you take a query and you make a new field that's based on other fields or mathematical calculations or something else. So you need to know how to do that before we continue for today. Go watch that and then come on back. Here I am in my TechHelp free template. This is a free download on my website; go grab a copy if you want to. In here, I've got a customer table where I've got six customers and I want to assign each of these customers a random value so that I can sort them in order and pick the top three, let's say. Let's make a query. Let's come over here and go to Create, Query Design. I'll bring in my customer table and then I'll bring in the customer ID and the first name. So right now it looks like that. Now let's make a calculated field to put that random number in. In Access we use the RND function to generate a random number. So I'm going to Shift+F2 to zoom in. I'm going to make this. Let's call this field X and this will be RND(), that says give me a random number. Hit OK. Let's save this query as RNDQ, my random number query. Let's run it. Let's widen that field out so we can see everything in there, and there's our random number. The random number that it generates is a floating point number with a decimal point and it goes from 0 to 1. It's technically greater than or equal to 0 and less than 1, so it's not quite 1. If you want to generate a number, let's say you're rolling dice and you want from 1 to 6, you just have to do a little math on it. We'll talk about that later. Now, the problem that we have here is that every single record got the same random number. This is something that happens when you're dealing with queries in Access. Whenever you use a function that doesn't have any parameters in it, Access will only evaluate that function once and use the same value for every record in the query. Even if it's something like Date or Now, even if this query takes 5 seconds to generate, that Now value is going to be the same for every record because Access only evaluates it once to save time. The same thing happens with RND. It only evaluates that once because it only has to. So now you get the same random number for everybody. Yes, every time you run this, it will be a different random number because Access uses the random number that it generates to seed the next random number. What is a seed value? Well, a random number seed is basically telling Access, take this value, put it in your algorithm for generating random numbers. An algorithm is just some big complicated math function, it could be anything really, and then out from that spits a random number. What that random number is is the next seed for the next random number. That's how it generates a pseudo-random number. Now, if we want a different value for each record, we have to seed this with a different value for each time the random number runs. So, going to design view, we have to give the random number a different seed for each record. There happens to be a unique seed value for each record and that happens to be the customer ID. So why don't we feed the random number a seed value of the customer ID? That's a good autonumber. We can use that. That should be unique for each record. Let's save it and run it. There we go, there's a bunch of unique values. If I go back to design view and run it again, I get a different set of unique values. Each time you run it, you get a different set of unique values because each record is seeding the random number generator. But there's another problem that creeps in. Watch this. If I close the database and I open it back up again, run my query, look at that: 0.70, 0.53, 0.57. Close the database, reopen it again, run the query: 0.70, 0.53, 0.57. The problem is that random number algorithm resets every time Access starts up. So unless you have some way of randomizing that, then you're going to get the same values over and over and over again. In this case, every time you run your list of employees for drug testing, the same 25 percent are going to get tested every time unless you find a way to randomize that sequence. Now, in VBA, for my VBA programmers, we do have a command called Randomize. We can use that in our VBA code to randomize the random number seed every time we run our code. But even if you issue a Randomize command in VBA, it still doesn't help your queries if you're generating random numbers in this fashion. So what we need to do is find some way to come up with a unique seed in here, a unique value without relying on the Randomize command. Furthermore, that seed value has to be different each time the random function is called. Right now it's the same. For each row, if you restart the database, it's going to give you the same value again because this value is always the same. So we're going to add to that something else that's a little bit different. What's a little bit different? What about the system clock? There's a function called Timer that at any moment will tell you how many seconds have passed since midnight. It's very similar to Date and Now. Timer gives you the number of seconds, and it's a floating point number too, so it's very accurate. So what we're going to do is take the customer ID and multiply that by the Timer value, that's the number of seconds since midnight, and then multiply that by negative one. Now why are we multiplying by negative one? To quirk in the way the random function works, let me explain. Normally, for the random function, when you send it a seed value, if it's blank or greater than zero, random will always return the next number in the pseudo-random sequence. So whatever the next number in the algorithm happens to be based on the seed value, that's the number you get. If you send it a zero, it will give you the most recently generated number, and that's handy if you want to repeat the value some way. But if you send it a negative number, it will use that seed number as the specific seed value for the algorithm. It's not going to say, "give me the next number in the sequence." It's going to use your number and run that through the algorithm. So by taking the customer ID and the Timer and saying, "use that specific value for the algorithm," you are guaranteed to get a random number every time, unless you happen to run the same exact query at the same exact time tomorrow. If that's the case, you have to be careful with that. So if your machine boots at midnight and automatically starts up Access and automatically runs this random number generator, you might duplicate it. But I doubt it, because your computer is not going to start up exactly the same, to the millisecond. So this is a pretty good way of making sure that your numbers are randomized. Let me hit OK. Let's save that. I'm going to close the database and let's open it back up again and run the query, and there's our random numbers. 8.78, 0.58. Let's close it, open it up again, run the query. 0.94, 0.44. See? There you go. Now it's completely randomized. Again, the reason why is because we're specifying the random number seed to use, and the customer ID times the system timer is going to be random for 99.99% of the time. This will work just fine for you. Now, to get the top 25% or top 50% or whatever you want, all you have to do is sort based on this field now, sorted ascending. If I run them now you'll see they're sorted. The E-02 means you have two more decimal places over there; that's a very small number. Now see, another very small number on top. Let's keep running it. There we go. Now you can see they're in order. If you want to change this to be a number from like 1 to 1000, for example, just take this result. Let me show you. Let's go 1 to 1000. We're going to take this result, we're going to multiply it by 1000, then we're going to chop off the decimal point. We're going to go Int of all of that, then we're going to add 1, so instead of going from 0 to 999, we go from 1 to 1000. That'll give us numbers from 1 to 1000 now. If I run it now, there's your numbers. Run it again, there we go. Now that I've got it sorted, I can come over here and say, "return..." Do you want to return the top five, the top 25, the top 25 percent? Top 25 percent, and then run it. There they are, the top two. Or if you want the top three records, run it, and there you go. I cover a lot more with random numbers in my Access Expert Level 26 class. Lots of math stuff in this lesson too. We actually build a dice roller, and I show you again how to display random X records. Here's the dice roller we build. Roll 3d6 dice. In Access Developer Level 3, we cover random numbers in VBA. I'll teach you about the RND and the Randomize functions in VBA and we'll build a math quiz generator where it generates two random numbers and it asks you for the answer. I will put links to both of those classes in the links section down below the video. If you want to learn more about generating random numbers in the extended cut, 22 minutes long, I cover a lot of different stuff. First, we'll talk about random numbers in VBA, how the random function works a little bit differently, and you can use the Randomize function to make sure that your random number seed is initialized properly. Then we will build a dice roller. A dice roller goes back to my Dungeons and Dragons days. You can roll three 6-sided dice six times and there's your ability scores: your strength, intelligence... You had to be a gamer nerd back in my youth, but dice rolling is always a popular thing. People always ask me how to do it. "How do I roll dice?" There's some dice rolling. Are they going to play Yahtzee or whatever? Then we'll learn how to assign random numbers to the customers and keep them there. How do you assign those random numbers and stick them in the customer's record? A couple of different reasons you want to do this. If you're doing drug testing, for example, you might want to know, last time, who were the top 20 that we called? So we don't call the same ones. So you can store that number in there. Or you might want to just be assigning random IDs to people and just have that part of the record. I'll show you how to use a record set to loop through the records in the customer table and assign a unique random number to each one of them. Then I'll show you how to get a better random number than just looking at the system timer. People randomize using a human element. Remember I said that true random numbers are better off if they can look at some external stimulus outside the computer? What's better than the mouse coordinates? Where exactly on this button does the user click? If they click here, the coordinates, the X and Y coordinates of the mouse, would be slightly different. So we can use that multiplied by the system timer, which is the number of seconds since midnight. That'll give us an even better random number than just looking at the system timer alone. Finally, if you want a real true random number, we'll use a free web API from random.org, which is a cool website. That's in the link down below, by the way, in the cool stuff links that I mentioned earlier. You can get a random number from them right into your Access database by clicking a button. It'll go out to the web, ask their website, "Hey, can I have a random number? Please bring it right back and put it right inside your database." All that is covered in the extended cut, 22 minutes long. The section will be 25 minutes, but who's counting? Silver members and up get access to all the extended cut videos. There are lots now, there are like 200 and some plus. Gold members can download all my databases and source code from the TechHelp videos. That's the reason they become a member right there. How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you've finished the expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more. You can now become a diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You'll get a shoutout in the video and a link to your website or product in the text below the video and on my website. Don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free. QuizQ1. What is the primary difference between a pseudo-random number generator and a true random number generator?A. Pseudo-random generators use mathematical algorithms, while true random generators use external stimuli. B. Pseudo-random generators use external stimuli, while true random generators use mathematical algorithms. C. Both use only mathematical algorithms but in different ways. D. Both are equally secure for cryptographic purposes. Q2. What does Access use to generate random numbers? A. True random number generator B. Pseudo-random number generator C. Radioactive decay detector D. Quantum computer Q3. Why do pseudo-random number generators need to be seeded? A. To prevent duplicate results B. To make numbers only between 0 and 1 C. To access the computer's datetime D. So they generate the same number always Q4. Why do all records in an Access query using RND() without parameters often get the same number? A. Access evaluates the function only once per query run B. Access cannot generate random numbers C. The function has a built-in bug D. Access uses the most recent number always Q5. How can you get a different random number for each record in an Access query? A. Provide a unique seed for each record, like CustomerID B. Reboot your computer every time C. Use the NOW() function D. Use a true random generator Q6. What happens if you use CustomerID as the only seed and restart the database? A. Same random numbers will show up for each CustomerID after restart B. Random numbers will change every time C. Random numbers cannot be generated D. The query will show errors Q7. What Access function provides the number of seconds since midnight? A. Timer B. Clock C. Date D. Now Q8. Why multiply the seed by negative one when using the RND function in Access? A. It tells Access to use the exact seed value for the random algorithm B. It makes all numbers positive C. It always generates the same number D. It prevents decimals Q9. What problem can arise if you use only CustomerID and Timer as the seed and run the query at exactly the same time each day? A. The same "random" numbers could be generated B. Access will crash C. No results will be returned D. Timer will reset to zero Q10. How can you get random integers from 1 to 1000 in Access? A. Multiply RND result by 1000, use Int(), then add 1 B. Use RND() only C. Multiply RND result by -1 D. Use the current date as the seed Q11. Which VBA command is used to further randomize the random number generator in Access VBA? A. Randomize B. Shuffle C. RND D. Initialize Q12. When is a pseudo-random number generator generally considered sufficient for use in Access? A. Games, business applications, and picking random employees B. Cryptography and cryptocurrency C. Generating numbers for quantum experiments D. When building web APIs Q13. What is one method shown to further improve randomization using external user input? A. Multiply mouse X,Y position by system timer B. Use the same seed value for all records C. Use CustomerID only D. Input a fixed number Q14. Where can you obtain true random numbers for use in Access, as mentioned in the video? A. random.org web API B. Microsoft Office support C. System clock D. Date function Q15. Why might you want to store random numbers in the customer records? A. To avoid selecting the same people for repeated processes like drug testing B. To ensure everyone gets the highest number possible C. To use as their phone number D. So you never have to randomize again Q16. What is the purpose of using calculated query fields in Access for this process? A. To generate dynamic random numbers per record using expressions B. To hard-code all field values C. To store text only D. To update table design Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers how to select any number of random records from a table in your Microsoft Access database. I will explain how Access generates random numbers, discuss the difference between pseudo-random numbers and true random numbers, and demonstrate how you can use these techniques for practical purposes, such as creating random employee lists for tasks like drug testing.To begin, it is important to understand that Access, like most computer programs, relies on a pseudo-random number generator. Rather than producing truly random results, it uses a mathematical algorithm to generate numbers that appear random. The starting point for this sequence, known as the seed value, determines the numbers you get. If you do not change the seed, you can get repeatable results, which is both useful and something to be aware of. A true random number generator, on the other hand, obtains its randomness from unpredictable physical phenomena, such as mouse movements, temperature fluctuations, or atmospheric noise. These are rarely needed for business applications, but they are critical for cryptography and situations where absolute randomness is required. For the vast majority of what you do in Access, using the built-in pseudo-random generator is entirely sufficient. If you are not yet familiar with calculated query fields, I recommend watching my free tutorial on that topic first. Calculated fields in queries allow you to create new values based on existing data or math, and you'll want to understand that before continuing here. Let me walk you through the process using the TechHelp free template database, which you can download from my website. I have a simple customer table with a handful of records. The goal is to assign each customer a random value, then sort and select, for example, the top three customers. First, I create a new query using Query Design, add the customer table, and select fields like CustomerID and FirstName. Next, I add a calculated field that uses Access's RND function to generate a random number. Saving and running the query, you will notice that every record receives the same random number. This happens because Access evaluates functions without arguments just once per query run, so every row gets the identical value. This is true for the RND function and for others, like Date or Now. To avoid this, each record needs a unique seed. Using the CustomerID as a parameter does provide different results per record, but you will still get the same numbers each time you restart Access and run this query because the algorithm begins from the same place. This means that employees picked for a random process will not actually vary between runs, which defeats the purpose of random selection. A better approach is to incorporate something variable and unpredictable, like the current system time. Access has a Timer function that returns the number of seconds since midnight. By multiplying CustomerID by Timer and passing that as the negative seed to the RND function, you ensure different values with each execution. Negative seed values tell the RND function to use your specific seed rather than just the next number in sequence, which greatly improves randomness. Now, when you run the query, you get a new set of random numbers for each customer each time. To pick a set of records at random, simply sort the query on the random number field and choose however many records you need, such as the top three or the top 25 percent. If you want a range from 1 to 1000 instead of decimals between 0 and 1, use simple math to expand and round the numbers to the desired range. These techniques allow you to build forms that generate random selection lists for any business process, such as picking employees for testing. If you need to retain random values for each record instead of having them change each time, you can assign the random number once and store it in a table. For those wanting to explore further, I cover more about random numbers in my Access Expert Level 26 and Developer Level 3 classes. We look at creating a dice roller, generating random numbers for quizzes, and using VBA's RND and Randomize functions. In the extended cut for today's lesson, which runs about 22 minutes, we will go even deeper. I demonstrate how to work with random numbers in VBA, how to use the Randomize function the right way, and how to build more advanced randomization tools like a dice roller. You will also learn methods to assign and save random numbers directly to customer records, and how to create higher quality random values using mouse coordinates and system timer together. Finally, I show how to retrieve true random numbers into Access using a free web API from random.org, which adds an external layer of randomness directly to your application. Silver members and higher get all extended cut TechHelp videos, while gold members can download every example database and source code. Gold and platinum members also enjoy other perks, such as free classes and priority for helping with TechHelp questions. Details about membership levels and their benefits are available when you join through my website. Remember, all regular TechHelp videos will always remain free as long as you keep watching. You can find the complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListExplanation of pseudo-random vs true random numbersHow Access generates pseudo-random numbers Understanding the RND function in Access Creating a query to assign random numbers to records Seeding the random number generator in Access queries Using customer ID as a unique seed for randomization Issue with repeated random values after restarting Access Incorporating the Timer function to improve randomness Explanation of negative seed values in random number generation Generating random numbers in a desired numeric range Sorting records by random values in a query Selecting a random subset of records from a table Returning a specified number or percentage of top random records |
||||
|
| |||
| Keywords: TechHelp Access random numbers, rnd, randomize, pseudo random numbers, random number generator, top 10 records, top x records, top 20%, truly random numbers, vba timer function, seed, unique, each row, 3d6, mouse xy, mouse move PermaLink Random in Microsoft Access |
