Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

Microsoft Access SQL Seminar, Part 1
 
Learn the basics of the SQL programming language
Build SELECT queries and enhance your Access databases

 

 

This 1 hour, 38 minute video seminar will teach you the basics of the SQL programming language. You will learn how to construct SELECT queries, filter and sort your results, and use basic SQL commands and operators.

This seminar was developed to teach you how to use the SQL language to enhance your Access databases. As I create more and more advanced Access seminars, I see that it's VITAL for my students to have a firm grasp of the SQL language. Knowing just a little bit of SQL can help you tremendously. You can use SQL to enhance your forms and reports. You can create dynamic SQL statements to change list and combo box data on the fly. Plus, you can reduce the clutter in your database by getting rid of "saved" queries.

Learn More...

Click on the video image to the right to learn more about exactly what's covered in this seminar. The video is about 9 minutes long and fully explains everything that's discussed. Then, continue reading the course outline below if you have further questions.

AccessLearningZone.com
  9 minutes
 
Seminars - Access SQL Part 1
Description: Learn the basics of the SQL programming language to enhance your Microsoft Access database projects
Versions: I will use Access 2010, however the lessons are valid for all versions of Access back to 2000 with some minor cosmetic differences.
Pre-Requisites: Access 101 - 104 a must
Access 201 - 207 highly recommended
Access 301 - 329 very helpful
Running Time: 1 hours, 38 minutes
Cost: $59 - Order multiple courses to receive a discount up to 50% off
Includes a sample, customizable, working database, as built in class.

 

This seminar has several goals. You will:

1. Learn about SQL and what it is
2. Discover why you should learn to program in SQL
3. Create basic SQL SELECT statements
4. Learn the FROM, WHERE, and ORDER BY clauses
5. Use the SQL operators and keywords such as AND, OR, and IS NULL
6. Work with the LIKE command and wildcard characters

7. Learn the IN, DISTINCT, DISTINCTROW, and other commands
8. Build a list box with a dynamic SQL sort
7. Create a form with a dynamic SQL RowSource

We will begin by learning about SQL: what it is, what it means, and why you should learn it. You'll see some examples of basic SQL statements.

 

Next you will learn how to construct a basic SQL SELECT statement. You will see how Access has been generating SQL statements for you all along when you use the "Design View" query builder. You'll learn about the FROM, WHERE, and ORDER BY clauses.

 

Next we'll dive deeper into WHERE clauses, which is where the power of an SQL statement is. You'll learn about the different operators, how to work with string values, TRUE/FALSE values, and dates. You'll see how the BETWEEN keyword works. You will learn how to use AND, OR, IS NULL, and IS NOT NULL.

 

 

You will learn how to set up Parameter Queries, use the LIKE keyword and wildcard characters to find partial matches in your text values. You'll learn how to search for ranges of characters and values. You'll learn about the IN keyword.

 

You will learn more about the ORDER BY clause, including ordering query results based on multiple fields and in ascending and descending order. You will learn the difference between the DISTINCT and DISTINCTROW keywords, and how to use them to return a list of unique values from your tables.

 

Next we'll build two forms to demonstrate practical examples of how SQL is useful. First, we'll create an unbound form with a listbox showing our customers. We'll create two buttons on the form that will allow us to change the sort method of the listbox by either the customer's last name or his credit limit. This will all happen in VBA code with an SQL statement. No queries will have to be built.

 

Next, we'll use a similar technique to allow one form to display data from multiple tables. We'll create a form showing a list of first and last names. Using buttons and custom SQL statements, we can change the table that provides data to this form. We can show either customers, or employees, or we can use a special UNION query to show both at the same time. This is an example of how to dynamically modify the RecordSource property of a form at runtime using SQL.

 

Again, this seminar is the perfect first step for anyone who wants to learn how to enhance their Microsoft Access databases with the added power of custom SQL. It is the first part of a three-part series. Part 2 will cover using SQL to modify and manipulate data (add, edit, delete records). Part 3 will teach you how to modify the design of your database (table and query structure) using SQL.

Of course, if you have any questions about whether or not this seminar is for you, please contact me.
 

 

Access SQL Seminar 1 Outline

00. Introduction (9:03)

01. What is SQL (6:09)
What is SQL
Structured Query Language
How to see SQL in queries
Why learn SQL

02. Basic SQL Components (14:27)
Build Customer Table
Build a simple query using QBE Grid
SQL View
SELECT statement
FROM clause
ORDER BY clause
WHERE condition
Changing fields in SELECT statement
Showing all fields with *
Clauses on separate lines
Semicolon
No spaces in field names

03. WHERE Clauses 1 (16:00)
Operators: = > < <= >= <>
String values and quotes
TRUE, FALSE, Yes, No, -1, 0
Dates in # signs
BETWEEN with dates, text, numbers
AND, OR with multiple fields
IS NULL
IS NOT NULL

04. WHERE Clauses 2 (13:23)
Parameter Queries
Parameters with BETWEEN
LIKE keyword
Wildcard characters: *, ?, #
Different wildcards: % and !
Range of Characters [A-P]
Not in a range [!A-P]
IN clause

05. ORDER BY and DISTINCT (6:49)
ORDER BY
Multiple fields
ASC, DESC
DISTINCT
DISTINCTROW

06. Customer List Form (15:22)
Build an Unbound Form
Add a Customer List Box
RowSource Property
Change SQL Dynamically
Command Buttons with VBA Code
Docmd.OpenForm

07. Name List Form (11:23)
Form that gets data from 2 tables
Show Customers
Show Employees
Show BOTH
UNION Query
Me.RecordSource
Me.Caption property

08. Review (5:13)

 


 

 
 

Student Interaction: Access SQL Seminar Part 1

Richard on 3/15/2011:  This seminar will teach you the basics of the SQL programming language. You will learn how to construct SELECT queries, filter and sort your results, and use basic SQL commands and operators. This seminar was developed to teach you how to use the SQL language to enhance your Access databases. As I create more and more advanced Access seminars, I see that it's VITAL for my students to have a firm grasp of the SQL language. Knowing just a little bit of SQL can help you tremendously. You can use SQL to enhance your forms and reports. You can create dynamic SQL statements to change list and combo box data on the fly. Plus, you can reduce the clutter in your database by getting rid of "saved" queries. This seminar has several goals. You will: 1. Learn about SQL and what it is 2. Discover why you should learn to program in SQL 3. Create basic SQL SELECT statements 4. Learn the FROM, WHERE, and ORDER BY clauses 5. Use the SQL operators and keywords such as AND, OR, and IS NULL 6. Work with the LIKE command and wildcard characters 7. Learn the IN, DISTINCT, DISTINCTROW, and other commands 8. Build a list box with a dynamic SQL sort 7. Create a form with a dynamic SQL RowSource
Alex Hedley on 3/15/2011: Brilliant Seminar,
Loads covered!
Great to have it all in one place for reference.
A great grounding in SQL, roll on Part 2 :)

Reply from Richard Rost:

Thanks.

jimmy on 3/15/2011: i watch this seminor part 1 i very injoied And this enrich my knowledge.

thousand thanks for the wonderful course
jimmy

Reply from Richard Rost:

Thanks, Jimmy.

Harry Goldstein on 3/16/2011: Will there be a PDF handbook for these lessons?

Reply from Richard Rost:

Yes. It's in the works.

 Bob Lilly on 3/16/2011: Well you hit and other HomeRun, looking forward to SQL 2!

Reply from Richard Rost:

Thanks!

Mo on 3/16/2011: Thanks!
Thomas Fries on 3/18/2011: This is exactly what I wanted

Reply from Richard Rost:

Wait 'til you see part 2. :)

R Fleming on 3/26/2011: Great stuff - when will Part 2 be ready

Reply from Richard Rost:

Working on it. I'm on the road right now for a few weeks, but when I get back, it's first on my list.

 Monty on 4/6/2011: What if you had over 1000 items(five fields listed per item). Would it still be applicable to use a list box for the applications you're applying to them in SQL 1, lesson 6? I ask because I have just such a sitution.

Reply from Richard Rost:

Sure. The number of items (records) doesn't really matter.

David on 5/12/2011: Richard,

Again the class. When do will you release Part2 and Part3?

And how is the PDF Handbook coming along?

David on 5/13/2011: Richard,

Do you have enough students requesting part 2 and part 3 yet? I hope so because I'm looking forward to taking the seminars.
David

Reply from Richard Rost:

Yep. Finishing up Part 2 now. Shouldn't be much longer...

WILL on 7/20/2011: in video #7 can u use a group option insead of buttons? if so how?
 MK on 4/4/2012: How do I "select all" in Access? Ctrl+A doesn't seem to be working for me. Thank you.

Reply from Richard Rost:

Select all of what? CTRL-A does work, but only if you're in certain circumstances. If you're in a table, for example, and you want to select all of the records, make sure you have ONE record highlighted first (click on the record selector button on the left) and then hit CTRL-A.

 MK on 4/5/2012: When I press the button I made, it prompts me to enter a parameter value. Any idea what I've done wrong?

Reply from Richard Rost:

You probably have something spelled wrong. A parameter value prompt pops up when Access can't find a field you're referencing.

 MK on 4/5/2012: One last question on this video...the prompts aren't coming up automatically in the build event programming terminal. For example you started typing "open form" and it came up as a tag. Is there a way to turn this feature on? And do you know off hand the hotkeys to toggle back and forth between the build event terminal and the access spreadsheet? Thanks again.

Reply from Richard Rost:

What version of Access are you using?

Genet Pinna on 11/16/2013: If you do want to see data from cutomerT table which you did not use them when you run a query, how do you define?
Genet Pinna on 12/10/2013: I use Access 2013 version.
Thanks,

Zied Ben Afia on 12/13/2013: Hey Richard!

Can we use NameList.RowSource = ""
instead of Me.RecordSource = "" ?
I don't get the difference between both of them


Jim Ogier on 1/22/2014: I built the forms as shown in the video but when I add the Event Open SQL statement I get an error prompt (highlighted in red) that an = sign is expected and I am unable to proceed from their. Any ideas what is wrong?

Jim O

Reply from Richard Rost:

What's the line of code that's generating the error? You're probably missing something.


Jim O on 1/23/2014: Yes their was an error (my doing) when I copied the name I included the brackets _Click(). As soon as I removed them everything worked fine.
Jim O on 1/24/2014: I have tried to apply this code to a button as an 'On Click' Event. But I keep getting an error message saying it expect an end statement. The code works if I run it through a regular query but I want to be able to manulipulate the data more easily with a combo box.

ProductList.RowScouce =

SELECT AddressDirectoryT.AddressID, IndividualxGroupT.GroupID, IIf([FirstName],[FirstName] & " " & [LastName],[CompanyName]) AS Contact
FROM AddressDirectoryT INNER JOIN IndividualxGroupT ON[ AddressDirectoryT.AddressID = IndividualxGroupT.AddressID];

End Sub

Jim O


Reply from Richard Rost:

It's probably the bracketing of your join field names. You'd need brackets around EACH field. Also, if possible, I don't like to put functions in my SQL statements. Almost always better to create a helper query with the function then use THAT query for your SQL statement data source.

Grant R on 2/6/2014: I noticed that my column widths were a little too small after I made my CustomerListF. Is there a way to get back in to edit the column widths? I've tried poking around with little success.
Tom Dlugosh on 1/3/2015: I created a customer form and added a list box to show the customer representatives associated with that customer. Works great! I then added a list box to show the ship to addresses associated with the same customer. It doesn't work! The Ship to addresses show up in different customers, sometimes the one with a customer ID 1 digit higher, sometimes 2 digits higher and occasionally anywhere in the ship to table. The SELECT statements are located in the list box properties and are identical in their format. What am I missing here?
Thanks,
Tom

Reply from Alex Hedley:

Make the Queries as their own object with a Form Criteria.
You can then set the Listbox source to these.
Open the Form and run the Query from the Navigation Pane to see if it's working as expected.
Check you are using the correct criteria and it has the right ID.

Joe Beniacar on 10/11/2015: Hi Richard, if you have multiple Tables and want to select the "*" from one of them, would you say: SELECT Table.* ?

Reply from Alex Hedley:

Indeed, you can choose the Fields by putting their Table name in front.
Often you would alias the Table so
SELECT t.field1 FROM Table t
Instead of
SELECT Table.field1 FROM Table
Makes it easier and less typing, we are lazy programmers and anything to make life quicker and easier :p

Joe Beniacar on 10/11/2015: Hi Richard, I have a few questions about this lesson: 1)Is a CrossTab Query like a Pivot Table (in Excel)? 2)If you had multiple Tables in your query, would you have to specify the "TABLE.fieldname" in the WHERE condition too? 3)For Operator Precedence, why doesn't NOT evaluate before AND/OR? - Isn't it kind of like a Comparison Operator, in that it also applies to just 1 field - while AND/OR applies across multiple conditions to multiple fields at the end? Thank you very much for your help.

Reply from Alex Hedley:

1) You don't have the same power as a Pivot Table since you don't have the drag in columns, filters etc. Think they removed Pivot Tables from Access 2013 (Discontinued features)

2) Yes you would have to be specific about your WHERE clause, just alias your Tables and add them there.

3) MS Article

Anonymous on 10/11/2015: Hi Richard, just 2 quick questions: 1)For the LIKE keyword, why would you not put '' around the [FirstLetter] Parameter piece, when you concatenate it with '*'? 2)Why does the * and # symbols from Access use the same symbol (!) in other SQL servers? Thank you very much for your help.

Reply from Alex Hedley:

1) Do you have the full string he uses? It's been a while since I've watched this one

2) In what context. Access uses # for dates, * for select all Fields
You use * in T-SQL

Robert Maddox on 10/26/2015: Issue Need Advice-
I can run the simple VBA of Customer List Form (#6) on my Windows 8.1Pro + Acces MSO 365, however when I try & run same on my Windows 7 Pro +Access 2007 Laptop it will not run. Any suggestions as to the issue?

Reply from Alex Hedley:

Upgrade from 2007, its very buggy.

Mohamed Salim on 4/16/2016: Hello Mr Richard.
I've learned Access from scratch, thanks to you. I was wondering if I can do with SQL the same thing with ADO Recordset function. I really want to focus on one thing and master it rather than distract myself with different options. So is having a full understanding of SQL is enough to have a full control on my recordset?, or I should also know about Recordsets option??
Thank you

Reply from Alex Hedley:

Recordsets are very powerful for traversing data, SQL is the method to get that data, they have different purposes, if you want to have a lot of control knowing both is key.

Anonymous on 6/3/2016: How do you handle a phone list if you have a separate but related table for PhoneT and related to another TypeT which includes such things as "office," "home," "cell," "fax" etc?

Reply from Alex Hedley:

PhoneT could have a TypeID which links to the TypeT.
Combo box on the PhoneF which has the TypeT list.

Richard Wilson on 8/31/2016: If one has a long SELECT statement with a number of fields in it, does the use of "DISTINCT" at the outset make it the functional equivalent of DISTINCTROW? If so, how does one use DISTINCT when there is a long select clause. In my situation, I have a query that makes a"FullName" and it is at the first of the SELECT clause which also has address information lots of other stuff. I only want DISTINCT to apply to FullName. I tried and it doesn't. How should I fix it? I note the SELECT clauses in the video all just contain one field.

Reply from Alex Hedley:

You need to have only the Field you wish to be DISTINCT in the Query, unless you use a subquery.

Eric Michalek on 5/18/2017: Access SQL 1, lesson 2, time index 12min, narration starts with that semi colon at the end and then video will not let me go past that spot. Is it my computer connection or is there a glitch?

Reply from Alex Hedley:

Might have been a connection issue, could you try again please.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP