Microsoft Access 2010 Tutorials
Accessing Your Data / Crosstab 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 / Crosstab Queries)
Crosstab Queries are something you just need to experience to appreciate, and let's take a look at some Crosstab Queries in this video, and the only way to really understand one, is to see it happen. Now the way we're going to create a Crosstab is to go to Create, and we can use the Query Designer, and what we'll do is, if we just drop a table in. Once we get the table in, then we will get our Ribbon up here for our Query Design Tools, and then we can click Crosstab, and it changes what we can design with down here. Now we can also do these just typing in straight SQL, but that's going to be the hard way to do it, especially while learning, so let me show you a better shortcut OK? What we're going to do here is close this query out, and then if you remember, I've done a students instruments query, and depending on what order you're watching this in, you may not have this query, but let me show it to you in SQL View. You can copy that, or you can just watch me, and come back to it later on OK? But really what I'm doing is a join on two tables, I'm getting the students first and last name, concatenating them together, getting the students age, City and instrument, and we'll use that one for now, as a good place to start. So what I want to do with this, is go into Design View, and notice the way this is set up now, if I run it, it returns this information. Well what I'm wanting to find out is, what's the average age for each instrument in each one of our Cities. Now I could pull that as a standard query, but I want to play with that in Crosstab, and show you what that's going to look like. So I'm going to go to Design View, and I don't want the student names in there, so I'm just going to right click and Cut to get those out of there OK? And that's just a little trick to remove things down here, but what I want to do with this now, is come up to the top, and just click on Crosstab OK? Now, just bear with me here, we need to set, for example, the age of the students are going to be the values that we get. The Cities, I want those in the column headers, and then the instruments that are being played, let's put those in the row headings alright? Actually let's do, let's do that differently. Let's switch that around, and we may even come back and look at it differently in just a moment. So we're going to do our instruments across the top in the columns, our students in the rows down the left side, and then the age of our students are going to be grouped as a value OK? And actually I don't want to group those, let's just do an average of those, and so when I execute that now, notice what we've got. How interesting is that? This shows me that in Brentwood, the average is seventeen for the drums, the guitars eleven. In Fairview, the average age is fourteen on the guitar players, and in Nashville, you can see the averages here, OK? Now that gives me the average ages, what if I want to know how many students we have? Well that's very easy to do with a Crosstab, just go back to Design View, and change this from average to just count. Let's just count the number of items that we have, and this is showing me that I have one person learning drums in Brentwood, learned one learning guitar, one in Fairview on guitar, two drummers in Nashville, one guitar player, one piano player, and one saxophone player OK? Now, this gives you just a little idea of what you can do with this. Now let me go back one more time, and let's switch these. Let's make this the column heading, this the row heading, run it one more time, and notice how we've switched these around. Here are our instruments, and here are our Cities OK? Now if I want to get really cool with this, I can come up here and click on Totals, and then come down here, and just say sum these up, and this would give you an idea of what we're looking like in each City. So we have two students in Brentwood, one in Fairview, five in Nashville right now OK? That is a Crosstab, now let me show you one more thing. If we go to Design View, we did this totally by clicking and dragging and choosing our values, our columns, and our rows, now if I get two columns OK, and I try to run this, it'll throw me an error, but notice it will tell me, and remind me what I need to do. I need at least one row heading, one column heading, and one value option, and so I've got two column headings, no row headings, so I go back and set a row heading, and when I run that, it works. Now one other thing I want to show you. After you've done all this, and it's working go to SQL View, and you can see how to build a Crosstab Query, directly in Transact SQL, although I have to admit to you, this is not the way I start doing my Crosstabs. I'm always going to build them in Design, kind of get them close, get them the way I'm liking the way their looking, then I'll go in and start hacking around in the Code a little bit to make them a little more complex or whatever I'm actually looking for. So anyway, that's just a really quick introduction into Crosstabs. Play with these with your data, that makes sense to you, data that you know, and that you can intuitively tell that it's correct. And you'll see, you can get some real powerful information out of your database, by using the Crosstab Query.
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
