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

Wordware Publishing, Inc., 2005. — 368 p. — ISBN: 1556220928, 9781556220920This book unlocks the power of Structured Query Language in Microsoft Access. It guides the reader through step-by=step realistic examples of creating databases in Microsoft Access, using SQL script to create table, adding records to tables and retrieving and managing records.The Relational Database StructureEarly Forms of Data Storage
The Relational Database Structure
Tables
Keys
The Planning Stage
Data Modeling
Entities and Relationships
Normalization
Client/Server Databases
Database Management SystemsStructured Query Language and Microsoft Access
Structured Query Language
SQL Versions
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 ViewCreating Tables and Inserting RecordsThe Data Definition Language Component
CREATE TABLE Syntax
Data Types
Constraints
Example
NULL/NOT NULL Constraint
PRIMARY KEY Constraint
Example
FOREIGN KEY Constraint
UNIQUE Constraint
Adding Constraints to Existing Tables
Example
Example
Constraint Syntax
Inserting Records
Example
Inserting Data without Specifying Column Names
Example
Inserting NULL Values
Example
Copying Records from One Table to an Existing Table
Example
Copying Records from One Table to a New Table Simultaneously
Example
Updating Records
Example
Deleting Records
ExampleRetrieving Records
The SELECT Statement
Example
Example
The ORDER BY Clause
Sorting in Descending Order
Example
Sorting in Ascending Order
Example
Sorting Multiple Columns
Example
Sorting Using Numbers
Example
Example
Handling Duplicate Values
The DISTINCT Keyword
Example
The DISTINCTROW Keyword
The TOP Keyword
Example
Example
The TOP PERCENT Keywords
Example
Example
Creating an Alias
Example
Concatenation
ExampleFiltering Retrieved RecordsThe WHERE Clause
Comparison Operators
Logical Operators
Operator Precedence
The AND, OR, =, and < Operators
Example
The LIKE Operator
Example
Example
Example
Example
Example
Example
Example
Example
The BETWEEN Operator
Example
The IN and NOT Operators
Example
The IS NULL and IS NOT NULL Operators
ExampleCreating Calculated FieldsOperators and Functions
Arithmetic Operators
Example
Aggregate Functions
Using the AVG (), FIRST (), LAST (), SUM (), MAX ()
and MIN () Functions
Example
Using the COUNT () Function
Example
String Functions and Operations
Use of the + and
Using the LEFT (), UCASE (), LEN (), and TRIM () Functions
Example
Using the MID () and INSTR () Functions
Example
Date and Time Functions
Inserting Dates into a Table
Example
Using the FORMAT () Function
Example
Using the DATE (), TIME (), MONTH (), DAY ()
and YEAR () Functions
Example
Miscellaneous FunctionsGrouping DataThe GROUP BY Clause
Example
Using the GROUP BY Clause with the ORDER BY Clause
Example
The HAVING Clause
Example
Using the HAVING Clause with the WHERE Clause
ExampleCreating Table Joins and UnionsTable Joins — An Overview
Qualification
Inner Join
Example
Using the DISTINCTROW Keyword
Example
Self Join
Example
Nested Join
Example
Outer Joins
Right Outer Join
Example
Left Outer Join
Example
UNION and UNION ALL Keywords
UNION
Example
UNION ALL
ExampleCreating SubqueriesSubqueries
Correlated and Non-Correlated Subqueries
The IN Subquery
Example
The EXISTS Subquery
Example
The ANY and SOME Subqueries
Example
The ALL Subquery
Example
Nested Subqueries
ExampleCreating ViewsCreating a View
Example
Creating a View Using the CREATE VIEW Keywords in SQL
Setting the SQL Version for a Current Database
Setting the SQL Version as the Default Setting for New Databases
Filtering a Record Through a View
Example
Example
Updating a Record Through a View
Example
Deleting a View
ExampleTable Management and IndexesAdding a Column to an Existing Table
Example
Changing a Column
Example
Setting a Default Value for a Column
Example
Removing a Column from a Table
Example
Removing a Table
Improving Data Retrieval Time Using Indexes
Index Options
Creating an Index
Example
Viewing and Editing Indexes
Deleting an Index
ExampleTemporary Tables vs. ViewsCreating a View
Example
Creating a Temporary Table
Example
Accessing the Temporary Table
Querying a Temporary Table
Example
Indexing a Temporary Table
Example
Updating a Temporary Table
Example
Deleting a Temporary Table
ExampleParameter QueriesParameter Queries
Creating a Simple Query
Example
Creating a Parameter Query
Example
Customizing Your Dialog Box
Example
Creating Multiple Prompts
Example
Example
Using the LIKE Keyword to Prompt the User
Example
Prompting the User for Dates
Example
Creating a Button to Prompt the User
Example
SQL Syntax for a Parameter Query in SQL View
ExampleIntegrating SQL Script in VBA CodeFixed Queries vs. “On-the-Fly” Queries
Filtered Recordsets for Forms
Filtered Recordsets for Combo Boxes
Recordsets for Subforms
Report FiltersIntegrating SQL Script into ASP CodeBasics
Building the Components
ODBC Connection
Code
Building SQL StatementsAccess ProjectsOverview
Differences between Access Projects and Access Databases
Project Window
Tables
Database Diagrams
Queries
Views
Stored Procedures
FunctionsConcluding ThoughtsCommon RulesAppendix A Answers to Quizzes and Projects
Appendix B Frequently Used SQL Keywords in Microsoft Access
Appendix C Terms and Definitions
Appendix D Microsoft Access Data Types
Appendix E SQL Script to Create the Tables in This Book