Cover

Table of Contents

Chapter 1: Getting Started with Access 2010

An Introduction to Access 2010

Start and Exit Access

Create a Blank Database

Close a Database

Create a Database by Using a Template

Open a Database File

Understanding the Access 2010 Interface

Change the Navigation Pane View

Open and Close an Object

View an Object

Chapter 2: Entering and Editing Data

Enter New Records

Navigate between Records

Edit Records

Attach Files to Records

Open, Save, and Remove File Attachments

Insert an OLE Object

Open, Edit, and Remove OLE Objects

Enter Data in a Multivalued Field

Delete Records

Resize Datasheet Columns and Rows

Sort Records

Display Summary Statistics

Print a Datasheet or Form

Chapter 3: Working with Tables

Plan Effective Tables

Create a Table in Datasheet View

Save a Table

Create a Table in Design View

Open a Table in Design View

Rearrange Fields

Insert and Delete Fields

Understanding Data Types

Change a Field’s Data Type

Understanding Primary and Composite Keys

Set the Primary or Composite Key

Rename a Table

Delete a Table

Copy a Table

Chapter 4: Working with Fields

Understanding Field Properties

Understanding Field Sizes

Change a Field Size

Set a Field Caption

Set a Field’s Format

Set a Default Value

Make a Field Required

Index a Field

Apply Smart Tags

Create an Input Mask

Create a Validation Rule

Create a Record-Level Validation Rule

Chapter 5: Working with Relationships and Lookups

Understanding Relationships

Create a Relationship between Two Tables

Edit a Relationship

Remove a Relationship

Arrange the Relationships Window

Print a Relationship Report

View Object Dependencies

Document the Database

Understanding Lookups

Create a Table for Use as a Field Lookup

Create a Field Lookup Based on a Table

Create a Field Lookup with Values That You Specify

Set Up a Multivalued Field

Chapter 6: Finding and Filtering Data

Understanding Find and Replace

Find Data by Using Find and Replace

Replace Data by Using Find and Replace

Filter to Show Only Blank or Nonblank Entries

Filter by Selection

Filter for Multiple Values

Using Text Filters

Filter by Form

Save a Filter As a Query

Chapter 7:Creating Simple Queries

Understanding Queries

Create a Query with the Simple Query Wizard

Start a New Query in Query Design View

Insert, Arrange, and Remove Query Fields

Set Field Sorting

Add an Alias to a Query Field

Understanding Criteria

Filter a Query for a Specific Value

Specify a Range of Values

Specify a List of Values

Hide a Field in the Query Results

Combine Criteria

Limit the Records Returned

Chapter 8: Creating More Complex Queries

Understanding Summary Queries

Create a Summary Query with the Simple Query Wizard

Create a Summary Query in Query Design View

Understanding Calculated Fields

Create a Calculated Field

Understanding Action Queries

Run a Make Table Query

Run a Delete Query

Run an Append Query

Run an Update Query

Prompt the User for a Parameter

Understanding Parameter Syntax

Chapter 9: Creating Forms

Understanding Forms

Create and Save a Form

Create a Form with the Form Wizard

Create a Form in Layout View

Create a Form in Design View

Delete a Field from Design View

Arrange Fields on a Form

Group Fields Together

Define the Tab Order on a Form

Chapter 10: Modifying and Formatting Forms

Display the Header and Footer

Resize Sections of a Form

Select Sections of a Form

Add a Form Title

Apply a Theme

Apply a Font Theme or Color Theme

Create a Custom Font Theme

Create a Custom Color Theme

Create a Custom Theme

Browse for a Theme to Apply

Adjust Internal Margins and Padding

Add a Label

Format Label Text

Change the Background Color

Use a Background Image

Add a Hyperlink

Add a Tabbed Section

Insert a Logo or Image

Set Conditional Formatting

Chapter 11: Creating and Formatting Reports

Understanding Report Views

Create a Simple Report

Apply a Theme to a Report

Preview and Print a Report

Create a Report with the Report Wizard

Create a Report in Layout View

Set the Page Size and Orientation

Change the Report Layout Type

Set Page Margins

Set Control Margins and Padding

Format Report Text

Size and Align Report Fields

Insert a Page-Numbering Code

Chapter 12: Grouping and Summarizing Data

Understanding Grouping and Summarizing

Group Report Results

Sort Report Results

Count Records

Add an Aggregate Function

Summarize a Datasheet with a PivotTable

Add and Delete Aggregate Functions in a PivotTable

Group and Ungroup PivotTable Content

Clear a PivotTable Grid

Switch a PivotTable to a PivotChart

Chapter 13: Creating Mailing Labels

Create Labels

Add a Field to an Existing Line

Add a Field to a Label as a Separate Line

Color the Label Background

Color the Label Text

Apply Font Formatting to Label Text

Export Labels to Word

Chapter 14: Creating Charts

Understanding Charts in Access

Open a PivotChart View

Start a PivotChart Form

Construct a PivotChart

Group Data in a PivotChart

Change the PivotChart Type

Change Chart Colors

Create an Embedded Chart Object

Chapter 15: Working with External Data

Import an Excel Worksheet

Link to an Excel Worksheet

Link to an Outlook Folder

Manage Linked Tables

Import a Table from Another Access Database

Import Data from a Delimited Text File

Export Data to Excel

Export Data as HTML

Export Data to a Plain Text File

Save Import or Export Specifications

Using Saved Import or Export Specifications

Chapter 16: Performing a Mail Merge with Microsoft Word

Begin a Mail Merge

Create the Main Document in Word

Insert an Address Block

Insert a Greeting Line

Match Fields

Insert Individual Fields

Preview the Merge Results

Filter the Recipient List

Sort the Recipient List

Merge to a New Document

Merge Directly to a Printer

Save the Merge for Later Use

Chapter 17: Maintaining a Database

Set a Trusted Location

Save in a Previous Version Format

Convert to the Access 2007/2010 Format

Back Up a Database

Document a Database

Compact and Repair a Database

Password-Protect a Database

Create a Switchboard

Set Switchboard Startup Options

Teach Yourself Visually™ Access® 2010

by Faithe Wempen

Visual_Logo_on_white.eps

Teach Yourself VISUALLY™ Access® 2010

Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256

www.wiley.com

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, 201-748-6011, fax 201-748-6008, or online at http://www.wiley.com/go/permissions.

Library of Congress Control Number: 2010923552

ISBN: 978-1-118-08185-3

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

Trademark Acknowledgments

Wiley, the Wiley Publishing logo, Visual, the Visual logo, Teach Yourself VISUALLY, Read Less - Learn More and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates. Access is a registered trademark of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc. is not associated with any product or vendor mentioned in this book.

Disclaimer

In order to get this information to you in a timely manner, this book was based on a pre-release version of Microsoft Office 2010. There may be some minor changes between the screenshots in this book and what you see on your desktop. As always, Microsoft has the final word on how programs look and function; if you have any questions or see any discrepancies, consult the online help for further information about the software.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

FOR PURPOSES OF ILLUSTRATING THE CONCEPTS AND TECHNIQUES DESCRIBED IN THIS BOOK, THE AUTHOR HAS CREATED VARIOUS NAMES, COMPANY NAMES, MAILING, E-MAIL AND INTERNET ADDRESSES, PHONE AND FAX NUMBERS AND SIMILAR INFORMATION, ALL OF WHICH ARE FICTITIOUS. ANY RESEMBLANCE OF THESE FICTITIOUS NAMES, ADDRESSES, PHONE AND FAX NUMBERS AND SIMILAR INFORMATION TO ANY ACTUAL PERSON, COMPANY AND/OR ORGANIZATION IS UNINTENTIONAL AND PURELY COINCIDENTAL.

For general information on our other products and services please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993 or fax 317-572-4002.

For technical support please visit www.wiley.com/techsupport.

WileyTitlePageLogo.eps

Sales

Contact Wiley at (877) 762-2974 or fax (317) 572-4002.

Credits

Executive Editor

Jody Lefevere

Project Editor

Christopher Stolle

Technical Editor

Joyce Nielsen

Copy Editor

Marylouise Wiack

Editorial Director

Robyn Siesky

Business Manager

Amy Knies

Senior Marketing Manager

Sandy Smith

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Barry Pruett

Project Coordinator

Lynsey Stanford

Graphics and Production Specialists

Carrie A. Cesavice
Andrea Hornberger

Jennifer Mayberry

Mark Pinto

Quality Control Technician

Jessica Kramer

Proofreading and Indexing

Shannon Ramsey

Johnna VanHoose Dinse

Screen Artist

Jill Proll

Illustrators

Rhonda David-Burroughs
Cheryl Grubbs

About the Author

Faithe Wempen, M.A., is a Microsoft Office Specialist Master Instructor and the author of over 100 books on computer hardware and software, including the PowerPoint 2010 Bible, Microsoft Office 2010 for Seniors for Dummies, and A+ Certification Workbook for Dummies.

Faithe is currently an adjunct instructor of computer information technology at IUPUI, where she teaches PC hardware and software architecture and A+ certification. Her online courses for corporate clients, including Hewlett Packard, Sony, and CNET, have educated over a quarter of a million students all over the world.

Author’s Acknowledgments

Thanks to my wonderful team of editors at Wiley for another job well done, including Jody Lefevere, Christopher Stolle, Marylouise Wiack, and Joyce Nielsen.

How to Use This Book

Who This Book Is For

This book is for the reader who has never used this particular technology or software application. It is also for readers who want to expand their knowledge.

The Conventions in This Book

577653 fm01.eps

001 Steps

This book uses a step-by-step format to guide you easily through each task. Numbered steps are actions you must do; bulleted steps clarify a point, step, or optional feature; and indented steps give you the result of the action.

002 Notes

Notes give additional information — special conditions that may occur during an operation, a situation that you want to avoid, or a cross-reference to a related area of the book.

003 Icons and Buttons

Icons and buttons show you exactly what you need to click to perform a step.

004 Tips

Tips offer additional information, including warnings and shortcuts.

005 Bold

Bold type shows command names, options, or text or numbers you must type.

006 Italics

Italic type introduces and defines a new term.

Chapter 1: Getting Started with Access 2010

Are you new to Access or upgrading to the latest version? This chapter explains how to create a database as well as how to navigate through the new-and-improved Microsoft Office Access 2010 interface.

577653-co01.eps

An Introduction to Access 2010

Start and Exit Access 2010

Create a Blank Database

Close a Database

Create a Database by Using a Template

Open a Database File

Understanding the Access 2010 Interface

Change the Navigation Pane View

Open and Close an Object

View an Object

An Introduction to Access 2010

Microsoft Access 2010 is a program for creating databases to store business or personal data. You can use Access to create, retrieve, and manage large or small collections of information.

577653-cf0101.eps

Relational Databases

Microsoft Access creates relational databases — that is, databases that can contain multiple tables with links between them. For example, a business may have a Customers table for storing customer contact information and an Orders table for storing information about orders placed. Each customer in the Customers table has a unique ID, and each order in the Orders table references a specific customer ID.

577653-cf0102.eps

Tables, Records, and Fields

In Access, data is stored in tables, and each individual entry in the table is called a record. For example, in a Customers table, the information about each customer is a separate record. Each record is composed of one or more fields that contain individual pieces of data. For example, customer fields may include Name, Address, City, State, and Zip Code.

577653-cf0103.eps

Datasheets and Forms

By default, each table appears as a spreadsheet grid called a datasheet. You can type directly into a datasheet. To make data entry more convenient, some people choose to create on-screen forms, which are like dialog boxes that prompt for field entries. An attractively formatted form is easier and more pleasant to use to enter new records than a plain datasheet.

577653-cf0104.eps

Filters and Queries

It is often useful to display a filtered view of a table. You can filter a table to show only certain records, only certain fields, or both. You can run a one-time filter or you can create a query, which is like a saved filter. Queries also enable you to combine data from multiple related tables into a single datasheet of results.

577653-cf0105.eps

Reports

Tables and query results appear in plain datasheets, which are not very attractive when printed. Reports present data from tables and queries in an attractive, customizable format — complete with titles, headers and footers, and even logos and graphics.

577653-cf0106.eps

Start and Exit Access

Before you can create or open a database file, you must first start Access. Access starts with the File menu open and the New command selected. From here, you can create a new database or open an existing one. When you are finished working with Access, you should exit the program.

577653-cf0107.eps

Start and Exit Access

Start Access

577653-fg0101.eps

001 Click Start.

002 Click All Programs.

577653-fg0102.eps

003 Click Microsoft Office.

004 Click Microsoft Access 2010.

577653-fg0103.eps

The Access program window opens.

Exit Access by Using the Close Button

577653-fg0104.eps

001 Click the Close button ( 577653-ma001.tif ).

Access closes, returning you to your desktop view.

Exit Access by Using the File Menu

577653-fg0105.eps

001 Click File.

002 Click Exit.

Access closes, returning you to your desktop view.

Create a Blank Database

A blank database contains only a single blank table and no other database objects, such as queries or forms. It provides the freedom to create exactly the objects that you want for your project.

577653-cf0108.eps

Create a Blank Database

577653-fg0106.eps

001 Click File.

002 Click New.

003 Click Blank Database.

004 Type a file name for the database.

005 Click Create.

577653-fg0107.eps

• A new database opens, with a new blank table started.

Close a Database

You can close a database without closing Access 2010 itself. Multiple databases can be open at once, each in its own copy of the application, but closing a database when you are finished with it frees up your computer’s memory.

577653-cf0109.eps

Close a Database

577653-fg0108.eps

001 Click File.

The File menu opens.

577653-fg0109.eps

002 Click Close Database.

The File menu stays open, and the New command becomes selected.

Create a Database by Using a Template

You can create a new database based on a template. Templates provide a jumpstart in creating a database by supplying tables, forms, and queries that you are likely to need.

577653-cf0110.eps

Create a Database by Using a Template

577653-fg0110.eps

001 Click File.

002 Click New.

003 Click the template category that you want.

• Sample templates are Microsoft-supplied templates stored on your hard disk.

• Office Online Templates are available from the Internet if you are connected.

577653-fg0111.eps

004 Click the template that best matches your needs.

Note: Under Sample Templates is a Northwind Traders template, which provides sample data and many objects. This database is used for many of the examples in this book.

• The screen for the template category that you chose appears on the right.

005 Type a name for the database file or accept the default name.

006 Click Download.

Note: If you chose a template stored on your local hard drive, the button name in step 6 is Create.

• If you chose an online template, it is downloaded from the Internet.

577653-fg0112.eps

• A form opens. Its appearance depends on the template that you chose.

Open a Database File

You can open a database that you previously created to continue developing its structure, typing data in it, or analyzing its data. Database files can be stored on a local hard drive or on a network or SharePoint server.

577653-cf0111.eps

Open a Database File

Browse for and Open a Database File

577653-fg0113.eps

001 Click File.

002 Click Recent.

• If the desired file appears in the Recent Databases list, click it — and you’re done. Otherwise, proceed to the next step.

003 Click Open.

Note: You can also press ctrl.eps + o.eps instead of performing steps 1 and 2.

577653-fg0114.eps

The Open dialog box opens.

• If necessary, you can navigate to a different location.

004 Click the name of the file that you want to open.

005 Click Open.

The database file opens.

If a Security Warning Message Bar Appears

577653-fg0115.eps

• Click Enable Content.

The message bar closes and the content is enabled.

Why does the Security Warning message bar appear?

A security warning appears when you are opening a database that is stored in a location that is not trusted, such as a file you received as an e-mail attachment, or when opening a file that contains macros (sets of recorded actions). The message implies that the file contains dangerous content, but that is not necessarily so; even a blank database can trigger this warning.

577653-cf0112.eps

How can I prevent the security alert from appearing?

After the first time you click Enable Content for a document that contains macros, the warning will not reappear for that document. If the warning pertains to an untrusted location rather than a document with macros, you can prevent the security alert by adding the location to your Trusted Locations list. To do this, see Chapter 17.

577653-cf0113.eps

Understanding the Access 2010 Interface

Access 2010 has a user interface consistent with those of other Office 2010 applications, including Word and Excel. It contains tabs, a multiple-tabbed Ribbon, and a status bar.

Understanding the Access 2010 Interface

577653-un0103.eps

• File displays a menu of database commands.

• Tabs contain buttons and other controls for working with data.

• The Ribbon displays and organizes tabs.

• Groups organize controls into sections within tabs.

• Clicking this icon opens a dialog box related to the group.

• The Record selector displays the current record number and allows you to navigate to other records.

577653-un0104.eps

• Object tabs provide access to all open database objects, such as tables, reports, and forms.

• The Navigation pane lists all available database objects.

• The scroll bars scroll through a datasheet.

• The status bar displays information about the current object or view.

• View buttons switch between various views of the selected object. The buttons are different depending on what type of object is active. Hover over a button to find out which view each button represents.

• The Quick Access Toolbar provides shortcuts to commonly used features. This toolbar is customizable.

Change the Navigation Pane View

The Navigation pane allows you to view and manage database objects, such as tables, queries, reports, and forms. You can display or hide the Navigation pane as well as change the way it sorts and lists objects.

577653-cf0114.eps

Change the Navigation Pane View

Display the Navigation Pane

577653-fg0116.eps

001 If the Navigation pane is hidden, click this button ( 577653-ma005.tif ).

The Navigation pane appears.

Hide the Navigation Pane

577653-fg0117.eps

001 If the Navigation pane is displayed, click this button ( 577653-ma006.tif ).

The Navigation pane disappears.

Adjust the Size of the Navigation Pane

577653-fg0118.eps

001 Drag the border to the left or right ( 577653-ma105.eps changes to 577653-ma109.tif ).

• A black line shows the new position for the border.

Change the Way Objects Are Displayed

577653-fg0119.eps

001 Click this arrow ( 577653-ma003.tif ).

A menu of object options opens.

002 Click the way that you want to view the object list.

• You can also filter the list to show only a certain type of object.

• You can choose All Access Objects to return to the full list after filtering.

What are some other ways to display and hide the Navigation pane?

Pressing f11.eps toggles the Navigation pane on and off. You can also click Navigation Pane along the left edge of the screen when it is hidden to display it. Another way to hide it is to double-click the divider line between the Navigation pane and the main window when it is displayed.

577653-cf0115.eps

What are those blue bars in the Navigation pane?

Those are category headings. You can expand or collapse a category by clicking its bar.

577653-un0105.eps

Open and Close an Object

You can open any available database object from the Navigation pane. The object appears in the main window to the right of the Navigation pane; from there, you can work with its content.

577653-cf0116.eps

Open and Close an Object

Open an Object

577653-fg0120.eps

001 If necessary, click a category to expand it.

002 Double-click the object.

• To switch among open objects, click the tab of the object that you want.

Close an Object

577653-fg0121.eps

001 Right-click on the object’s tab.

A shortcut menu opens.

002 Choose Close from the shortcut menu.

The object closes.

View an Object

You can display objects in different views. The available views depend on the object type but usually include a view for using the object, such as the Datasheet view, and a view for modifying the object, such as Design view.

577653-cf0117.eps

View an Object

Select a View from a Menu

577653-fg0122.eps

001 Right-click on an open object’s tab.

A shortcut menu opens.

002 Choose the view that you want from the shortcut menu.

Select a View by Using the View Buttons

577653-fg0123.eps

001 Click the button for the view that you want.

Note: The buttons that are available change depending on the object type.

• To determine which view a button represents, you can point to it to see a screen tip.

Teach Yourself Visually®Access® 2010

Teach Yourself Visually^®Access^® 2010