Microsoft Access 2010 Tutorials
Accessing Your Data / Writing Queries in SQL
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 / Writing Queries in SQL)
Writing queries directly in the SQL language is going to be the best way to get data out of your database in some of the ways that you can't really get it out of Design environment, and it's just much easier than trying to click and drag and figure out how to do it in the Design environment. So let me show you a couple things to do here. First of all, we're going to use a query that we created in an earlier video called Student Age DQ. And the way we created this query - I'm just going to right-click it and open it in Design View - if you remember, this is how we did it, we just pulled first name, last name, and age down and we ran the query, alright? Well, I'm going to go back to Design View with this and anywhere, when I'm in Design View on a query, once I have dragged my query down, all my columns, I've got everything I want, if I right-click and choose SQL View, it is showing me the Transact SQL that's out here. Now I don't know why, but by default they're showing it to you in a nice large 8-point type. That's not going to work, right? So let's go to the File Tab, go to Options, and come out to Object Designers, and come down under Query Design, and let's change this to, I'm going to change it to 14 for the class. You probably wouldn't want to do it that large, but if you come down here, close the query, reopen it in Design, right-click, go back into SQL View. Now it's nice and large, 14 point and we can see what's going on. Now, let me show you something else. I can still run this in the SQL View and see the results, and if I right-click when I'm looking at the results I don't get the SQL Design, but I can always right-click on the Tab up here, and get the SQL View, so I can work with the query in SQL. Now what can I do here? Well, let's do something kind of cool. Let's do a little concatenation here and let's put a plus sign, that's the SQL concatenation, and then let's just put a literal space in and another plus sign, and that will put the first name and the last name in a column together, and let's run that and see what we get. Well, that's kind of cool. And if I just right-click on the right side of my cell when I get the little double arrow icon, just like in Excel, it spreads the column to the widest needed location. But notice I've got this funky column name now called Expression 1000. So I'll right-click here, go back into SQL View, and now we will type the keyword as, and we'll just put students. Let's put student single, and click Run, and notice it has re-named it. Now let me warn you about something. If I've used that Caption Property in my table design, so I go back to Design View on the table, yes we'll hit Next, if I've used this Caption View on first name and last name, sometimes that thing will cause a problem, alright? In this instance, we're not getting a problem, it re-named it, but the general rule is, if you're going to re-name these columns, this is called Column Aliasing. Do it in your SQL and don't use that Caption thing, OK? I showed you how to do that Caption thing a little earlier in a different video, and if you like it, do it, but the general rule out there is don't do that, OK? But it's down there, and I wanted you to see how it worked. Now let's do something else cool with this, with SQL. Right-click, go back to SQL View, and let's just change these around a little bit. Let's make this last, and I'll get that R out of there. The R is silent. And right here let's put a comma in, and then let's change this to First, alright? And let's run it again, and notice now we've got the last name comma first name, and let's go back one more time, and let's say, you know what? I'm going to take the little semicolon out that tells it it's the end of the Command. I go to the next line, and I will say Order by first name, and I don't really have to put the table name in here because we're only using one table and it can quickly figure out. And notice, they're now, in the order of the first name. Well, that's not going to help us a whole lot, so let's go back to SQL View and let's change this to last name, and execute it, and now we've got Harold Carlisle, Charlotte Grimes, Hanks, James, Jordane, and see, you see how these are laid out. So as you can tell, you can do some neat things out here. Anything you build, go out take a look at it in SQL View, before you do anything with it and just look to see what's going on out here, and we will go back to SQL View later. But this is how you can create a query very easily in SQL View and start to also teach yourself a little bit about the SQL language at the same time.
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
