Acknowledgments
Introduction
Chapter 1 The Relational Database Structure
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 Systems
Summary
Chapter 2 Structured Query Language and Microsoft Access
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 2003
Chapter 3 Creating Tables and Inserting Records
The Data Definition Language Component
CREATE TABLE Syntax
Data Types
Example 1
Constraints
Example 2
NULL/NOT NULL Constraint
PRIMARY KEY Constraint
Example 3
FOREIGN KEY Constraint
UNIQUE Constraint
Adding Constraints to Existing Tables
Example 4
Example 5
Constraint Syntax
Inserting Records
Example 6
Inserting Data without Specifying Column Names
Example 7
Inserting NULL Values
Example 8
Copying Records from One Table to an Existing Table
Example 9
Copying Records from One Table to a New Table Simultaneously
Example 10
Updating Records
Update a Record with a Text Value
Example 11
Update a Record with a New Calculated Value
Example 12
Update Multiple Columns
Example 13
Update a Column that Contains a Date
Example 14
Deleting Records
Example 15
Chapter 4 Retrieving Records
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
Example 16
Concatenate Multiple Fields from Multiple Tables
Example 17
Chapter 5 Filtering Retrieved Records
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 Operators
Chapter 6 Creating Calculated Fields
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 () Function
Chapter 7 Grouping Data
The GROUP BY Clause
Using the GROUP BY Clause with the ORDER BY Clause
The HAVING Clause
Using the HAVING Clause with the WHERE Clause
Chapter 8 Creating Table Joins and Unions
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 ALL
Chapter 9 Creating Subqueries
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 Value
Chapter 10 CreatingViews
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 View
Chapter 11 Table Management and Indexes
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 Index
Chapter 12 Temporary Tables vs. Views
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?
Chapter 13 Parameter Queries
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 Queries
Chapter 14 Integrating SQL Script in VBA Code
Fixed Queries vs. “On-the-Fly” Queries
Filtered Recordsets for Forms
Filtered Recordsets for Combo Boxes
Recordsets for Subforms
Report Filters
Chapter 15 Integrating SQL Script into ASP Code
Basics
Building the Components
ODBC Connection
Code
Building SQL Statements
Chapter 16 Access Projects
Overview
Differences between Access Projects and Access Databases
Project Window
Database Diagrams
Queries
Views
Stored Procedures
Functions
Chapter 17 Concluding Thoughts
Common Rules
Appendix 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
Index