Home > Store

Access 2013 Absolute Beginner's Guide

Register your product to gain access to bonus material or receive a coupon.

Access 2013 Absolute Beginner's Guide

eBook (Watermarked)

  • Sorry, this book is no longer in print.
  • Includes EPUB and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

Not for Sale

Description

  • Copyright 2013
  • Dimensions: 7" x 9-1/8"
  • Edition: 1st
  • eBook (Watermarked)
  • ISBN-10: 0-13-326092-5
  • ISBN-13: 978-0-13-326092-2

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

Sample Content

Table 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 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

Updates

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020