book

SQL for Microsoft Access, 2nd Edition

by Cecelia L. Allison, Neal A. Berkowitz

June 2008

Intermediate to advanced

374 pages

6h 10m

English

Jones & Bartlett Learning

Read now

Unlock full access

Contents
Acknowledgments
Introduction
The Importance of SQL in Microsoft AccessCode InterpretationsCompanion Files
Chapter 1 The Relational Database Structure
IntroductionDefinitionsEarly Forms of Data StorageThe Relational Database StructureTablesKeysThe Planning StageData ModelingEntities and RelationshipsNormalizationClient/Server DatabasesDatabase Management SystemsSummaryQuiz 1Project 1
Chapter 2 Structured Query Language and Microsoft Access
IntroductionDefinitionsStructured Query LanguageSQL VersionsSwitching to Version SQL-92 in Microsoft Access 2007Switching to Version SQL-92 in Microsoft Access 2003SQL ComponentsSQL SyntaxThe Power of SQL in Microsoft AccessThe Query WizardThe Query Design Tool and SQL ViewOpening Microsoft Access and Switching to SQL ViewOpening Microsoft AccessSwitching to SQL View in Microsoft Access 2007Switching to SQL View in Microsoft Access 2003SummaryQuiz 2Project 2
Chapter 3 Creating Tables and Inserting Records
IntroductionKeywordsDefinitionsThe Data Definition Language ComponentCREATE TABLE SyntaxData TypesExample 1ConstraintsExample 2NULL/NOT NULL ConstraintPRIMARY KEY ConstraintExample 3FOREIGN KEY ConstraintUNIQUE ConstraintAdding Constraints to Existing TablesExample 4Example 5Constraint SyntaxInserting RecordsExample 6Inserting Data without Specifying Column NamesExample 7Inserting NULL ValuesExample 8Copying Records from One Table to an Existing TableExample 9Copying Records from One Table to a New Table SimultaneouslyExample 10Updating RecordsUpdate a Record with a Text ValueExample 11Update a Record with a New Calculated ValueExample 12Update Multiple ColumnsExample 13Update a Column that Contains a DateExample 14Deleting RecordsExample 15SummaryQuiz 3Project 3
Chapter 4 Retrieving Records
IntroductionKeywordsDefinitionsThe SELECT StatementExample 1Example 2The ORDER BY ClauseSorting in Descending OrderExample 3Sorting in Ascending OrderExample 4Sorting Multiple ColumnsExample 5Example 6Sorting Using NumbersExample 7Example 8Handling Duplicate ValuesThe DISTINCT KeywordExample 9The DISTINCTROW KeywordHandling Duplicate Subsets of the Entire Result CollectionThe TOP KeywordExample 10Example 11The TOP PERCENT KeywordsExample 12Example 13Creating an AliasExample 14Create an Alias that Contains a SpaceExample 15ConcatenationConcatenate Multiple Fields and CharactersExample 16Concatenate Multiple Fields from Multiple TablesExample 17SummaryQuiz 4Project 4
Chapter 5 Filtering Retrieved Records
IntroductionKeywordsDefinitionsThe WHERE ClauseComparison OperatorsLogical OperatorsOperator PrecedenceThe AND, OR, =, and < OperatorsExample 1The LIKE OperatorExample 2Example 3Example 4Example 5Example 6Example 7Example 8Example 9The BETWEEN OperatorExample 10The IN and NOT OperatorsExample 11Example 12The IS NULL and IS NOT NULL OperatorsExample 13SummaryQuiz 5Project 5
Chapter 6 Creating Calculated Fields
IntroductionKeywordsDefinitionsOperators and FunctionsArithmetic OperatorsUse an Arithmetic Operator with SELECTExample 1Use an Arithmetic Operator in the WHERE clauseExample 2Aggregate FunctionsUsing the AVG (), FIRST (), LAST (), SUM (), MAX (), and MIN () FunctionsExample 3Using the COUNT () FunctionExample 4String Functions and OperationsUse of the + and &Example 5Using the LEFT (), UCASE (), LEN (), and TRIM () FunctionsExample 6Using the MID () and INSTR () FunctionsExample 7Date and Time FunctionsInserting Dates into a TableExample 8Using the FORMAT () FunctionExample 9Using the DATE (), TIME (), MONTH (), DAY (), and YEAR () FunctionsExample 10Miscellaneous FunctionsUsing the CCUR () FunctionExample 11SummaryQuiz 6Project 6
Chapter 7 Grouping Data
IntroductionKeywordsDefinitionsThe GROUP BY ClauseExample 1Using the GROUP BY Clause with the ORDER BY ClauseExample 2The HAVING ClauseExample 3Using the HAVING Clause with the WHERE ClauseExample 4SummaryQuiz 7Project 7

Chapter 8 Creating Table Joins and Unions
IntroductionKeywordsDefinitionsTable Joins — An OverviewQualificationInner JoinExample 1Using the DISTINCTROW KeywordExample 2Self JoinExample 3Nested JoinExample 4Outer JoinsRight Outer JoinExample 5Left Outer JoinExample 6Create a Join that Contains an Aggregate FunctionExample 7UNION and UNION ALL KeywordsUNIONExample 8UNION ALLExample 9SummaryQuiz 8Project 8
Chapter 9 Creating Subqueries
IntroductionKeywordsDefinitionsSubqueriesCorrelated and Non-Correlated SubqueriesThe IN SubqueryExample 1The EXISTS SubqueryExample 2The ANY and SOME SubqueriesExample 3The ALL SubqueryExample 4Nested SubqueriesExample 5Using a Subquery to Find the Second Highest ValueExample 6SummaryQuiz 9Project 9
Chapter 10 Creating Views
IntroductionKeywordsDefinitionsCreating a ViewExample 1Creating a View Using the CREATE VIEW Keywords in SQL-92Create a View that Contains a Complex JoinExample 2Query the CustomersTotalTransactions ViewExample 3Filtering a Record through a ViewExample 4Example 5Updating a Record through a ViewExample 6Deleting a ViewExample 7SummaryQuiz 10Project 10
Chapter 11 Table Management and Indexes
IntroductionKeywordsDefinitionsAdding a Column to an Existing TableExample 1Changing a ColumnExample 2Setting a Default Value for a ColumnExample 3Removing a Column from a TableExample 4Removing a TableImproving Data Retrieval Time Using IndexesIndex OptionsCreating an IndexExample 5Indexing in Descending OrderExample 6Viewing and Editing IndexesDeleting an IndexExample 7SummaryQuiz 11Project 11
Chapter 12 Temporary Tables vs. Views
IntroductionDefinitionsCreating a ViewExample 1Creating a Temporary TableExample 2Accessing the Temporary TableQuerying a Temporary TableExample 3Indexing a Temporary TableExample 4Updating a Temporary TableExample 5Copying Records from One Temporary Table to a New Temporary Table SimultaneouslyExample 6Deleting a Temporary TableExample 7Why Do We Need Temporary Tables?SummaryQuiz 12Project 12
Chapter 13 Parameter Queries
IntroductionDefinitionsParameter QueriesCreating a Simple QueryExample 1Creating a Parameter QueryExample 2Customizing Your Dialog BoxExample 3Creating Multiple PromptsExample 4Example 5Using the LIKE Keyword to Prompt the UserExample 6Prompting the User for DatesExample 7Creating a Button to Prompt the UserExample 8View the New Button on the FormSQL Syntax for a Parameter Query in SQL ViewExample 9Example 10Non-parameter Parameter QueriesSummaryQuiz 13Project 13
Chapter 14 Integrating SQL Script in VBA Code
IntroductionDefinitionsFixed Queries vs. “On-the-Fly ” QueriesFiltered Recordsets for FormsFiltered Recordsets for Combo BoxesRecordsets for SubformsReport FiltersSummary
Chapter 15 Integrating SQL Script into ASP Code
IntroductionDefinitionsBasicsBuilding the ComponentsODBC ConnectionCodeBuilding SQL StatementsSummary
Chapter 16 Access Projects
IntroductionDefinitionsOverviewDifferences between Access Projects and Access DatabasesProject WindowTablesNotesDatabase DiagramsQueriesViewsStored ProceduresFunctionsSummaryQuiz 16Project 16
Chapter 17 Concluding Thoughts
IntroductionCommon RulesSummary
Appendix A Answers to Quizzes and Projects
Quiz 1Project 1Quiz 2Project 2Quiz 3Project 3Quiz 4Project 4Quiz 5Project 5Quiz 6Project 6Quiz 7Project 7Quiz 8Project 8Quiz 9Project 9Quiz 10Project 10Quiz 11Project 11Quiz 12Project 12Quiz 13Project 13Quiz 16
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
Create and Populate the Activities TableCreate and Populate the Committee1 TableCreate and Populate the Committee2 TableCreate and Populate the Computers TableCreate and Populate the Customers TableCreate and Populate the Customers2 TableCreate and Populate the Departments TableCreate and Populate the Employees TableCreate and Populate the Friends TableCreate and Populate the Manufacturers TableCreate and Populate the Numbers TableCreate and Populate the Products TableCreate and Populate the Sales TableCreate and Populate the Tools TableCreate and Populate the Toys TableCreate and Populate the Transactions Table
Index

Overview

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.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft SQL Server 2019: A Beginner's Guide, Seventh Edition, 7th Edition

Publisher Resources

ISBN: 9781598220605

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills