SQL for Microsoft Access

by Allison, Cecelia L.; Berkowitz, Neal A.

Edition: 2nd

ISBN13: 9781598220605

ISBN10: 1598220608

Format: Paperback

Pub. Date: 2008-07-04

Publisher(s): Jones & Bartlett Learning

Other versions by this Author

List Price: ~~$41.95~~

Buy New

Usually Ships in 8 - 10 Business Days.

$41.91

Add to Cart

Rent Textbook

Select for Price

Add to Cart

There was a problem. Please try again later.

Rent Digital

Online:60 Days access
Downloadable:60 Days

$22.66

Online:90 Days access
Downloadable:90 Days

$25.18

Online:180 Days access
Downloadable:180 Days

$27.70

Online:1825 Days access
Downloadable:Lifetime Access

$32.72

*To support the delivery of the digital material to you, a digital delivery fee of $3.99 will be charged on each digital item.

$27.70*

Add to Cart

Used Textbook

We're Sorry
Sold Out

Buy from our Marketplace starting at $16.09

Summary

This book unlocks the power of Structured Query Language (SQL) in Microsoft Access. It guides the reader through step-by-step examples that use SQL script in Microsoft Access to create tables, add records to tables, and retrieve and manage records using simple to advanced data manipulation techniques including combined queries, joined queries, nested queries, views, and temporary tables. The reader also discovers how SQL script can be implemented within VBA and ASP code. Beginners learn about early forms of data storage, the structure of a relational database, and how to plan the design of a database. The reader will learn how to use a database design technique called normalisation, and will also encounter the ERD data model, which is used to demonstrate how to organise and document data that will be stored in the database. Examples of the one-to-one relationship, many-to-one relationship, and the many-to-many relationship are also discussed. Intermediate Microsoft Access users will learn to use the SQL View interface within Microsoft Access to create simple to advanced SQL queries. They will learn to use SQL script to create a table from the ground up that includes columns with data types, constraints, field sizes, indexes, and default values. They will also learn to use SQL script to insert records into a table and to create customized queries that retrieve records from a single table or multiple tables simultaneously. Other topics include using SQL script to create calculated fields, manipulate strings, compute dates, create temporary tables, create views, and create parameter queries that prompt the user to enter data. The advanced reader will discover how SQL script can be integrated into VBA and ASP code.

Author Biography

Cecelia L. Allison is an author and on-line professor, She is offered through hundreds of educational facilities, and also teaches on-line technical courses for ITT Technical Institute and Park University Neal A. Berkowitz is a consultant, author, web designer, and system analyst and architect, with more than 25 years of experience covering all aspects of computers

Acknowledgments	p. xiii
Introduction	p. xiv
The Relational Database Structure	p. 1
Introduction	p. 1
Early Forms of Data Storage	p. 3
The Relational Database Structure	p. 4
Tables	p. 4
Keys	p. 5
The Planning Stage	p. 6
Data Modeling	p. 7
Entities and Relationships	p. 7
Normalization	p. 10
Client/Server Databases	p. 11
Database Management Systems	p. 11
Summary	p. 12
Structured Query Language and Microsoft Access	p. 15
Introduction	p. 15
Structured Query Language	p. 16
SQL Versions	p. 16
Switching to Version SQL-92 in Microsoft Access 2007	p. 17
Switching to Version SQL-92 in Microsoft Access 2003	p. 17
SQL Components	p. 17
SQL Syntax	p. 18
The Power of SQL in Microsoft Access	p. 19
The Query Wizard	p. 19
The Query Design Tool and SQL View	p. 20
Opening Microsoft Access and Switching to SQL View	p. 21
Opening Microsoft Access	p. 21
Switching to SQL View in Microsoft Access 2007	p. 23
Switching to SQL View in Microsoft Access 2003	p. 25
Summary	p. 28
Creating Tables and Inserting Records	p. 31
Introduction	p. 31
The Data Definition Language Component	p. 32
CREATE TABLE Syntax	p. 34
Data Types	p. 36
Example 1	p. 39
Constraints	p. 40
Example 2	p. 40
NULL/NOT NULL Constraint	p. 41
PRIMARY KEY Constraint	p. 42
Example 3	p. 43
FOREIGN KEY Constraint	p. 44
UNIQUE Constraint	p. 45
Adding Constraints to Existing Tables	p. 45
Example 4	p. 46
Example 5	p. 46
Constraint Syntax	p. 47
Inserting Records	p. 48
Example 6	p. 48
Inserting Data without Specifying Column Names	p. 50
Example 7	p. 50
Inserting NULL Values	p. 51
Example 8	p. 51
Copying Records from One Table to an Existing Table	p. 51
Example 9	p. 51
Copying Records from One Table to a New Table Simultaneously	p. 53
Example 10	p. 53
Updating Records	p. 54
Update a Record with a Text Value	p. 54
Example 11	p. 54
Update a Record with a New Calculated Value	p. 55
Example 12	p. 55
Update Multiple Columns	p. 55
Example 13	p. 55
Update a Column that Contains a Date	p. 56
Example 14	p. 56
Deleting Records	p. 58
Example 15	p. 58
Summary	p. 59
Retrieving Records	p. 61
Introduction	p. 61
The SELECT Statement	p. 62
Example 1	p. 65
Example 2	p. 66
The ORDER BY Clause	p. 68
Sorting in Descending Order	p. 69
Example 3	p. 69
Sorting in Ascending Order	p. 70
Example 4	p. 70
Sorting Multiple Columns	p. 71
Example 5	p. 71
Example 6	p. 72
Sorting Using Numbers	p. 73
Example 7	p. 73
Example 8	p. 74
Handling Duplicate Values	p. 74
The DISTINCT Keyword	p. 74
Example 9	p. 75
The DISTINCTROW Keyword	p. 75
Handling Duplicate Subsets of the Entire Result Collection	p. 76
The TOP Keyword	p. 76
Example 10	p. 76
Example 11	p. 77
The TOP PERCENT Keywords	p. 78
Example 12	p. 78
Example 13	p. 79
Creating an Alias	p. 80
Example 14	p. 80
Create an Alias that Contains a Space	p. 81
Example 15	p. 81
Concatenation	p. 82
Concatenate Multiple Fields and Characters	p. 82
Example 16	p. 82
Concatenate Multiple Fields from Multiple Tables	p. 83
Example 17	p. 83
Summary	p. 84
Filtering Retrieved Records	p. 87
Introduction	p. 87
The WHERE Clause	p. 88
Comparison Operators	p. 89
Logical Operators	p. 90
Operator Precedence	p. 91
The AND, OR, =, and < Operators	p. 91
Example 1	p. 91
The LIKE Operator	p. 95
Example 2	p. 96
Example 3	p. 97
Example 4	p. 97
Example 5	p. 97
Example 6	p. 98
Example 7	p. 98
Example 8	p. 98
Example 9	p. 98
The BETWEEN Operator	p. 99
Example 10	p. 99
The IN and NOT Operators	p. 100
Example 11	p. 101
Example 12	p. 101
The IS NULL and IS NOT NULL Operators	p. 102
Example 13	p. 102
Summary	p. 103
Creating Calculated Fields	p. 105
Introduction	p. 105
Operators and Functions	p. 106
Arithmetic Operators	p. 108
Use an Arithmetic Operator with SELECT	p. 110
Example 1	p. 110
Use an Arithmetic Operator in the WHERE clause	p. 111
Example 2	p. 111
Aggregate Functions	p. 112
Using the AVG 0, FIRST 0, LAST 0, SUM 0, MAX 0, and MIN 0 Functions	p. 113
Example 3	p. 113
Using the COUNT 0 Function	p. 114
Example 4	p. 114
String Functions and Operations	p. 115
Use of the + and &	p. 117
Example 5	p. 118
Using the LEFT 0, UCASE 0, LEN 0, and TRIM 0 Functions	p. 118
Example 6	p. 118
Using the MID 0 and INSTR 0 Functions	p. 120
Example 7	p. 120
Date and Time Functions	p. 122
Inserting Dates into a Table	p. 124
Example 8	p. 124
Using the FORMAT 0 Function	p. 125
Example 9	p. 125
Using the DATE 0, TIME 0, MONTH 0, DAY 0, and YEAR 0 Functions	p. 127
Example 10	p. 127
Miscellaneous Functions	p. 128
Using the CCUR 0 Function	p. 129
Example 11	p. 129
Summary	p. 130
Grouping Data	p. 133
Introduction	p. 133
The GROUP BY Clause	p. 134
Example 1	p. 135
Using the GROUP BY Clause with the ORDER BY Clause	p. 138
Example 2	p. 138
The HAVING Clause	p. 140
Example 3	p. 140
Using the HAVING Clause with the WHERE Clause	p. 142
Example 4	p. 142
Summary	p. 144
Creating Table Joins and Unions	p. 145
Introduction	p. 145
Table Joins - An Overview	p. 146
Qualification	p. 146
Inner Join	p. 147
Example 1	p. 148
Using the DISTINCTROW Keyword	p. 152
Example 2	p. 152
Self Join	p. 153
Example 3	p. 155
Nested Join	p. 156
Example 4	p. 156
Outer Joins	p. 159
Right Outer Join	p. 160
Example 5	p. 160
Left Outer Join	p. 163
Example 6	p. 163
Create a Join that Contains an Aggregate Function	p. 165
Example 7	p. 165
UNION and UNION ALL Keywords	p. 167
UNION	p. 167
Example 8	p. 167
UNION ALL	p. 171
Example 9	p. 171
Summary	p. 172
Creating Subqueries	p. 175
Introduction	p. 175
Subqueries	p. 176
Correlated and Non-Correlated Subqueries	p. 176
The IN Subquery	p. 176
Example 1	p. 177
The EXISTS Subquery	p. 180
Example 2	p. 180
The ANY and SOME Subqueries	p. 183
Example 3	p. 184
The ALL Subquery	p. 185
Example 4	p. 185
Nested Subqueries	p. 186
Example 5	p. 186
Using a Subquery to Find the Second Highest Value	p. 189
Example 6	p. 189
Summary	p. 190
Creating Views	p. 191
Introduction	p. 191
Creating a View	p. 192
Example 1	p. 192
Creating a View Using the CREATE VIEW Keywords in SQL-92	p. 194
Create a View that Contains a Complex Join	p. 195
Example 2	p. 195
Query the Customers Total Transactions View	p. 196
Example 3	p. 196
Filtering a Record through a View	p. 197
Example 4	p. 197
Example 5	p. 198
Updating a Record through a View	p. 198
Example 6	p. 199
Deleting a View	p. 199
Example 7	p. 200
Summary	p. 200
Table Management and Indexes	p. 201
Introduction	p. 201
Adding a Column to an Existing Table	p. 202
Example 1	p. 203
Changing a Column	p. 204
Example 2	p. 204
Setting a Default Value for a Column	p. 205
Example 3	p. 205
Removing a Column from a Table	p. 206
Example 4	p. 206
Removing a Table	p. 207
Improving Data Retrieval Time Using Indexes	p. 207
Index Options	p. 208
Creating an Index	p. 209
Example 5	p. 209
Indexing in Descending Order	p. 210
Example 6	p. 210
Viewing and Editing Indexes	p. 211
Deleting an Index	p. 212
Example 7	p. 213
Summary	p. 213
Temporary Tables vs. Views	p. 215
Introduction	p. 215
Creating a View	p. 216
Example 1	p. 216
Creating a Temporary Table	p. 218
Example 2	p. 218
Accessing the Temporary Table	p. 219
Querying a Temporary Table	p. 220
Example 3	p. 220
Indexing a Temporary Table	p. 221
Example 4	p. 221
Updating a Temporary Table	p. 222
Example 5	p. 222
Copying Records from One Temporary Table to a New Temporary Table Simultaneously	p. 223
Example 6	p. 223
Deleting a Temporary Table	p. 224
Example 7	p. 224
Why Do We Need Temporary Tables?	p. 224
Summary	p. 228
Parameter Queries	p. 229
Introduction	p. 229
Parameter Queries	p. 229
Creating a Simple Query	p. 230
Example 1	p. 230
Creating a Parameter Query	p. 231
Example 2	p. 231
Customizing Your Dialog Box	p. 233
Example 3	p. 233
Creating Multiple Prompts	p. 234
Example 4	p. 234
Example 5	p. 236
Using the LIKE Keyword to Prompt the User	p. 237
Example 6	p. 237
Prompting the User for Dates	p. 238
Example 7	p. 238
Creating a Button to Prompt the User	p. 239
Example 8	p. 239
View the New Button on the Form	p. 244
SQL Syntax for a Parameter Query in SQL View	p. 246
Example 9	p. 246
Example 10	p. 247
Non-parameter Parameter Queries	p. 248
Summary	p. 250
Integrating SQL Script in VBA Code	p. 253
Introduction	p. 253
Fixed Queries vs. "On-the-Fly" Queries	p. 254
Filtered Recordsets for Forms	p. 259
Filtered Recordsets for Combo Boxes	p. 263
Recordsets for Subforms	p. 265
Report Filters	p. 270
Summary	p. 274
Integrating SQL Script into ASP Code	p. 275
Introduction	p. 275
Basics	p. 276
Building the Components	p. 277
ODBC Connection	p. 277
Code	p. 281
Building SQL Statements	p. 286
Summary	p. 291
Access Projects	p. 293
Introduction	p. 293
Overview	p. 293
Differences between Access Projects and Access Databases	p. 298
Project Window	p. 301
Tables	p. 302
Database Diagrams	p. 307
Queries	p. 310
Views	p. 311
Stored Procedures	p. 316
Functions	p. 318
Summary	p. 321
Concluding Thoughts	p. 323
Introduction	p. 323
Common Rules	p. 323
Summary	p. 324
Answers to Quizzes and Projects	p. 327
Frequently Used SQL Keywords in Microsoft Access	p. 341
Terms and Definitions	p. 345
Microsoft Access Data Types	p. 351
SQL Script to Create the Tables in This Book	p. 353
Index	p. 369
Table of Contents provided by Ingram. All Rights Reserved.

Kids

Men

Unisex

Women

For You

For Your Car

For Your Home

For Your Pet

For Your Tech

Holiday

Home Decor

Mascot

Office Decor

Spirit

Stationary

Diploma Frames

Graduation Gifts

For Your Office

Clothing

SQL for Microsoft Access

Buy New

Rent Textbook

Rent Digital

Used Textbook

Summary

Author Biography

Table of Contents

SQL for Microsoft Access

Buy New

Rent Textbook

Rent Digital

Used Textbook

How Marketplace Works:

Summary

Author Biography

Table of Contents

Digital License