Microsoft Access 2010 Tutorials
Accessing Your Data / Top Queries
Visitors to VTC.com will be able to view all introductory videos for each training course.
Free Trial Members will gain access to first three chapters for each training course.
Full Access Members have full access to VTC.com�s entire library of video tutorials.
Learn More
Subtitles of the Movie (Microsoft Access 2010 / Accessing Your Data / Top Queries)
A Top Query, is a query that simply limits the number of rows that are returned from the database, regardless of what's in the where clause of the query. And the best way to let you understand this, is to let you see one. So let's go in here, let's click on Create, and we'll open Query Design, and we'll just grab something out of the students table, since we've been using that, and we know our data. We're just going to grab the first name, and I'll drag down the last name, and the age. And let's say that we had built a Complex Query, joining a bunch of tables and so forth, and we want to see if we've got it right, but we don't want to run a query that returns five or six hundred rows out of the database. Well notice if I run like this, I indeed get, you know, all of my rows, one two three four five six seven, I've got eight rows right? Well, let's go back to the Design View, and look right up here, there's a Return Area under the Query Setup Section on our Query Toolbar, on the Ribbon up here, if you will. Right now it's set automatically by default to all, and I just want to change that, to say five, OK? And notice, not a lot seems to have happened here, but if I right click, and look at the SQL View, you will notice right here Top five has been inserted into the SQL Code, and so when I run this, notice it only returns the top five. Now let's go play with this, right click, go to SQL View, and let's return the top two, and run that, and notice it only gives me the first two. Now you have to be very careful with this, because if we go back to Design View, you'll notice that I've just asked first name, last name, and age on students. I don't see anything here, but right here it's telling me, only return the first two. Now I can also return percent, and I want to show you this. If I pull for example 25 percent, now if I run this, notice we only got two. Two is 25 percent of eight. We have eight rows in our table, the percentage is going to be the percentage of the entire possible result set that would have been returned if I didn't have the Top in there. And if I right click on Query, and go to SQL View, you will notice that I now see Top 25 percent. So you're thinking, well what's the big deal? Why didn't I just put a Where Clause to limit it to just what I wanted? Well this really comes into play when we start to look at Views a little bit later on OK? And so if I do select top four and run it, obviously I get four. There are situations with Views where we have to use this Top to kind of trick the Query Engine if you will. I don't want to go too deep here, but this is a Tool that you can use, especially if you've written a complex query, that puts a load on the database, and you only want to return three or four rows, to make sure you've got it working right, that sort of thing. But just understand that kind of what I've been showing you all along here, with these queries, where we just pull data out of the table, and we leave this by default set on all up here, and we're just playing around and testing, can get you in trouble. When you've got three and four thousand rows of data in these tables, we're turning all this data back, to check and Code and stuff, can really start to put a drain on your database, and so this is something you might want to get into a habit of doing. Just pull the top five, test it, make sure the query's what you want, but just make sure that before you save this query, you go back out into Design Mode, and you change that back to all, so that you can get everybody. So now when you run it, you get them, and now when you save it, you're saving it with the all, there's my irritating music, and my warning, do you want to save the changes, and I can say yes, and then I can give it a name alright? But anyway that's what the Top Function does in the statements, and we will dig into this and re-visit it once again, when we talk about Views a little later on.
Tutorial Information
| Course: | Microsoft Access 2010 |
| Author: | Mark Long |
| SKU: | 34224 |
| ISBN: | 1-936334-91-7 |
| Release Date: | 2011-05-12 |
| Duration: | 9 hrs / 121 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
