Allison C.L., Berkowitz N.A. SQL for Microsoft Access

2nd ed. — Jones & Bartlett Publishers, 2008. — 292 p. — ISBN: 1598220608, 9781598220605SQL for Microsoft Access, Second Edition provides a guide to getting the most out of Microsoft Access through the use of Structured Query Language. Step-by-step examples demonstrate how to use SQL script to create tables, add records to tables, and retrieve and manage records. Readers will also learn about calculated fields, Access projects, and the integration of SQL script in VBA and ASP code.The Importance of SQL in Microsoft Access
Code Interpretations
Companion FilesThe Relational Database StructureDefinitions
Early Forms of Data Storage
The Relational Database Structure
Tables
Keys
The Planning Stage
Data Modeling
Entities and Relationships
Normalization
Client/Server Databases
Database Management SystemsQuiz 1
Project 1Structured Query Language and Microsoft AccessDefinitions
Structured Query Language
SQL Versions
Switching to Version SQL-92 in Microsoft Access 2007
Switching to Version SQL-92 in Microsoft Access 2003
SQL Components
SQL Syntax
The Power of SQL in Microsoft Access
The Query Wizard
The Query Design Tool and SQL View
Opening Microsoft Access and Switching to SQL View
Opening Microsoft Access
Switching to SQL View in Microsoft Access 2007
Switching to SQL View in Microsoft Access 2003Quiz 2
Project 2Creating Tables and Inserting RecordsKeywords
Definitions
The Data Definition Language Component
CREATE TABLE Syntax
Data Types
Constraints
NULL/NOT NULL Constraint
PRIMARY KEY Constraint
FOREIGN KEY Constraint
UNIQUE Constraint
Adding Constraints to Existing Tables
Constraint Syntax
Inserting Records
Inserting Data without Specifying Column Names
Inserting NULL Values
Copying Records from One Table to an Existing Table
Copying Records from One Table to a New Table Simultaneously
Updating Records
Update a Record with a Text Value
Update a Record with a New Calculated Value
Update Multiple Columns
Update a Column that Contains a Date
Deleting RecordsQuiz 3
Project 3Retrieving RecordsKeywords
Definitions
The SELECT Statement
The ORDER BY Clause
Sorting in Descending Order
Sorting in Ascending Order
Sorting Multiple Columns
Sorting Using Numbers
Handling Duplicate Values
The DISTINCT Keyword
The DISTINCTROW Keyword
Handling Duplicate Subsets of the Entire Result Collection
The TOP Keyword
The TOP PERCENT Keywords
Creating an Alias
Create an Alias that Contains a Space
Concatenation
Concatenate Multiple Fields and Characters
Concatenate Multiple Fields from Multiple TablesQuiz 4
Project 4Filtering Retrieved RecordsKeywords
Definitions
The WHERE Clause
Comparison Operators
Logical Operators
Operator Precedence
The AND, OR, =, and < Operators
The LIKE Operator
The BETWEEN Operator
The IN and NOT Operators
The IS NULL and IS NOT NULL OperatorsQuiz 5
Project 5Creating Calculated FieldsKeywords
Definitions
Operators and Functions
Arithmetic Operators
Use an Arithmetic Operator with SELECT
Use an Arithmetic Operator in the WHERE clause
Aggregate Functions
Using the AVG (), FIRST (), LAST (), SUM (), MAX (), and MIN () Functions
Using the COUNT () Function
String Functions and Operations
Use of the + and &
Using the LEFT (), UCASE (), LEN (), and TRIM () Functions
Using the MID () and INSTR () Functions
Date and Time Functions
Inserting Dates into a Table
Using the FORMAT () Function
Using the DATE (), TIME (), MONTH (), DAY (), and YEAR () Functions
Miscellaneous Functions
Using the CCUR () FunctionQuiz 6
Project 6Grouping DataKeywords
Definitions
The GROUP BY Clause
Using the GROUP BY Clause with the ORDER BY Clause
The HAVING Clause
Using the HAVING Clause with the WHERE ClauseQuiz 7
Project 7Creating Table Joins and UnionsKeywords
Definitions
Table Joins — An Overview
Qualification
Inner Join
Using the DISTINCTROW Keyword
Self Join
Nested Join
Outer Joins
Right Outer Join
Left Outer Join
Create a Join that Contains an Aggregate Function
UNION and UNION ALL Keywords
UNION
UNION ALLQuiz 8
Project 8Creating SubqueriesKeywords
Definitions
Subqueries
Correlated and Non-Correlated Subqueries
The IN Subquery
The EXISTS Subquery
The ANY and SOME Subqueries
The ALL Subquery
Nested Subqueries
Using a Subquery to Find the Second Highest ValueQuiz 9
Project 9Creating ViewsKeywords
Definitions
Creating a View
Creating a View Using the CREATE VIEW Keywords in SQL-92
Create a View that Contains a Complex Join
Query the CustomersTotalTransactions View
Filtering a Record through a View
Updating a Record through a View
Deleting a ViewQuiz 10
Project 10Table Management and IndexesKeywords
Definitions
Adding a Column to an Existing Table
Changing a Column
Setting a Default Value for a Column
Removing a Column from a Table
Removing a Table
Improving Data Retrieval Time Using Indexes
Index Options
Creating an Index
Indexing in Descending Order
Viewing and Editing Indexes
Deleting an IndexQuiz 11
Project 11Temporary Tables vs. ViewsDefinitions
Creating a View
Creating a Temporary Table
Accessing the Temporary Table
Querying a Temporary Table
Indexing a Temporary Table
Updating a Temporary Table
Copying Records from One Temporary Table to a New Temporary Table Simultaneously
Deleting a Temporary Table
Why Do We Need Temporary Tables?Quiz 12
Project 12Parameter QueriesDefinitions
Parameter Queries
Creating a Simple Query
Creating a Parameter Query
Customizing Your Dialog Box
Creating Multiple Prompts
Using the LIKE Keyword to Prompt the User
Prompting the User for Dates
Creating a Button to Prompt the User
View the New Button on the Form
SQL Syntax for a Parameter Query in SQL View
Non-parameter Parameter QueriesQuiz 13
Project 13Integrating SQL Script in VBA CodeDefinitions
Fixed Queries vs. “On-the-Fly ” Queries
Filtered Recordsets for Forms
Filtered Recordsets for Combo Boxes
Recordsets for Subforms
Report FiltersIntegrating SQL Script into ASP CodeDefinitions
Basics
Building the Components
ODBC Connection
Code
Building SQL StatementsAccess ProjectsDefinitions
Overview
Differences between Access Projects and Access Databases
Project Window
Tables
Notes
Database Diagrams
Queries
Views
Stored Procedures
FunctionsQuiz 16
Project 16Concluding ThoughtsCommon RulesAnswers to Quizzes and Projects
Frequently Used SQL Keywords in Microsoft Access
Terms and Definitions
Microsoft Access Data Types
SQL Script to Create the Tables in This Book
Create and Populate the Activities Table
Create and Populate the Committee1 Table
Create and Populate the Committee2 Table
Create and Populate the Computers Table
Create and Populate the Customers Table
Create and Populate the Customers2 Table
Create and Populate the Departments Table
Create and Populate the Employees Table
Create and Populate the Friends Table
Create and Populate the Manufacturers Table
Create and Populate the Numbers Table
Create and Populate the Products Table
Create and Populate the Sales Table
Create and Populate the Tools Table
Create and Populate the Toys Table
Create and Populate the Transactions Table