Access 2013 Absolute Beginner's Guide

Make the most of Access 2013 -- without becoming a technical expert! Access 2013 Absolute Beginner's Guide is the fastest way to get comfortable and productive with the newest version of Access, use its powerful new tools, and extend it to the web and the latest mobile devices. Whether you're new to Access or you're one of the millions who've used previous versions, this practical, approachable book will show you how to do exactly what you want, one incredibly clear and easy step at a time.

Friendly, direct, and patient, this book reveals Access 2013's power, and helps you quickly master its updated interface. World-renowned Access tutorial author Alison Balter provides step-by-step instructions for every task requiring more than one step. She presents screenshots guiding you through complex processes, and illustrations demonstrating the touch gestures you need to run Access on the latest tablets and Windows 8 devices. Along the way, she covers key Access 2013 improvements that make it easier to build web-based apps and SharePoint-integrated database solutions that are consistent, manageable, flexible, and exceptionally usable.

List of contents

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 Spr...

About the author

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 [e-mail], or visit the InfoTech Services Group website
at http://www.TechIsMyThing.com.

Summary

Presents a guide to the latest version of the database management application, covering such topics as creating databases and tables, setting up queries, producing reports, building forms, and using macros --

Product details

• Authors: Alison Balter
• Publisher: Pearson Academic
• Languages: English
• Product format: Paperback / Softback
• Released: 01.01.2013
• EAN: 9780789748713
• ISBN: 978-0-7897-4871-3
• No. of pages: 416
• Weight: 640 g
• Series: Que; Que
• Subject: Natural sciences, medicine, IT, technology > IT, data processing > Application software