SELECT Statements

IsáBel .
A free video tutorial from IsáBel .
Microsoft Certified IT Professional. Trainer of 21 Years
4.4 instructor rating • 14 courses • 28,255 students

Lecture description

Multiple examples will walk you through the core of scripting SQL - the SELECT statement.

Learn more from the full course

Microsoft Access SQL: SQL for Non-Programmers

Learn How You Can Use SQL in Microsoft Access with the Tips, Tools, and Exercises in This Best-Selling Course.

03:20:46 of on-demand video • Updated June 2020

  • Files & SQL Script Included!
  • WRITE SQL Queries in Your Microsoft Access Databases
  • CREATE Select Queries
  • FILTER with the WHERE Clause
  • SORT Your Access Records
  • GROUP Your Records
  • USE Aggregate Functions
  • LEARN About Date Function
  • GET Familiar with Functions You Can Use with Strings
  • LEARN How to Join Tables
  • CREATE Tables Using SQL
  • INSERT Records Into Your Tables
  • Use Action Queries Like:
  • - UPDATE
  • - DELETE
  • CREATE SubQueries
  • ... and more
English [Auto] I'm going to start off with a pretty easy one and feel free to follow along. In this example I want to select all the columns and all the records from the Customers table. Now notice here in the query window select is already there for us as well as a semi-colon. Now when you enter your select statement it's good practice to end the statement with a semi-colon. So the system knows that you're done. That's actually required in some other forms of sequel but not actually required here in Access. But if you do want to follow along with good practice go ahead and in all select statements with a semi-colon. All right. So I want to see all the records in the Customers table. So I'm going to use my asterisk I'm going to select everything. From customers. All right. Now that should do it in my Rivett I'll go up to the top and click run and there it is. It's bringing back all 29 records from that table. Now I'll go ahead and switch back over to my sequel view and to do that. I'm going to go to the View button on the top left hand side and change it back to sequel view. Now just to let you know sequel is not case sensitive and select from don't need to be capitalized. But again I'm showing you that here so it's easier for you to read. All right. Next I'm going to try that again. But this time I want to select all the columns and all the records from the Products table. So all I have to do here is just highlight customers and change that to products. Good. I'll go ahead and run the query. And there we are. There should be 14 columns and 45 records now in the last two examples. I use the asterisk to retrieve all the data. Next I'm going to specify the columns that I want to use. So in this example I want to select the company names state and phone numbers for all of my customers. So I'm going ahead back to sequel by clicking on the View dropdown and changing that back to sequel view. And you have to really be familiar with the information that you're pulling out here. And luckily for you I'm totally familiar with the tables but you also might want to spend a few minutes just getting used to seeing what information is in what tables. Now I want to see all of my customer information so I know that I have to pull the information from the customers table. And again I want to see the name of the company the state that they're in. And their phone number. So I'm going to get rid of my asterisk and I'm going to first type good company followed by a comma and the field for state in my table my underlying table is actually called State Province. So how I'm going to type that here is I'm going to start off with square brackets. And the reason why I'm starting off with a stick square bracket here is because the name of the column which actually I should probably spell correctly here province with the in there we go and close out my square bracket the name of the column has a special character in it. It has a forward slash and if I just typed out space province without the square brackets around it the system wouldn't like it and it would throw me an error. So if there's any special characters in the name of your fields you'd want to surround the name of the field with square brackets. All right I'll separate that with a comma because I also need the phone number. Now I'm going to start off here with a square bracket again. And the reason here is because in the underlying table the name of the field is business. So it's two words separated by a space and a space is actually treated like a special character here as well. So again I need to surround the name of the column with square brackets. All right. So I have select company name state province and business phone from customers. I'll go ahead and run. MATT AND IT IS PERFECT. Again 29 records and three columns. Now you can also specify the order in which you want the columns to show up in your select statement in the underlying table. The company name state and phone actually happened to be in the same order. But maybe I want to see if for some reason state first. I could have typed that into my sequel statement. I could have typed in state province company and phone business phone if that's the way I want to see it. You don't have to follow the order of the underlying table unless you really want to. Which I did here. Ok I'll show you three more select statements before moving on to the next topic. Next I want to select the product name list price add category for the records in the products table. So first I'll go ahead and get rid of customers and change that to products. And again I want to see the product name list price add category so delete the columns that I had from the last query and product name does have a space in it in the underlying table. So I will wrap it up and of course square brackets right are separated by a comma and add in the list price field. Wrapped up again in square brackets. And last but not least I also want to see category. I'll go ahead and read that. And there we are 45 records. Now switching back to my sequel you just to let you know so far when I've been creating the sequel statements I've been deleting them or typing over the previous ones right and that's because here inside of Access you actually can't type in more than one sequence statement inside of the query window. Now if you're used to using other types of sequel like sequel server you'll know that you can of course type in multiple sequel statements all on one sheet or all on one query window here inside of Access. It's just one at a time. That's why I keep deleting it and starting again. OK. In my next example I want to use the employees table and I want to select the first name last name city and state of the employees. So first I'm going to go ahead and get rid of the products table and type in the name of the table that I want to use which is employees and I'll get rid of all of my fields from the products table. And I want to add in the first name field and you know it has a space in that column name. That's why I surrounded it with the square brackets right. OK I also want the last name field followed by city and state province move my cursor all the way so you can see that Ansin state province has that special character of the forward slash. I'm again wrapping it up in square brackets. All right I'll go ahead and read that. And there I go again my nine employees but I'm just saying the four fields that I selected in my sequence statement. All right back to sequel view and in the last example in this lecture I'm going to use the customers table to select the company name state first name last name and job title of the customers. So I'll change my employees table to the customers table and I'm not going to get rid of everything here because I do want to use first name and last name and those field names are the same in both tables. So to save a little bit of typing I'll go ahead and leave that. So after select I'll go ahead and type in a company followed by a comma and then I need state province and state provinces here at the end. So I'm going to highlight that just by using my shift and my arrow key I'm going to cut it by using control X moving over before. First name control the to paste and comma. Just to be a little bit faster here. So I've got company state province. First name last name. And then lastly I need job title and that's it. Let me go ahead and make sure that works by clicking on run perfect. It works. So in this lecture you've gotten more familiar with the select statement and you've seen how easy it is to retrieve the data from your tables. Again the syntax is select. Then the name of the columns that you want to retrieve or an asterisk to retrieve all of the columns and then from and the name of the table that you want to retrieve the data from.