Microsoft Office Access 2003 inside out

PUBLISHED BY

Microsoft Press

A Division of Microsoft Corporation

One Microsoft Way

Redmond, Washington 98052-6399

Copyright © 2004 by John L. Viescas

All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form

or by any means without the written permission of the publisher.

Library of Congress Cataloging-in-Publication Data

Viescas, John, 1947-

Microsoft Office Access 2003 Inside Out / John L. Viescas.

p. cm.

Includes index.

ISBN 0-7356-1513-6

1. Database management. 2. Microsoft Access. I. Title.

QA76.9.D3V545 2003

005.75'65--dc21 2003056160

Printed and bound in the United States of America.

1 2 3 4 5 6 7 8 9 QWT 8 7 6 5 4 3

Distributed in Canada by H.B. Fenn and Company Ltd.

A CIP catalogue record for this book is available from the British Library.

Microsoft Press books are available through booksellers and distributors worldwide. For further informa￾tion about international editions, contact your local Microsoft Corporation office or contact Microsoft

Press International directly at fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress.

Send comments to [email protected].

ActiveX, FoxPro, FrontPage, InfoPath, Microsoft, Microsoft Internet Explorer (logo only), Microsoft

Press, MSDN, MS-DOS, MSN, the Office logo, OpenType, Outlook, PivotChart, PivotTable, PowerPoint,

SharePoint, Visual Basic, Visual C# , Visual C++ , Visual FoxPro, Visual J#, Visual Studio, the

Microsoft Visual Tools eMbedded logo, Windows, and Windows NT are either registered trademarks or

trademarks of Microsoft Corporation in the United States and/or other countries. Other product and

company names mentioned herein may be the trademarks of their respective owners.

The example companies, organizations, products, domain names, e-mail addresses, logos, people, places,

and events depicted herein are fictitious. No association with any real company, organization, product,

domain name, e-mail address, logo, person, place, or event is intended or should be inferred.

Acquisitions Editor: Alex Blanton

Project Editor: Sandra Haynes

Series Editor: Sandra Haynes

Technical Editor: Curt Philips

Body Part No. X09-71422

For my bestest pal in the whole wide world. Without your love and

support, we wouldn’t have made this project happen.

And for mom. I finished this book just in time for your 90th birthday. I

hope I’ve made you proud.

v

Contents at a Glance

Part 1

Understanding Microsoft

Access

Chapter 1

What Is Microsoft Access? . . . . . . . 3

Chapter 2

The Many Faces of

Microsoft Access . . . . . . . . . . . . . 17

Chapter 3

Designing Your Database

Application. . . . . . . . . . . . . . . . . . 53

Part 2

Building a Microsoft Access

Desktop Application

Chapter 4

Creating Your Database

and Tables . . . . . . . . . . . . . . . . . . 85

Chapter 5

Modifying Your Table Design . . . . 139

Chapter 6

Importing and Linking Data. . . . . 179

Chapter 7

Creating and Working

with Simple Queries . . . . . . . . . . 219

Chapter 8

Building Complex Queries . . . . . . 277

Chapter 9

Modifying Data with

Action Queries . . . . . . . . . . . . . . 337

Part 3

Creating Forms and Reports

in a Desktop Application

Chapter 10

Using Forms . . . . . . . . . . . . . . . 365

Chapter 11

Building a Form . . . . . . . . . . . . . 397

Chapter 12

Customizing a Form . . . . . . . . . . 433

Chapter 13

Advanced Form Design. . . . . . . . 481

Chapter 14

Using Reports . . . . . . . . . . . . . . 523

Chapter 15

Constructing a Report . . . . . . . . 537

Chapter 16

Advanced Report Design . . . . . . 555

Part 4

Designing an

Access Project

Chapter 17

Building Tables in an

Access Project . . . . . . . . . . . . . 599

Chapter 18

Building Queries in an

Access Project . . . . . . . . . . . . . 637

Chapter 19

Designing Forms in an

Access Project . . . . . . . . . . . . . 685

Chapter 20

Building Reports in an

Access Project . . . . . . . . . . . . . 701

Contents at a Glance

vi

Part 5

Automating an Access

Application

Chapter 21

Understanding Event

Processing . . . . . . . . . . . . . . . . . 713

Chapter 22

Understanding Visual

Basic Fundamentals . . . . . . . . . . 757

Chapter 23

Automating Your Application

with Visual Basic . . . . . . . . . . . . 857

Chapter 24

The Finishing Touches . . . . . . . . 927

Part 6

Linking Access and

the Web

Chapter 25

Publishing Data on the Web . . . . 961

Chapter 26

Creating Static and

Dynamic Web Pages . . . . . . . . . . 985

Chapter 27

Building Data Access Pages . . . 1015

Chapter 28

Working with XML

and SharePoint. . . . . . . . . . . . . 1095

Part 7

After Completing Your

Application

Chapter 29

Upsizing a Desktop

Application to a Project . . . . . . 1133

Chapter 30

Securing Your Database . . . . . . 1163

Chapter 31

Distributing Your Application . . 1203

Part 8

Appendix

Appendix

Installing Microsoft Office . . . . 1223

Part 9

Articles

Article 1

Understanding SQL . . . . . . . . . . . A1

Article 2

Exporting Data . . . . . . . . . . . . . A45

Article 3

Visual Basic Function

Reference . . . . . . . . . . . . . . . . . A49

Article 4

Internet Explorer Web Page

Color Names . . . . . . . . . . . . . . . A59

vii

Table of Contents

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii

We’d Like to Hear from You! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv

About the CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvii

What’s on the CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii

Sample Applications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxviii

Using the CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxixi

System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix

Support Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix

Conventions and Features Used in This Book . . . . . . . . . . . . . . . . . . . . . . . . xxxi

Text Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxi

Design Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxi

Syntax Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxiii

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxv

Microsoft Access Today . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxvi

About This Book. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvii

Part 1

Understanding Microsoft Access

Chapter 1

What Is Microsoft Access? 3

What Is a Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

Database Capabilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

Microsoft Access as an RDBMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

Data Definition and Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

Data Manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

Data Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Microsoft Access as an Application Development System . . . . . . . . . . . . . 11

Deciding to Move to Database Software. . . . . . . . . . . . . . . . . . . . . . . . . . 13

Chapter 2

The Many Faces of Microsoft Access 17

The Architecture of Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Exploring a Desktop Database—Housing Reservations . . . . . . . . . . . . . . . 19

Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Data Access Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

Table of Contents

viii

Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

Organizing Your Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

Exploring a Project File—LawTrack Contacts . . . . . . . . . . . . . . . . . . . . . . . 48

Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

Views, Functions, and Stored Procedures. . . . . . . . . . . . . . . . . . . . . 50

The Many Faces of Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

Chapter 3

Designing Your Database Application 53

Application Design Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

Step 1: Identifying Tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Step 2: Charting Task Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Step 3: Identifying Data Elements . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Step 4: Organizing the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

Step 5: Designing a Prototype and a User Interface . . . . . . . . . . . . . 55

Step 6: Constructing the Application . . . . . . . . . . . . . . . . . . . . . . . . 55

Step 7: Testing, Reviewing, and Refining . . . . . . . . . . . . . . . . . . . . . 56

An Application Design Strategy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Analyzing the Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

Selecting the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

Organizing Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Data Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

Choosing the Database Subjects. . . . . . . . . . . . . . . . . . . . . . . . . . . 64

Mapping Subjects to Your Database . . . . . . . . . . . . . . . . . . . . . . . . 66

Database Design Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

Waste Is the Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

Normalization Is the Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

Efficient Relationships Are the Result . . . . . . . . . . . . . . . . . . . . . . . 77

When to Break the Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

Improving Performance of Critical Tasks . . . . . . . . . . . . . . . . . . . . . . 79

Capturing Point-In-Time Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80

Creating Report Snapshot Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

Part 2

Building a Microsoft Access Desktop Application

Chapter 4

Creating Your Database and Tables 85

Creating a New Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

Using a Database Template to Create a Database . . . . . . . . . . . . . . 86

Creating a New Empty Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

Creating Your First Simple Table by Entering Data . . . . . . . . . . . . . . . . . . . 92

Creating a Table Using the Table Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . 94

Creating a Table in Design View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

ix

Table of Contents

Defining Fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

Understanding Field Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . 102

Setting Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104

Completing the Fields in the Companies Table . . . . . . . . . . . . . . . . 110

Defining Simple Field Validation Rules . . . . . . . . . . . . . . . . . . . . . . 111

Defining Input Masks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

Defining a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

Defining a Table Validation Rule. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

Understanding Other Table Properties . . . . . . . . . . . . . . . . . . . . . . . . . . 121

Defining Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123

Defining Your First Relationship. . . . . . . . . . . . . . . . . . . . . . . . . . . 126

Creating a Relationship on Multiple Fields . . . . . . . . . . . . . . . . . . . 128

Adding Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

Single Field Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

Multiple-Field Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

Setting Table Design Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133

Printing a Table Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

Database Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137

Chapter 5

Modifying Your Table Design 139

Before You Get Started. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140

Making a Backup Copy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140

Checking Object Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . 142

Deleting Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

Renaming Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145

Changing Field Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146

Moving Fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150

Inserting Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152

Copying Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154

Deleting Fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156

Changing Data Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157

Changing Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157

Changing Data Lengths. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162

Dealing with Conversion Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . 163

Changing Other Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . 163

Reversing Changes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165

Using the Table Analyzer Wizard. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165

Taking a Look at Lookup Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171

Changing the Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176

Compacting Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177

Chapter 6

Importing and Linking Data 179

A Word About Open Database Connectivity (ODBC) . . . . . . . . . . . . . . . . . 179

Importing vs. Linking Database Files . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

Table of Contents

x

Importing Data and Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182

Importing dBASE Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182

Importing Paradox Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184

Importing SQL Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186

Importing Access Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191

Importing Spreadsheet Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192

Preparing a Spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193

Importing a Spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194

Fixing Errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197

Importing Text Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199

Preparing a Text File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199

Importing a Text File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202

Fixing Errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206

Modifying Imported Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

Linking Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

Security Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209

Performance Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209

Linking Access Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211

Linking dBASE and Paradox Files. . . . . . . . . . . . . . . . . . . . . . . . . . 213

Linking Text and Spreadsheet Files . . . . . . . . . . . . . . . . . . . . . . . . 213

Linking SQL Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214

Modifying Linked Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216

Unlinking Linked Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216

Using the Linked Table Manager . . . . . . . . . . . . . . . . . . . . . . . . . . 216

Chapter 7

Creating and Working with Simple Queries 219

Selecting Data from a Single Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221

Specifying Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223

Setting Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224

Entering Selection Criteria. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226

Using Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233

Using the Expression Builder. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240

Specifying Field Names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

Sorting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247

Testing Validation Rule Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249

Checking a New Field Validation Rule. . . . . . . . . . . . . . . . . . . . . . . 249

Checking a New Table Validation Rule . . . . . . . . . . . . . . . . . . . . . . 250

Working in Query Datasheet View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252

Moving Around and Using Keyboard Shortcuts . . . . . . . . . . . . . . . . 252

Working with Subdatasheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254

Changing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257

Working with Hyperlinks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264

Sorting and Searching for Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 269

xi

Table of Contents

Chapter 8

Building Complex Queries 277

Selecting Data from Multiple Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . 277

Creating Inner Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278

Building a Query on a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284

Using Outer Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287

Using a Query Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293

Summarizing Information with Totals Queries . . . . . . . . . . . . . . . . . . . . . 296

Totals Within Groups. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296

Selecting Records to Form Groups. . . . . . . . . . . . . . . . . . . . . . . . . 301

Selecting Specific Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302

Building Crosstab Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302

Partitioning Data in a Crosstab Query . . . . . . . . . . . . . . . . . . . . . . 306

Using Query Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308

Customizing Query Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310

Controlling Query Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311

Working with Unique Records and Values . . . . . . . . . . . . . . . . . . . . 312

Defining a Subdatasheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315

Other Query Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318

Editing and Creating Queries in SQL View. . . . . . . . . . . . . . . . . . . . . . . . 319

Limitations on Using Select Queries to Update Data . . . . . . . . . . . . . . . . 323

Creating PivotTables and PivotCharts from Queries . . . . . . . . . . . . . . . . . 324

Building a Query for a PivotTable . . . . . . . . . . . . . . . . . . . . . . . . . . 325

Designing a PivotTable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328

Designing a PivotChart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332

Chapter 9

Modifying Data with Action Queries 337

Updating Groups of Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337

Testing with a Select Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338

Converting a Select Query to an Update Query . . . . . . . . . . . . . . . . 339

Running an Update Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340

Updating Multiple Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342

Creating a New Table with a Make-Table Query . . . . . . . . . . . . . . . . . . . . 346

Creating a Make-Table Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346

Running a Make-Table Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350

Inserting Data from Another Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352

Creating an Append Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353

Running an Append Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355

Deleting Groups of Rows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356

Testing with a Select Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356

Using a Delete Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358

Deleting Inactive Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359

Troubleshooting Action Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360

Solving Common Action Query Errors and Problems . . . . . . . . . . . . 360

Looking at an Error Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361

Table of Contents

xii

Part 3

Creating Forms and Reports in a Desktop Application

Chapter 10

Using Forms 365

Uses of Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365

A Tour of Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366

Headers, Detail Sections, and Footers. . . . . . . . . . . . . . . . . . . . . . 366

Multiple-Page Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367

Continuous Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368

Subforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369

Pop-Up Forms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370

Modal Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371

Special Controls. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372

PivotTables and PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379

Moving Around on Forms and Working with Data . . . . . . . . . . . . . . . . . . . 380

Viewing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381

Adding Records and Changing Data . . . . . . . . . . . . . . . . . . . . . . . . 384

Searching for and Sorting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391

Performing a Simple Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392

Performing a Quick Sort on a Form Field . . . . . . . . . . . . . . . . . . . . 392

Adding a Filter to a Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392

Printing Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394

Chapter 11

Building a Form 397

Forms and Object-Oriented Programming . . . . . . . . . . . . . . . . . . . . . . . . 397

Starting from Scratch—A Simple Input Form. . . . . . . . . . . . . . . . . . . . . . 400

Building a New Form with Design Tools . . . . . . . . . . . . . . . . . . . . . 400

Building a Simple Input Form for the tblCompanies Table. . . . . . . . . 411

Customizing Colors and Checking Your Design Results . . . . . . . . . . 418

Working with Form Wizards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420

Creating the Basic Products Form with a Form Wizard . . . . . . . . . . . 420

Modifying the Products Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 424

Simplifying Data Input with a Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426

Taking Advantage of Combo Boxes and List Boxes . . . . . . . . . . . . . 426

Using Toggle Buttons, Check Boxes, and Option Buttons. . . . . . . . . 430

Chapter 12

Customizing a Form 433

Aligning and Sizing Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433

Sizing Controls to Fit Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435

Adjusting Control Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439

“Snapping” Controls to the Grid . . . . . . . . . . . . . . . . . . . . . . . . . . 440

Lining Up Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441

xiii

Table of Contents

Enhancing the Look of a Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444

Lines and Rectangles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444

Colors and Special Effects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447

Fonts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449

Setting Control Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451

Formatting Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451

Adding a Scroll Bar. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461

Enabling and Locking Controls. . . . . . . . . . . . . . . . . . . . . . . . . . . . 462

Setting the Tab Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462

Adding a Smart Tag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463

Understanding Other Control Properties . . . . . . . . . . . . . . . . . . . . . 465

Setting Form Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469

Allowing Different Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469

Setting Navigation Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470

Defining a Pop-Up and/or Modal Form . . . . . . . . . . . . . . . . . . . . . . 471

Controlling Edits, Deletions, Additions, and Filtering . . . . . . . . . . . . 472

Defining Window Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472

Setting the Border Style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473

Understanding Other Form Properties . . . . . . . . . . . . . . . . . . . . . . 474

Setting Form and Control Defaults . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477

Changing Control Defaults . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477

Working with AutoFormat. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478

Defining a Template Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479

Chapter 13

Advanced Form Design 481

Basing a Form on a Multiple-Table Query . . . . . . . . . . . . . . . . . . . . . . . . 482

Creating a Many-to-One Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 482

Creating and Embedding Subforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487

Specifying the Subform Source . . . . . . . . . . . . . . . . . . . . . . . . . . . 488

Designing the Innermost Subform . . . . . . . . . . . . . . . . . . . . . . . . . 490

Designing the First Level Subform . . . . . . . . . . . . . . . . . . . . . . . . . 494

Embedding a Subform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495

Specifying the Main Form Source. . . . . . . . . . . . . . . . . . . . . . . . . . 499

Creating the Main Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 499

Creating a Subdatasheet Subform. . . . . . . . . . . . . . . . . . . . . . . . . 502

Displaying Values in an Option Group. . . . . . . . . . . . . . . . . . . . . . . . . . . 504

Using Conditional Formatting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506

Working with the Tab Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509

Creating Multiple-Page Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513

Introducing ActiveX Controls—The Calendar Control . . . . . . . . . . . . . . . . 515

Working with PivotChart Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518

Building a PivotChart Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519

Embedding a Linked PivotChart . . . . . . . . . . . . . . . . . . . . . . . . . . . 520

Table of Contents

xiv

Chapter 14

Using Reports 523

Uses of Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523

A Tour of Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524

Print Preview—A First Look . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525

Headers, Detail Sections, Footers, and Groups . . . . . . . . . . . . . . . 526

Subreports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528

Objects in Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530

Printing Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532

Print Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532

Chapter 15

Constructing a Report 537

Starting from Scratch—A Simple Report . . . . . . . . . . . . . . . . . . . . . . . . 537

Building the Report Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 538

Designing the Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539

Sorting and Grouping Information . . . . . . . . . . . . . . . . . . . . . . . . . 541

Completing the Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542

Using a Report Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546

Selecting a Report Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546

Specifying Wizard Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547

Viewing the Result . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552

Chapter 16

Advanced Report Design 555

Building a Query for a Complex Report. . . . . . . . . . . . . . . . . . . . . . . . . . 556

Creating the Basic Facility Occupancy by Date Report . . . . . . . . . . . . . . . 557

Defining the Sorting and Grouping Criteria . . . . . . . . . . . . . . . . . . . . . . . 559

Setting Section and Report Properties . . . . . . . . . . . . . . . . . . . . . . . . . . 562

Section Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562

Report Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564

Using Calculated Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572

Adding the Print Date and Page Numbers. . . . . . . . . . . . . . . . . . . . 572

Performing Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573

Hiding Redundant Values and Concatenating Text Strings . . . . . . . . 578

Calculating Percentages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 580

Using Running Sum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 581

Taking Advantage of Conditional Formatting . . . . . . . . . . . . . . . . . . 584

Creating and Embedding a Subreport. . . . . . . . . . . . . . . . . . . . . . . . . . . 585

Understanding Subreport Challenges. . . . . . . . . . . . . . . . . . . . . . . 586

Building a Report with a Subreport . . . . . . . . . . . . . . . . . . . . . . . . 589

Adding a PivotChart to a Report. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592

Designing the PivotChart Form . . . . . . . . . . . . . . . . . . . . . . . . . . . 592

Embedding a PivotChart in a Report . . . . . . . . . . . . . . . . . . . . . . . 593

xv

Table of Contents

Part 4

Designing an Access Project

Chapter 17

Building Tables in an Access Project 599

Creating a New Project File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600

Building a New SQL Server Database. . . . . . . . . . . . . . . . . . . . . . . 600

Connecting to an Existing SQL Server Database . . . . . . . . . . . . . . . 603

Creating a Table in Design View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607

Defining Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608

Column Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 610

Completing the Columns in the Companies Table . . . . . . . . . . . . . . 614

Understanding Column Properties . . . . . . . . . . . . . . . . . . . . . . . . . 616

Defining a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618

Adding Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619

Creating Additional Tables in Contact Tracking . . . . . . . . . . . . . . . . . . . . 623

Defining Check Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625

Creating Additional Constraint Expressions . . . . . . . . . . . . . . . . . . 628

Defining Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629

Defining Relationships in Table Design View. . . . . . . . . . . . . . . . . . 629

Defining Relationships Using Database Diagrams. . . . . . . . . . . . . . 632

Setting Table Design Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635

Chapter 18

Building Queries in an Access Project 637

Building Queries Using the Query Designer. . . . . . . . . . . . . . . . . . . . . . . 640

Understanding the Query Designer . . . . . . . . . . . . . . . . . . . . . . . . 640

Working with Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650

Working with In-Line Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 655

Working with Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . 661

Building Queries Using a Text Editor. . . . . . . . . . . . . . . . . . . . . . . . . . . . 665

Building a Text Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . 667

Building a Text Scalar Function . . . . . . . . . . . . . . . . . . . . . . . . . . . 681

Building a Text Table-Valued Function . . . . . . . . . . . . . . . . . . . . . . . 683

Chapter 19

Designing Forms in an Access Project 685

Understanding Form Differences in an Access Project . . . . . . . . . . . . . . . 685

Choosing Option Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687

Setting Project Form Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . 689

Setting Recordset Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692

Understanding Max Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692

Working with Server Filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 694

Setting Input Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697