Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Access is designed to be a database for individuals and small businesses. Larger enterprises will usually turn to database solutions such as Oracle or SQL Server. What Access has in common with larger applications is that the underlying structure relies on the same programming language, SQL which is spelled SQL and stands for Structured Query Language. When your database grows large and your needs become complex, you may find it easier to program your queries in SQL instead of using the Design View grid. Let's learn a few fundamentals about SQL.
Here I have my SalesReps table and it shows all of my information about my SalesReps. If I go over to the navigation pane and downs to the queries section, I will double-click on the High/Low Salaries. This query pulls just three fields, LastName, FirstName and the Salary. And the salary has both the highest values greater than 30 and the lowest values less than 10. When I look at this in Design View, I can see the three fields and in the Criteria line, I see less than or equal to 10 or greater than or equal to 30.
Let's go up to the View button and click on the dropdown and see what this looks like in SQL. Now we can view the code underlying the query. SELECT tells Access which fields you want to see, LastName, FirstName and Salary. Notice the syntax always includes along with the field name the table name as well, TableName.FieldName. SalesReps.LastName. FROM tells Access which table the data is stored in. Where has our criteria SalesReps. Salary<=10 or SalesReps.Salary>=30.
Now at the end of your SQL statement, there is a semicolon. I'm going to add a line so I Backspace over that semicolon. Go to the next row and put in my next line. ORDER BY SalesReps.LastName and then I will put in my semicolon to show that I'm finished with the query. When I go back to Design View, now I can see the addition in the Sort row of the Ascending under LastName.
And when I go to the Datasheet View, sure enough, I am now sorted by last name. If you need your Access database to communicate with enterprise-level databases, becoming familiar with the SQL code view and programming language will make you a professional power user.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.