Contents

 

Acknowledgments 

Introduction 

 

Chapter 1 The Relational Database Structure 

Introduction 

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 

Introduction 

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 

Summary 

Chapter 3 Creating Tables and Inserting Records 

Introduction 

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 

Summary 

Chapter 4 Retrieving Records 

Introduction 

The SELECT Statement 

Example 1 

Example 2 

The ORDER BY Clause 

Sorting in Descending Order 

Example 3 

Sorting in Ascending Order 

Example 4 

Sorting Multiple Columns 

Example 5 

Example 6 

Sorting Using Numbers 

Example 7 

Example 8 

Handling Duplicate Values 

The DISTINCT Keyword 

Example 9 

The DISTINCTROW Keyword 

Handling Duplicate Subsets of the Entire Result Collection 

The TOP Keyword 

Example 10 

Example 11 

The TOP PERCENT Keywords 

Example 12 

Example 13 

Creating an Alias 

Example 14 

Create an Alias that Contains a Space 

Example 15 

Concatenation 

Concatenate Multiple Fields and Characters 

Example 16 

Concatenate Multiple Fields from Multiple Tables 

Example 17 

Summary 

Chapter 5 Filtering Retrieved Records 

Introduction 

The WHERE Clause 

Comparison Operators 

Logical Operators 

Operator Precedence 

The AND, OR, =, and < Operators 

Example 1 

The LIKE Operator 

Example 2 

Example 3 

Example 4 

Example 5 

Example 6 

Example 7 

Example 8 

Example 9 

The BETWEEN Operator 

Example 10 

The IN and NOT Operators 

Example 11 

Example 12 

The IS NULL and IS NOT NULL Operators 

Example 13 

Summary 

Chapter 6 Creating Calculated Fields 

Introduction 

Operators and Functions 

Arithmetic Operators 

Use an Arithmetic Operator with SELECT 

Example 1 

Use an Arithmetic Operator in the WHERE clause 

Example 2 

Aggregate Functions 

Using the AVG (), FIRST (), LAST (), SUM (), MAX (), and MIN () Functions 

Example 3 

Using the COUNT () Function 

Example 4 

String Functions and Operations 

Use of the + and & 

Example 5 

Using the LEFT (), UCASE (), LEN (), and TRIM () Functions 

Example 6 

Using the MID () and INSTR () Functions 

Example 7 

Date and Time Functions 

Inserting Dates into a Table 

Example 8 

Using the FORMAT () Function 

Example 9 

Using the DATE (), TIME (), MONTH (), DAY (), and YEAR () Functions 

Example 10 

Miscellaneous Functions 

Using the CCUR () Function 

Example 11 

Summary 

Chapter 7 Grouping Data 

Introduction 

The GROUP BY Clause 

Example 1 

Using the GROUP BY Clause with the ORDER BY Clause 

Example 2 

The HAVING Clause 

Example 3 

Using the HAVING Clause with the WHERE Clause 

Example 4 

Summary 

Chapter 8 Creating Table Joins and Unions 

Introduction 

Table Joins—An Overview 

Qualification 

Inner Join 

Example 1 

Using the DISTINCTROW Keyword 

Example 2 

Self Join 

Example 3 

Nested Join 

Example 4 

Outer Joins 

Right Outer Join 

Example 5 

Left Outer Join 

Example 6 

Create a Join that Contains an Aggregate Function 

Example 7 

UNION and UNION ALL Keywords 

UNION 

Example 8 

UNION ALL 

Example 9 

Summary 

Chapter 9 Creating Subqueries 

Introduction 

Subqueries 

Correlated and Non-Correlated Subqueries 

The IN Subquery 

Example 1 

The EXISTS Subquery 

Example 2 

The ANY and SOME Subqueries 

Example 3 

The ALL Subquery 

Example 4 

Nested Subqueries 

Example 5 

Using a Subquery to Find the Second Highest Value 

Example 6 

Summary 

Chapter 10 CreatingViews 

Introduction 

Creating a View 

Example 1 

Creating a View Using the CREATE VIEW Keywords in SQL-92 

Create a View that Contains a Complex Join 

Example 2 

Query the CustomersTotalTransactions View 

Example 3 

Filtering a Record through a View 

Example 4 

Example 5 

Updating a Record through a View 

Example 6 

Deleting a View 

Example 7 

Summary 

Chapter 11 Table Management and Indexes 

Introduction 

Adding a Column to an Existing Table 

Example 1 

Changing a Column 

Example 2 

Setting a Default Value for a Column 

Example 3 

Removing a Column from a Table 

Example 4 

Removing a Table 

Improving Data Retrieval Time Using Indexes 

Index Options 

Creating an Index 

Example 5 

Indexing in Descending Order 

Example 6 

Viewing and Editing Indexes 

Deleting an Index 

Example 7 

Summary 

Chapter 12 Temporary Tables vs. Views 

Introduction 

Creating a View 

Example 1 

Creating a Temporary Table 

Example 2 

Accessing the Temporary Table 

Querying a Temporary Table 

Example 3 

Indexing a Temporary Table 

Example 4 

Updating a Temporary Table 

Example 5 

Copying Records from One Temporary Table to a New Temporary Table Simultaneously 

Example 6 

Deleting a Temporary Table 

Example 7 

Why Do We Need Temporary Tables? 

Summary 

Chapter 13 Parameter Queries 

Introduction 

Parameter Queries 

Creating a Simple Query 

Example 1 

Creating a Parameter Query 

Example 2 

Customizing Your Dialog Box 

Example 3 

Creating Multiple Prompts 

Example 4 

Example 5 

Using the LIKE Keyword to Prompt the User 

Example 6 

Prompting the User for Dates 

Example 7 

Creating a Button to Prompt the User 

Example 8 

View the New Button on the Form 

SQL Syntax for a Parameter Query in SQL View 

Example 9 

Example 10 

Non-parameter Parameter Queries 

Summary 

Chapter 14 Integrating SQL Script in VBA Code 

Introduction 

Fixed Queries vs. “On-the-Fly” Queries 

Filtered Recordsets for Forms 

Filtered Recordsets for Combo Boxes 

Recordsets for Subforms 

Report Filters 

Summary 

Chapter 15 Integrating SQL Script into ASP Code 

Introduction 

Basics 

Building the Components 

ODBC Connection 

Code 

Building SQL Statements 

Summary 

Chapter 16 Access Projects 

Introduction 

Overview 

Differences between Access Projects and Access Databases 

Project Window 

Tables 

Database Diagrams 

Queries 

Views 

Stored Procedures 

Functions 

Summary 

Chapter 17 Concluding Thoughts 

Introduction 

Common Rules 

Summary 

 

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 

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset