Using Microsoft Access 2010

Alison Balter is the president of InfoTech Services Group, Inc., a computer consulting firm based in Newbury Park, California. Alison is a highly experienced independent trainer and consultant specializing in Windows applications training and development. During her 25 years in the computer industry, she has trained and consulted with many corporations and government agencies. Since Alison founded InfoTech Services Group, Inc. (formerly Marina Consulting Group) in 1990, its client base has expanded to include major corporations and government agencies such as Cisco, Shell Oil, Accenture, Northrop, the U.S. Drug Enforcement Administration, Prudential Insurance, Transamerica Insurance, Fox Broadcasting, the U.S. Navy, and others.   Alison is the author of more than 300 internationally marketed computer training videos and CD-ROMs, including 18 Access 2000 videos, 35 Access 2002 videos, 15 Access 2003 videos, 14 Access 2007 User Videos, and 18 Access 2007 Developer Videos. Alison travels throughout North America giving training seminars on Microsoft Access, Microsoft SQL Server, and Visual Basic for Applications. She is also featured in several live satellite television broadcasts for National Technological University.   Alison is also author of 13 books published by Sams Publishing: Alison Balter’s Mastering Access 95 Development, Alison Balter’s Mastering Access 97 Development, Alison Balter’s Mastering Access 2000 Development, Alison Balter’s Mastering Access 2002 Desktop Development, Alison Balter’s Mastering Access 2002 Enterprise Development, Alison Balter’s Mastering Microsoft Access Office 2003, Teach Yourself Microsoft Office Access 2003 in 24 Hours, Access Office 2003 in a Snap, Alison Balter’s Mastering Access 2007 Development, three e-books on Microsoft Access 2007, and Teach Yourself SQL Express 2005 in 24 Hours. Alison is a co-author of three Access books published by Sams Publishing: Essential Access 95, Access 95 Unleashed, and Access 97 Unleashed.   An active participant in many user groups and other organizations, Alison is a past president of the Independent Computer Consultants Association of Los Angeles and of the Los Angeles Clipper Users’ Group. She served as president of the Ventura County Professional Women’s Network for two years.   Alison’s firm, InfoTech Services Group, Inc., is available for consulting work and onsite training in Microsoft Access, Visual Studio .NET, and SQL Server, as well as for Windows Server 2003, Windows 2000, Windows NT, Windows Vista, Windows XP, PC networking, and Microsoft Exchange Server.

Introduction     xviii

1 Manipulating Data with Databases and Tables      1

What Is a Relational Database?      1

A Preview of the Database Components     1

What Types of Things Can I Do with Microsoft Access?      12

Working with an Existing Database     16

Working with Table Data     18

Editing Table Data     21

Adding Records to a Table     23

Deleting Records     24

Filtering Table Data     30

2 Using Queries to Retrieve the Data You Need     32

What Is a Query and When Should You Use One?      32

Opening a Query     32

Adding and Removing Fields     35

Modifying the Sort Order of a Query     37

Working with Simple Criteria     40

Modifying the Datasheet View of a Query     52

Saving a Query     52

Printing Query Results     53

Closing a Query     54

Designing a Query Based on Multiple Tables     55

3 Using Forms to Enter and Edit Table Data     57

Opening an Existing Form     57

Working with Data in a Form     57

Sorting Records     69

Filtering the Data Underlying a Form     69

Viewing the Design of a Form     72

Closing a Form     72

The AutoForm Feature     73

Using the Form Wizard to Build a Form     75

Using the Conditional Formatting Feature of a Form     78

4 Using Reports to Print Information     80

Opening and Viewing a Report     80

Printing a Report     83

The AutoReport Feature and the Report Wizard     85

Closing a Report     89

Viewing the Design of a Report     89

5 Creating Your Own Databases and Tables     92

Types of Databases Available     92

Creating a New Database     92

Building a New Table     94

Selecting the Appropriate Field Type for Data     99

Using Indexes to Improve Performance     04

The All-Important Primary Key     106

Working with Field Properties     107

6 Relating the Information in Your Database     118

Introduction to Relational Database Design     118

The Types of Relationships     125

Establishing Relationships in Access     127

Establishing Referential Integrity     131

7 Enhancing the Queries That You Build     137

Everything You Need to Know About Query Basics     137

Ordering Query Results     143

Refining a Query by Using Criteria     144

Updating Query Results     147

Building Queries Based on Multiple Tables     148

Creating Calculated Fields     153

Getting Help from the Expression Builder     155

Creating and Running Parameter Queries     157

Creating and Running Action Queries     158

Using Aggregate Functions to Summarize Numeric Data     167

Creating Totals Queries     169

Working with Outer Joins     171

8 Building Powerful Forms     174

Power Control Techniques     174

Conditional Formatting     185

Form Properties and Why Should You Use Them      186

Control Properties and Why to Use Them     192

Working with Combo Boxes     201

The Command Button Wizard: Programming Without Typing     206

Building Forms Based on More Than One Table     208

9 Building Powerful Reports     214

The Anatomy of a Report     214

Control Properties and Why to Use Them     216

Building Reports Based on More Than One Table     223

Working with Sorting and Grouping     232

Report Properties and Why to Use Them     237

Basing Reports on Stored Queries or Embedded SQL Statements     242

10 Automating Your Database with Macros     244

Learning the Basics of Creating and Running a Macro     244

Running an Access Macro .      254

Modifying an Existing Macro     257

Creating an Embedded Macro     259

Creating Data Macros     262

Creating a Drillthrough Macro     265

Other New Features Available in Macros     268

Testing a Macro     269

Determining When You Should Use Macros and When You Shouldn’t      271

Converting a Macro to VBA Code     271

Creating an AutoExec Macro     273

11 Sharing Data with Other Applications     275

What Is External Data?      275

Importing, Linking, and Opening Files: When and Why     276

Exporting to Another Access Database     278

Exporting to an Excel Spreadsheet     280

Exporting to ASCII     282

Importing from Another Access Database     285

Importing Spreadsheet Data      289

Importing ASCII Data     293

Linking to Tables in Another Access Database     296

Linking to Another Type of Database     298

The Linked Table Manager      307

12 Working with Web Databases      309

Working with Web Databases     309

Creating a Blank Web Database     310

Creating Publishable Objects     312

Publishing Your Database to Access Services     318

Viewing Your Application in a Browser     321

Index    323