microsoft access 2010: advanced course 01 -...
TRANSCRIPT
Microsoft Access 2010: Advanced
Course 01 - Querying with SQL
Slide 1
Topic A
SQL and Access
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 2
Sample SQL Statements
SELECT * FROM Product;
SELECT Product.ProductID,
OrderItem.ItemQuantity
FROM Product, OrderItem;
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 3
A Query in SQL View
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 4
Topic B
Writing SQL Statements
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 5
Writing a SQL Statement
1. Create a query in Design view
2. Switch to SQL view
3. In the query window, write the SQL
statement
4. Save the query with a relevant name
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 6
Syntax for WHERE Clause
SELECT FieldName
FROM DataSource
WHERE Criteria;
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 7
Aliases
SELECT lngRetailerID as Vendor,
strAddr1 as Address,
strPhone as Contact
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 8
Syntax to Display Matched Records
SELECT DataSource1.FieldName,
DataSource2.FieldName
FROM DataSource1, DataSource2
WHERE DataSource1.CommonField=
DataSource2.CommonField;
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 9
Syntax to Display Unmatched Records
SELECT DataSource1.FieldName
FROM DataSource1
WHERE CommonField not in
(SELECT
DataSource2.CommonField
FROM DataSource2);
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 10
Context-Sensitive Help
1. Open the query in SQL view
2. Place the insertion point in the
keyword
3. Press F1 to open the Access Help
window
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 11
Topic C
Attaching SQL Queries to Controls
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 12
SQL-Specific Queries
Union query
Pass-through query
Data-definition query
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 13
Create a Union Query
1. Create a query in Design view
2. Close the Show Table dialog box
3. On the Design tab, click Union to
open the SQL window
4. Enter the SQL statement for the
query in the window
5. Save and run the query
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 14
Attach a SQL Query to a Button
1. Create a SQL query and save it
2. Open the form in Design view
3. Create a command button
4. In the wizard, under Categories, select
Miscellaneous
5. Under Actions, select Run Query
6. Select the query you created
7. Select an option to display on the button
8. Edit the button name and click Finish
9. Update and close the form
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Slide 15
Command Button Wizard
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
Review Questions:
1. What portions of a simple SQL statement are NOT optional?
A. SELECT, FROM and WHERE
B. SELECT and FROM
C. SELECT and WHERE
D. SELECT, FROM and ORDER BY
2. What wildcard character can be used in the SELECT statement to display all of the fields from a
table?
A. ?
B. ALL
C. *
D. %
3. Which of the following is NOT a category of SQL commands?
A. File system control commands
B. Data definition language
C. Data manipulation language
D. Transaction control commands
4. Which character is used to end a SQL statement?
A. Colon
B. Comma
C. Semicolon
D. Pound sign
5. True or False: In a SQL statement, the FROM clause specifies the data source for the query.
A. True
B. False
Answer Key:
1. B
In a simple SQL statement, SELECT and FROM are the two mandatory portions.
2. C
The asterisk (*) can be used in the SELECT statement to display all of the fields from a table.
3. A
The SQL language consists of data definition language, data manipulation language, transaction
control commands and session control commands.
4. C
The semicolon is used to end a SQL statement.
5. A
True. In a SQL statement, the FROM clause specifies the data source for the query.