SQL for Microsoft Access, 2nd Edition

SQL for Microsoft Access, 2nd Edition

by Cecelia L. Allison, Neal A. Berkowitz
Released June 2008
Publisher(s): Jones & Bartlett Learning
ISBN: 9781449613167

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description


SQL for Microsoft Access (2nd 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.
Explore the relational database structure and the basics of SQL. Understand how table joins, unions, and subqueries are used to retrieve records from multiple tables simultaneously. Learn how to filter records and group data. Discover how to create parameter queries that prompt users for data. Test your knowledge and comprehension with the end-of-chapter quizzes and projects.

Table of contents

  1. Contents
  2. Acknowledgments
  3. Introduction
    1. The Importance of SQL in Microsoft Access
    2. Code Interpretations
    3. Companion Files
  4. Chapter 1 The Relational Database Structure
    1. Introduction
    2. Definitions
    3. Early Forms of Data Storage
    4. The Relational Database Structure
      1. Tables
      2. Keys
    5. The Planning Stage
      1. Data Modeling
        1. Entities and Relationships
      2. Normalization
    6. Client/Server Databases
    7. Database Management Systems
    8. Summary
      1. Quiz 1
      2. Project 1
  5. Chapter 2 Structured Query Language and Microsoft Access
    1. Introduction
    2. Definitions
    3. Structured Query Language
      1. SQL Versions
        1. Switching to Version SQL-92 in Microsoft Access 2007
        2. Switching to Version SQL-92 in Microsoft Access 2003
      2. SQL Components
      3. SQL Syntax
    4. The Power of SQL in Microsoft Access
      1. The Query Wizard
      2. The Query Design Tool and SQL View
    5. Opening Microsoft Access and Switching to SQL View
      1. Opening Microsoft Access
      2. Switching to SQL View in Microsoft Access 2007
      3. Switching to SQL View in Microsoft Access 2003
    6. Summary
      1. Quiz 2
      2. Project 2
  6. Chapter 3 Creating Tables and Inserting Records
    1. Introduction
    2. Keywords
    3. Definitions
    4. The Data Definition Language Component
      1. CREATE TABLE Syntax
    5. Data Types
      1. Example 1
    6. Constraints
      1. Example 2
      2. NULL/NOT NULL Constraint
      3. PRIMARY KEY Constraint
        1. Example 3
      4. FOREIGN KEY Constraint
      5. UNIQUE Constraint
      6. Adding Constraints to Existing Tables
        1. Example 4
        2. Example 5
      7. Constraint Syntax
    7. Inserting Records
      1. Example 6
      2. Inserting Data without Specifying Column Names
        1. Example 7
      3. Inserting NULL Values
        1. Example 8
      4. Copying Records from One Table to an Existing Table
        1. Example 9
      5. Copying Records from One Table to a New Table Simultaneously
        1. Example 10
    8. Updating Records
      1. Update a Record with a Text Value
        1. Example 11
      2. Update a Record with a New Calculated Value
        1. Example 12
      3. Update Multiple Columns
        1. Example 13
      4. Update a Column that Contains a Date
        1. Example 14
    9. Deleting Records
      1. Example 15
    10. Summary
      1. Quiz 3
      2. Project 3
  7. Chapter 4 Retrieving Records
    1. Introduction
    2. Keywords
    3. Definitions
    4. The SELECT Statement
      1. Example 1
      2. Example 2
    5. The ORDER BY Clause
      1. Sorting in Descending Order
        1. Example 3
      2. Sorting in Ascending Order
        1. Example 4
      3. Sorting Multiple Columns
        1. Example 5
        2. Example 6
      4. Sorting Using Numbers
        1. Example 7
        2. Example 8
    6. Handling Duplicate Values
      1. The DISTINCT Keyword
        1. Example 9
      2. The DISTINCTROW Keyword
    7. Handling Duplicate Subsets of the Entire Result Collection
      1. The TOP Keyword
        1. Example 10
        2. Example 11
      2. The TOP PERCENT Keywords
        1. Example 12
        2. Example 13
    8. Creating an Alias
      1. Example 14
      2. Create an Alias that Contains a Space
        1. Example 15
    9. Concatenation
      1. Concatenate Multiple Fields and Characters
        1. Example 16
      2. Concatenate Multiple Fields from Multiple Tables
        1. Example 17
    10. Summary
      1. Quiz 4
      2. Project 4
  8. Chapter 5 Filtering Retrieved Records
    1. Introduction
    2. Keywords
    3. Definitions
    4. The WHERE Clause
    5. Comparison Operators
    6. Logical Operators
    7. Operator Precedence
      1. The AND, OR, =, and < Operators
        1. Example 1
      2. The LIKE Operator
        1. Example 2
        2. Example 3
        3. Example 4
        4. Example 5
        5. Example 6
        6. Example 7
        7. Example 8
        8. Example 9
      3. The BETWEEN Operator
        1. Example 10
      4. The IN and NOT Operators
        1. Example 11
        2. Example 12
      5. The IS NULL and IS NOT NULL Operators
        1. Example 13
    8. Summary
      1. Quiz 5
      2. Project 5
  9. Chapter 6 Creating Calculated Fields
    1. Introduction
    2. Keywords
    3. Definitions
    4. Operators and Functions
    5. Arithmetic Operators
      1. Use an Arithmetic Operator with SELECT
        1. Example 1
      2. Use an Arithmetic Operator in the WHERE clause
        1. Example 2
    6. Aggregate Functions
      1. Using the AVG (), FIRST (), LAST (), SUM (), MAX (), and MIN () Functions
        1. Example 3
      2. Using the COUNT () Function
        1. Example 4
    7. String Functions and Operations
      1. Use of the + and &
        1. Example 5
      2. Using the LEFT (), UCASE (), LEN (), and TRIM () Functions
        1. Example 6
      3. Using the MID () and INSTR () Functions
        1. Example 7
    8. Date and Time Functions
      1. Inserting Dates into a Table
        1. Example 8
      2. Using the FORMAT () Function
        1. Example 9
      3. Using the DATE (), TIME (), MONTH (), DAY (), and YEAR () Functions
        1. Example 10
    9. Miscellaneous Functions
      1. Using the CCUR () Function
        1. Example 11
    10. Summary
      1. Quiz 6
      2. Project 6
  10. Chapter 7 Grouping Data
    1. Introduction
    2. Keywords
    3. Definitions
    4. The GROUP BY Clause
      1. Example 1
      2. Using the GROUP BY Clause with the ORDER BY Clause
        1. Example 2
    5. The HAVING Clause
      1. Example 3
      2. Using the HAVING Clause with the WHERE Clause
        1. Example 4
    6. Summary
      1. Quiz 7
      2. Project 7
  11. Chapter 8 Creating Table Joins and Unions
    1. Introduction
    2. Keywords
    3. Definitions
    4. Table Joins — An Overview
      1. Qualification
      2. Inner Join
        1. Example 1
      3. Using the DISTINCTROW Keyword
        1. Example 2
      4. Self Join
        1. Example 3
      5. Nested Join
        1. Example 4
      6. Outer Joins
        1. Right Outer Join
        2. Example 5
        3. Left Outer Join
        4. Example 6
        5. Create a Join that Contains an Aggregate Function
        6. Example 7
    5. UNION and UNION ALL Keywords
      1. UNION
        1. Example 8
      2. UNION ALL
        1. Example 9
    6. Summary
      1. Quiz 8
      2. Project 8
  12. Chapter 9 Creating Subqueries
    1. Introduction
    2. Keywords
    3. Definitions
    4. Subqueries
      1. Correlated and Non-Correlated Subqueries
      2. The IN Subquery
        1. Example 1
      3. The EXISTS Subquery
        1. Example 2
      4. The ANY and SOME Subqueries
        1. Example 3
      5. The ALL Subquery
        1. Example 4
      6. Nested Subqueries
        1. Example 5
      7. Using a Subquery to Find the Second Highest Value
        1. Example 6
    5. Summary
      1. Quiz 9
      2. Project 9
  13. Chapter 10 Creating Views
    1. Introduction
    2. Keywords
    3. Definitions
    4. Creating a View
      1. Example 1
    5. Creating a View Using the CREATE VIEW Keywords in SQL-92
      1. Create a View that Contains a Complex Join
        1. Example 2
      2. Query the CustomersTotalTransactions View
        1. Example 3
    6. Filtering a Record through a View
      1. Example 4
      2. Example 5
    7. Updating a Record through a View
      1. Example 6
    8. Deleting a View
      1. Example 7
    9. Summary
      1. Quiz 10
      2. Project 10
  14. Chapter 11 Table Management and Indexes
    1. Introduction
    2. Keywords
    3. Definitions
    4. Adding a Column to an Existing Table
      1. Example 1
    5. Changing a Column
      1. Example 2
    6. Setting a Default Value for a Column
      1. Example 3
    7. Removing a Column from a Table
      1. Example 4
    8. Removing a Table
    9. Improving Data Retrieval Time Using Indexes
      1. Index Options
      2. Creating an Index
        1. Example 5
      3. Indexing in Descending Order
        1. Example 6
      4. Viewing and Editing Indexes
      5. Deleting an Index
        1. Example 7
    10. Summary
      1. Quiz 11
      2. Project 11
  15. Chapter 12 Temporary Tables vs. Views
    1. Introduction
    2. Definitions
    3. Creating a View
      1. Example 1
    4. Creating a Temporary Table
      1. Example 2
    5. Accessing the Temporary Table
    6. Querying a Temporary Table
      1. Example 3
    7. Indexing a Temporary Table
      1. Example 4
    8. Updating a Temporary Table
      1. Example 5
    9. Copying Records from One Temporary Table to a New Temporary Table Simultaneously
      1. Example 6
    10. Deleting a Temporary Table
      1. Example 7
    11. Why Do We Need Temporary Tables?
    12. Summary
      1. Quiz 12
      2. Project 12
  16. Chapter 13 Parameter Queries
    1. Introduction
    2. Definitions
    3. Parameter Queries
    4. Creating a Simple Query
      1. Example 1
    5. Creating a Parameter Query
      1. Example 2
    6. Customizing Your Dialog Box
      1. Example 3
    7. Creating Multiple Prompts
      1. Example 4
      2. Example 5
    8. Using the LIKE Keyword to Prompt the User
      1. Example 6
    9. Prompting the User for Dates
      1. Example 7
    10. Creating a Button to Prompt the User
      1. Example 8
    11. View the New Button on the Form
    12. SQL Syntax for a Parameter Query in SQL View
      1. Example 9
      2. Example 10
    13. Non-parameter Parameter Queries
    14. Summary
      1. Quiz 13
      2. Project 13
  17. Chapter 14 Integrating SQL Script in VBA Code
    1. Introduction
    2. Definitions
    3. Fixed Queries vs. “On-the-Fly ” Queries
    4. Filtered Recordsets for Forms
    5. Filtered Recordsets for Combo Boxes
    6. Recordsets for Subforms
    7. Report Filters
    8. Summary
  18. Chapter 15 Integrating SQL Script into ASP Code
    1. Introduction
    2. Definitions
    3. Basics
    4. Building the Components
      1. ODBC Connection
      2. Code
    5. Building SQL Statements
    6. Summary
  19. Chapter 16 Access Projects
    1. Introduction
    2. Definitions
    3. Overview
    4. Differences between Access Projects and Access Databases
    5. Project Window
    6. Tables
      1. Notes
    7. Database Diagrams
    8. Queries
    9. Views
    10. Stored Procedures
    11. Functions
    12. Summary
      1. Quiz 16
      2. Project 16
  20. Chapter 17 Concluding Thoughts
    1. Introduction
    2. Common Rules
    3. Summary
  21. Appendix A Answers to Quizzes and Projects
    1. Quiz 1
    2. Project 1
    3. Quiz 2
    4. Project 2
    5. Quiz 3
    6. Project 3
    7. Quiz 4
    8. Project 4
    9. Quiz 5
    10. Project 5
    11. Quiz 6
    12. Project 6
    13. Quiz 7
    14. Project 7
    15. Quiz 8
    16. Project 8
    17. Quiz 9
    18. Project 9
    19. Quiz 10
    20. Project 10
    21. Quiz 11
    22. Project 11
    23. Quiz 12
    24. Project 12
    25. Quiz 13
    26. Project 13
    27. Quiz 16
  22. Appendix B Frequently Used SQL Keywords in Microsoft Access
  23. Appendix C Terms and Definitions
  24. Appendix D Microsoft Access Data Types
  25. Appendix E SQL Script to Create the Tables in This Book
    1. Create and Populate the Activities Table
    2. Create and Populate the Committee1 Table
    3. Create and Populate the Committee2 Table
    4. Create and Populate the Computers Table
    5. Create and Populate the Customers Table
    6. Create and Populate the Customers2 Table
    7. Create and Populate the Departments Table
    8. Create and Populate the Employees Table
    9. Create and Populate the Friends Table
    10. Create and Populate the Manufacturers Table
    11. Create and Populate the Numbers Table
    12. Create and Populate the Products Table
    13. Create and Populate the Sales Table
    14. Create and Populate the Tools Table
    15. Create and Populate the Toys Table
    16. Create and Populate the Transactions Table
  26. Index

Product information

  • Title: SQL for Microsoft Access, 2nd Edition
  • Author(s): Cecelia L. Allison, Neal A. Berkowitz
  • Release date: June 2008
  • Publisher(s): Jones & Bartlett Learning
  • ISBN: 9781449613167