Access 2013 Absolute Beginner's Guide

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 28 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, the University of Southern California , Massachusetts Institute of Technology, 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. Alison is also the 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, Access 97 Unleashed, and Using Microsoft Access 2010.   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 2 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 2008, Windows Vista, Windows XP, Windows 7, Windows 8, PC networking, and Microsoft Exchange Server. You can contact Alison by email at Alison@TechIsMyThing.com, or visit the InfoTech Services Group website at http://www.TechIsMyThing.com.

Introduction.....................................................1

Who Should Read This Book.................................................................................... 1

How This Book Is Organized..................................................................................... 1

 Requirements, Editions, and Features................................................................ 2

Using This Book.......................................................................................................... 5

 

1 Why Use Microsoft Access?...................7



What Is a Relational Database?................................................................................ 8

What Types of Things Can I Do with Microsoft Access?....................................... 8

 Access as a Development Platform for Small-Business Applications.............. 9

 Access as a Development Platform for Departmental Applications................ 9

 Access as a Development Platform for Corporation-Wide Applications................10

 Access as a Front End for Enterprisewide Client/Server Applications..................11

 Access as a Tool to Develop Web Applications..............................................11

A Preview of the Database Components..............................................................11

 Tables: A Repository for Data............................................................................11

 Relationships: Tying the Tables Together........................................................14

 Queries: Stored Questions or Actions You Apply to Data.............................16

 Forms: A Means to Display, Modify, and Add Data.......................................17

 Reports: Turning Data into Information............................................................18

 Macros: A Means of Automating a System......................................................20

 Modules: The Foundation of the Application Development Process...........21

 

2 Getting Started with Microsoft Access........................25



Creating a Database from a Template..................................................................26 Viewing Database Objects......................................................................................27

 Viewing Database Tables...................................................................................27

 Viewing Queries..................................................................................................30

 Viewing Forms.....................................................................................................30

 Viewing Reports...................................................................................................31

Creating a Database from Scratch.........................................................................31

Opening an Existing Database...............................................................................32

Closing a Database..................................................................................................33

Exiting Access...........................................................................................................33

 

3 Tables: The Repository for Your Data............................35



Working with Table Data.........................................................................................36

 Opening an Access Table..................................................................................36

 Navigating Around a Table................................................................................36

 Closing a Table....................................................................................................38

Editing Table Data...................................................................................................39

 Edit Existing Records..........................................................................................39

 Undoing Changes...............................................................................................39

Adding Records to a Table.....................................................................................40

Add Records to a Table...........................................................................................40

Deleting Records......................................................................................................41

 Selecting One or More Records........................................................................41

 Deleting Records.................................................................................................42

 Finding and Replacing Records.........................................................................44

Filtering Table Data..................................................................................................47

 Filtering by Selection..........................................................................................47

 Removing Filters..................................................................................................48

 

4 Using Queries to Retrieve the Data You Need......................51



What Is a Query and When Should You Use One?..............................................51 Creating a Simple Query.........................................................................................52

 Designing the Query...........................................................................................52

 Adding Tables to Queries..................................................................................53

 Adding Fields to Queries...................................................................................53

Ordering the Query Result......................................................................................56

 Sorting on a Single Field....................................................................................56

 Sorting on More Than One Field......................................................................57

Working with Simple Criteria..................................................................................57

 Using an Exact Match Query.............................................................................57

 Creating Criteria Based on Multiple Conditions..............................................58

Saving a Query.........................................................................................................66

Closing a Query........................................................................................................66

 

5 Using Forms to Display and Modify Information................69



 Moving from Record to Record in a Form.......................................................70

 Undoing Changes Made Within a Form...........................................................71

 Using a Form to Delete Records from a Table................................................73

 Copying Records Within a Form.......................................................................76

 Finding a Record That Meets Specific Criteria................................................77

Sorting Records........................................................................................................79

 Sort Records on a Single Field..........................................................................80

Filtering the Data Underlying a Form....................................................................80

 Use the Filter by Form Feature..........................................................................80

 Removing a Filter................................................................................................81

 Use Multiple Filter Criteria.................................................................................81

Closing a Form.........................................................................................................83

 

6 Creating Your Own Forms.....................................85



The AutoForm Feature............................................................................................86  Create a Form by Using the AutoForm Feature..............................................86

 Saving a Form......................................................................................................87

Using the Form Wizard to Build a Form................................................................87

Creating Split Forms................................................................................................88

Creating Multiple Item Forms.................................................................................90

Creating Navigation Forms.....................................................................................91

 Customizing a Navigation Form........................................................................93

 Creating a Navigation Form with Horizontal and Vertical Tabs.....................95

 

7 Using Reports to Print Information.........................99



Opening and Viewing a Report............................................................................100

 Preview a Report...............................................................................................100

 Moving from Page to Page..............................................................................100

 Zooming In and Out ........................................................................................101

 Viewing Multiple Pages....................................................................................102

Working in Layout View.........................................................................................103

Working in Report View.........................................................................................103

Printing a Report....................................................................................................104

 Use Page Setup.................................................................................................104

 Sending Reports to the Printer........................................................................105

Closing a Report.....................................................................................................105

 

8 Building Your Own Reports............................... 109



 Using the AutoReport Feature.........................................................................110 Viewing the Design of a Report............................................................................113

Types of Reports Available....................................................................................114

 Detail Reports....................................................................................................114

 Summary Reports..............................................................................................115

 Reports with Graphics.......................................................................................116

 Reports with Forms...........................................................................................116

 Reports with Labels...........................................................................................117

 Creating Mailing Labels....................................................................................117

 

9 Creating Your Own Tables............................ 125



Building a New Table............................................................................................126

 Building a Table from a Datasheet.................................................................126

 Designing a Table from Scratch......................................................................128

Selecting the Appropriate Field Type for Data..................................................129

 Short Text Fields: The Most Common Field Type........................................132

 Long Text Fields: For Long Notes and Comments.......................................132

 Number Fields: For When You Need to Calculate.......................................132

 Date/Time Fields: For Tracking When Things Happened............................133

 Currency Fields: For Storing Money...............................................................133

 AutoNumber Fields: For Unique Record Identifiers......................................133

 Yes/No Fields: For When One of Two Answers Is Correct..........................134

 OLE Object Fields: For Storing Just About Anything...................................134

 Hyperlink Fields: For Linking to the Internet..................................................134

 Attachment Fields: For Storing File Attachments Associated with a Record............134

 Calculated Fields: For Storing Your Database Calculations.........................135

Using Indexes to Improve Performance...............................................................135

 Create an Index Based on a Single Field.......................................................136

 Create an Index Based on Multiple Fields.....................................................137

The All-Important Primary Key..............................................................................137

Working with Field Properties...............................................................................138

 The Field Size Property: Limiting What the User Enters into a Field..........139

 The Format Property: Determining How Access Displays Data...................139

 The Caption Property: Providing Alternatives to the Field Name...............141

 The Default Value Property: Saving Data-Entry Time...................................142

 The Validation Rule and Validation Text Properties: Controlling What the User Enters in a Field......142

 The Required Property: Making the User Enter a Value...............................144

 The Allow Zero Length Property: Accommodating for Situations with Nonexistent Data..........144

 The Input Mask Property: Determining What Data Goes into a Field.........................145

 The Lookup Wizard...........................................................................................147

 

10 Relating the Information in Your Database........................ 151



Introduction to Relational Database Design.......................................................152 The History of Relational Database Design.........................................................152

 Goals of Relational Database Design.............................................................152

 Rules of Relational Database Design..............................................................152

 Normalization and Normal Forms...................................................................154

 Denormalization: Purposely Violating the Rules............................................158

 Integrity Rules....................................................................................................158

The Types of Relationships...................................................................................159

 One-to-Many Relationships..............................................................................159

 One-to-One Relationships................................................................................160

 Many-to-Many Relationships............................................................................161

Establishing Relationships in Access....................................................................161

 Following Guidelines for Establishing Relationships.....................................162

 Modifying an Existing Relationship.................................................................164

Establishing Referential Integrity..........................................................................164

 The Cascade Update Related Fields Option.................................................166

 The Cascade Delete Related Records Option...............................................167

 

11 Enhancing the Queries That You Build................................. 171



Everything You Ever Needed to Know About Query Basics.............................172

 Removing a Field from the Query Design Grid.............................................172

 Inserting a Field After a Query Is Built............................................................172

 Moving a Field to a Different Location on the Query Grid..........................173

 Move More Than One Column........................................................................173

Updating Query Results.........................................................................................173

 Update Results of a Query...............................................................................174

Building Queries Based on Multiple Tables........................................................174

 Pitfalls of Multitable Queries............................................................................176

 AutoLookup in Multitable Queries..................................................................177

 Modifying the Datasheet View of a Query.....................................................178

 Printing Query Results......................................................................................181

Refining a Query by Using Criteria.......................................................................184

 Working with Dates in Criteria.........................................................................185

 

12 Advanced Query Techniques........................................ 189



Creating Calculated Fields....................................................................................190 Getting Help from the Expression Builder..........................................................191

Creating and Running Parameter Queries...........................................................192

Creating and Running Action Queries.................................................................194

 Creating and Running Update Queries..........................................................195

 Creating and Running Delete Queries............................................................197

 Creating and Running Append Queries.........................................................198

 Creating and Running Make Table Queries...................................................201

Using Aggregate Functions to Summarize Numeric Data.................................202

Creating Totals Queries.........................................................................................203

Working with Outer Joins......................................................................................206

 Establish an Outer Join....................................................................................206

 

13 Building Powerful Forms................................................ 209



Power Control Techniques....................................................................................210

 Add Fields to a Form........................................................................................210

 Selecting, Moving, Aligning, and Sizing Form Objects................................211

 Modifying Object Tab Order...........................................................................219

Conditional Formatting..........................................................................................219

 Conditionally Format Data...............................................................................220

Form Properties and Why Should You Use Them..............................................221

 Working with the Properties Window.............................................................221

 Working with the Important Form Properties................................................222

Control Properties and Why to Use Them..........................................................227

 The Format Properties of a Control................................................................227

 The Data Properties of a Control....................................................................230

 The Other Properties of a Control..................................................................233

 

14 Advanced Form Techniques................................... 237



Working with Combo Boxes.................................................................................238  List Boxes...........................................................................................................241

The Command Button Wizard: Programming Without Typing.........................241

Building Forms Based on More Than One Table...............................................244

 Creating One-to-Many Forms..........................................................................244

 Working with Subforms....................................................................................247

 

15 Building Powerful Reports..................................... 251



The Anatomy of a Report......................................................................................252

Control Properties and Why to Use Them..........................................................253

 The Format Properties of a Control................................................................253

 The Data Properties of a Control....................................................................258

 The Other Properties of a Control..................................................................258

Building Reports Based on More Than One Table............................................260

 Creating One-to-Many Reports.......................................................................260

 Working with Subreports..................................................................................266

 

16 Advanced Report Techniques...................................... 269



Working with Sorting and Grouping....................................................................270  Add Sorting and Grouping to a Report..........................................................270

 Sorting and Grouping Settings........................................................................271

 Group Header and Footer Properties and Why to Use Them.....................273

Report Properties and Why to Use Them............................................................274

 Working with the Properties Window.............................................................275

 The Format Properties of a Report.................................................................275

 The Report’s Data Properties...........................................................................276

 Other Properties of a Report...........................................................................277

Basing Reports on Stored Queries or Embedded SQL Statements...................278

 

17 Automating Your Database with Macros............................. 281



Learning the Basics of Creating and Running a Macro......................................282

 Working with Macro Actions............................................................................282

 Working with Action Arguments.....................................................................284

 Submacros..........................................................................................................286

 Program Flow.....................................................................................................287

Running an Access Macro.....................................................................................289

 Running a Macro from the Macro Design Window.......................................290

 Running a Macro from the Macros Group of the Navigation Pane.............290

 Triggering a Macro from a Form or Report Event.........................................291

Modifying an Existing Macro................................................................................292

 Inserting New Macro Actions...........................................................................292

 Deleting Macro Actions....................................................................................292

 Moving Macro Actions......................................................................................293

 Copying Macro Actions....................................................................................293

Creating an Embedded Macro.............................................................................294

 

18 Advanced Macro Techniques...................................... 299



Creating Data Macros............................................................................................300  Respond to Events............................................................................................300

Creating a Drillthrough Macro..............................................................................301

Other New Features Available in Macros............................................................304

Testing a Macro......................................................................................................305

Determining When You Should Use Macros and When You Shouldn’t...........306

Converting a Macro to VBA Code.......................................................................307

Creating an AutoExec Macro................................................................................308

 

19 Sharing Data with Other Applications........................ 311



What Is External Data?...........................................................................................312

Importing, Linking, and Opening Files: When and Why...................................312

 Determining Whether to Import or Link.........................................................313

 Looking at Supported File Formats.................................................................314

Exporting to Another Access Database...............................................................314

Exporting to an Excel Spreadsheet......................................................................316

 Exporting to an Excel Spreadsheet Using the Context-Sensitive Menu.................316

 Exporting to an Excel Spreadsheet Using Drag and Drop...........................317

 Exporting to an Excel Spreadsheet by Using the External Data Tab on the Ribbon......318

Exporting to ASCII..................................................................................................318

 Export Tables and Queries to the ASCII File Format....................................318

Importing from Another Access Database..........................................................321

 Import an Access Table....................................................................................322

Importing Spreadsheet Data.................................................................................323

Importing ASCII Data.............................................................................................326

 Import ASCII Data into Access........................................................................326

Linking to Tables in Another Access Database...................................................329

Linking to Another Type of Database..................................................................331

 Link to Excel Spreadsheets..............................................................................331

 Linking to SQL Server Databases....................................................................333

The Linked Table Manager...................................................................................338

 Move and Update Table Links.........................................................................338

 

20 Working with Web Databases............................. 343



Working with Web Databases...............................................................................344 Creating a Blank Web Database..........................................................................344

Creating a Query....................................................................................................346

Creating and Working with Forms........................................................................349

 Creating a New Form.......................................................................................350

 Customizing an Existing Form.........................................................................354

Using a Template to Create a Website...............................................................357

Viewing Your Website in a Browser.....................................................................358

 

21 Putting It All Together............................................ 363



Designing the Tables to Store Your Data............................................................364

Building the Forms to Edit Your Data..................................................................369

 Designing the Clients Form.............................................................................369

 Designing the Projects Form...........................................................................372

 Adding a Command Button That Links the Clients and Projects Forms.........374

Building the Queries to Extract the Data You Need..........................................375

Designing the Reports to Display Your Data......................................................377

 Designing the rptClientListing Report............................................................377

 Building the Macros Necessary to Automate Your Application..................380

 

Index ........................................... 383