Want a NOOK? Explore Now
Make the most of Access 2013—without becoming a technical expert! This book is the fastest way to master Access and use it to build powerful, useful databases of all kinds—even web application databases! Even if you’ve never used Access before, you’ll learn how to do what you want, one incredibly clear and easy step at a time. Access has never, ever been this simple!
Who knew how simple Access® 2013 could be? This is the easiest, most practical beginner’s guide to using Microsoft’s incredibly powerful new Access 2013 database program… simple, reliable instructions for doing everything you really
want to do! Here’s a small sample of what you’ll learn:
• Create tables to efficiently store and navigate your data
• Build queries that retrieve exactly the information you want
• Design intuitive forms that help your users work more efficiently
• Build reports that answer key questions intuitively and visually
• Learn easy techniques for designing more reliable databases
• Work faster with AutoForms, AutoReports, and other shortcuts
• Automate repetitive tasks and build more polished databases with macros
• Share Access data with Excel, SQL Server, and other applications
• Solve complex problems with advanced query, form, and reporting techniques
• Build modern web databases that serve users through browsers
• Run your database on the cloud through Microsoft Office 365
• Construct a complete database application from start to finish
• And much more…
Alison Balter, President of InfoTech Services Group, Inc., has spent 25 years training and consulting on Microsoft Access and related applications with top organizations such as Cisco, Shell, Accenture, Northrop, the U.S. Drug Enforcement Administration, Prudential, Transamerica, Fox Broadcasting, and the U.S. Navy. She travels throughout North America delivering seminars on Access and has authored 14 books and videos for Pearson, including Microsoft Access 2010 LiveLessons and Alison Balter’s Mastering Access 2007 Development. She is past president of the Independent Computer Consultants Association of Los Angeles.
Category: Databases
Covers: Microsoft® Access® 2013
User Level: Beginning
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
Overview
Make the most of Access 2013—without becoming a technical expert! This book is the fastest way to master Access and use it to build powerful, useful databases of all kinds—even web application databases! Even if you’ve never used Access before, you’ll learn how to do what you want, one incredibly clear and easy step at a time. Access has never, ever been this simple!
Who knew how simple Access® 2013 could be? This is the easiest, most practical beginner’s guide to using ...