Published with the authorization of Microsoft Corporation by: O’Reilly Media, Inc.
1005 Gravenstein Highway North Sebastopol, California 95472 Copyright © 2010 by Curtis D. Frye
Complying with all applicable copyright laws is the responsibility of the user. All rights reserved. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without express written permission of O’Reilly Media, Inc. Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 WCT 5 4 3 2 1 0
Microsoft Press titles may be purchased for educational, business or sales promotional use. Online editions are also available for most titles (http://my.safaribooksonline.com). For more information, contact our corporate/institutional sales department: (800) 998-9938 or corporate@
oreilly.com. Visit our website at microsoftpress.oreilly.com. Send comments to [email protected].
Microsoft, Microsoft Press, ActiveX, Excel, FrontPage, Internet Explorer, PowerPoint, SharePoint, Webdings, Windows, and Windows 7 are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Other product and company names mentioned herein may be the trademarks of their respective owners.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.
This book expresses the author’s views and opinions. The information contained in this book is provided without any express, statutory, or implied warranties. Neither the author, O’Reilly Media, Inc., Microsoft Corporation, nor their respective resellers or distributors, will be held liable for any damages caused or alleged to be caused either directly or indirectly by such information.
Acquisitions and Developmental Editor: Kenyon Brown Production Editor: Loranah Dimant
Copy Editor: Nancy Sixsmith
Editorial Production: Octal Publishing, Inc. Technical Reviewer: Mark Reddin Compositor: Ron Bilodeau Illustrator: Robert Romano Indexer: Ginny Munroe
iii
Introduction: About This Book
1
No Computerese! . . . . 1
A Quick Overview . . . . 2
A Few Assumptions . . . . 4
A Final Word (or Two) . . . . 4
What’s New in Access 2010?
5
Managing Access Settings and Files in Backstage View . . . . 6 Customizing the Access 2010 User Interface . . . . 7 Creating Databases Using Improved Templates . . . . 7 Building Databases by Re-Using Application Parts . . . . 7 Creating Navigation Forms . . . . 8 Formatting Database Objects Using Office Themes . . . . 8 Gaining Insights into Data Using Conditional Formatting . . . . 9 Defining Calculated Fields Using the Improved Expression Builder . . . . 9Introducing Access 2010
11
Starting Access 2010 . . . . 13 Opening a Database . . . . 16 Viewing Multiple Database Objects . . . . 18 Closing a Database and Exiting Access 2010 . . . . 19 Displaying and Managing Database Objects . . . . 20 Using the Access 2010 Help System . . . . 221
2
3
iv Contents
Creating a Database
25
Viewing a Sample Database . . . . 28 Creating a New Database . . . . 29 Creating Databases Using Database Templates . . . . 31 Creating a New Table in Design View . . . . 33 Creating a New Table by Typing . . . . 35 Creating a New Table Using a Template . . . . 36 Creating a New Table Using Application Parts . . . . 38 Setting a Primary Key . . . . 39 Getting Data from Other Access 2010 Tables . . . . 40 Creating Relationships Between Tables . . . . 44 Enforcing Referential Integrity . . . . 46Customizing Fields
47
Working with Tables . . . . 48 Assigning a Data Type . . . . 50 Viewing or Changing Field Properties . . . . 52 Formatting Field Contents . . . . 54 Creating Input Masks . . . . 56 Assigning Required Fields and Requiring Data Entry . . . . 58 Setting Default Values . . . . 59 Indexing Field Values . . . . 60 Validating Data Entry . . . . 61 Creating a Lookup Field . . . . 62 Creating an Append-Only Memo Field . . . . 66 Creating an Attachment Field . . . . 674
5
Contents v Entering Data Using AutoCorrect . . . . 72 Adding and Editing Text . . . . 75 Manipulating Columns . . . . 77 Modifying Columns and Rows . . . . 79 Viewing a Subworksheet . . . . 80 Filtering Table Records . . . . 82
Creating Forms
87
Creating a Simple Form . . . . 88 Creating a Form Using the Form Wizard . . . . 89 Creating a Form in Design View . . . . 90 Creating a Multiple Items Form . . . . 92 Modifying an Existing Form . . . . 93 Adding and Deleting Form Controls . . . . 95 Adding a Date Picker Control . . . . 98 Creating a Subform . . . . 99 Displaying a Form and Its Datasheet Simultaneously . . . . 101Creating Queries
103
Creating a Query Using the Query Wizard . . . . 106 Editing a Query in Design View . . . . 110 Using Criteria to Focus Query Results . . . . 113 Using Queries to Calculate Values . . . . 115 Creating a Parameter Query . . . . 116 Finding Duplicate Records . . . . 1176
7
8
vi Contents Finding Unmatched Records . . . . 119 Writing Query Results to a New Table . . . . 121 Creating an Update Query . . . . 122 Creating a Crosstab Query . . . . 123 Finding the Largest and Smallest Values in a Field by Using a Query . . . . 125
Creating Reports
127
Creating a Report Using the Report Wizard . . . . 128 Creating a Summary Report . . . . 130 Creating a Report in Design View . . . . 132 Modifying an Existing Report . . . . 133 Adding and Deleting Report Controls . . . . 135 Calculating Values in a Report . . . . 137 Grouping Report Records . . . . 139 Creating a Subform or Subreport . . . . 141 Creating Mailing Labels . . . . 142Beautifying Forms and Reports
145
Formatting Text . . . . 146 Applying Office Themes . . . . 148 Setting Control Appearance . . . . 152 Adding Lines, Shapes, and Borders . . . . 156 Showing Gridlines in a Report . . . . 158 Coloring Alternate Rows in a Form or Report . . . . 160 Adding a Totals Row to a Worksheet . . . . 161 Adding a Picture . . . . 162 Applying Conditional Formatting . . . . 1649
10
Contents vii
Setting Image Height and Width . . . . 170
Creating Charts in Access 2010
173
Creating a Chart . . . . 174Formatting Chart Elements . . . . 176
Customizing Chart Axes . . . . 179
Add Information to a Chart . . . . 181
Changing a Chart’s Type . . . . 184
Interacting with Other Programs
185
Inserting a New Object . . . . 187 Manipulating Objects . . . . 189 Inserting Excel 2010 Charts and Worksheets . . . . 192 Importing Data from Another Access 2010 Database . . . . 194 Linking to a Table in Another Access 2010 Database . . . . 196 Working with Hyperlinks . . . . 197 Importing Data from an Excel 2010 File . . . . 200 Importing Data from a Text File . . . . 202 Importing Data from an XML File . . . . 204 Exporting Data to a Text File . . . . 206 Exporting Data to Another Access 2010 File . . . . 208 Exporting Data to an XML File . . . . 210 Saving Database Objects As Web Files . . . . 212 Analyzing Data with Excel 2010 . . . . 214 Publishing Data to Word 2010 . . . . 216 Collecting Data from E-mail Messages . . . . 21811
12
viii Contents
Administering a Database
221
Encrypting a Database . . . . 223 Locking Database Records . . . . 225 Creating a Navigation Form . . . . 227 Documenting a Database . . . . 229 Setting Startup Options . . . . 230Customizing Access 2010
235
Adding Commands to the Quick Access Toolbar . . . . 236 Modifying the Ribbon User Interface . . . . 238Presenting Table and Query Data Dynamically
247
Creating a PivotTable . . . . 250 Adding and Removing PivotTable Fields . . . . 252 Pivoting a PivotTable . . . . 254 Filtering PivotTable Data . . . . 255 Formatting a PivotTable . . . . 257 Creating a PivotChart . . . . 258 Index . . . .26113
14
15
1
In this section:
Introduction:
About This Book
I
f you want to get the most from your computer and your software with the least amount of time and effort—and who doesn’t?—this book is for you . You’ll find Microsoft® Access®2010 Plain &
Simple to be a straightforward, easy-to-read refer-ence tool . With the premise that your computer should work for you, not you for it, this book’s purpose is to help you get your work done quickly and efficiently so that you can get away from the computer and live your life .
No Computerese!
Let’s face it: When there’s a task you don’t know how to do but you need to get it done in a hurry, or when you’re stuck in the middle of a task and can’t figure out what to do next, there’s nothing more frustrating than having to read page after page of technical background material . You want the information you need—nothing more, nothing less—and you want it now! It should be easy to find and understand . That’s what this book is all about . It’s written in plain English— no jargon . There’s no single task in the book that takes more than a couple of pages . Just look up the task in the index or the table of contents, turn to the page, and you’ll find the information1
■ ■ No Computerese! ■ ■ A Quick Overview ■ ■ A Few Assumptions ■you need laid out in an illustrated step-by-step format . You won’t get bogged down by the whys and wherefores: Just follow the steps and get your work done . Occasionally, you might have to turn to another page if the procedure you’re working on is accompanied by a “See Also .” That’s because a lot of tasks overlap, and I didn’t want to keep repeating myself . Some useful “Tips” are scattered here and there, and a “Try This” or a “Caution” is thrown in once in a while, but by and large I’ve tried to remain true to the heart and soul of a Plain & Simple book, which is that the information you need is available to you at a glance .
Useful Tasks…
Whether you use Microsoft Access 2010 at home or on the road, I’ve tried to pack this book with procedures for every-thing I could think of that you might want to do, from the simplest tasks to some of the more esoteric ones .…And the Easiest Way to Do Them
Another thing I’ve tried to do in this book is to identify and document the easiest way to accomplish a task . Access 2010 often provides a multitude of methods to accomplish a single end result—which can be daunting or delightful, depend-ing on the way you like to work . If you tend to stick with one favorite and familiar approach, I think the methods described in this book are the way to go . If you like trying out alternative techniques, go ahead! The intuitiveness of Access 2010 invites exploration, and you’re likely to discover ways of doing things that you think are easier or that you like better than mine . If you do, that’s great! It’s exactly what the developers of Access 2010 had in mind when they provided so many alternatives .
A Quick Overview
Your computer probably came with Access 2010 preinstalled, but if you have to install it yourself, the setup routine makes installation so simple that you won’t need my help anyway . So, unlike many computer books, this one doesn’t start with installation instructions and a list of system requirements . Next, you don’t need to read this book in any particular order . You can jump in, get the information you need, and then close the book and keep it near your computer until the next time you need to know how to get something done . But that doesn’t mean the information is scattered about with wild abandon . The book is organized so that the tasks you want to accomplish are arranged in two levels . You’ll find the general type of task you’re looking for under a main sec-tion title such as “Creating a New Database,” “Getting Help,” “Exporting Data to a Text File,” and so on . Then, in each of those sections, the smaller tasks within the main task are arranged in a loose progression from the simplest to the more complex . Section 1, which you’re reading now, introduces MicrosoftAccess 2010 Plain & Simple and tells you what to expect in
this book . Section 2 covers what’s new in Access 2010, from the new Backstage View that helps you manage your data-bases effectively to the new ways you can summarize your data visually . Sections 3 and 4 cover the basics of operating a database: starting Access 2010 and shutting it down, sizing and arranging program windows, getting help from within the program and on the Web, what types of objects are avail-able in an Access 2010 database, and the role each of those objects fulfills . There’s also a lot of useful information about designing and creating a new database, either from scratch or by using built-in database templates; navigating within a database; creating relationships between tables; and getting data from other Access 2010 databases . 2 A Quick Overview
add, delete, and rearrange fields; and how to make data entry easier for you and your colleagues . You’ll find the informa-tion helpful whether you want to change how a field displays its data, set a default value for a field, ensure that the data entered into the field is appropriate for that field, or enter table data by picking the proper value from a list . Section 6 focuses on working with entire tables, rather than individual fields in a table . Here’s where you’ll find information about entering data quickly, finding and replac-ing table text, and modifying how Access 2010 displays your data in a table . There’s also a short section here on filtering a table’s contents, which lets you limit the data displayed to exactly what you need to make a decision . Sections 7 through 10 are all about building database objects to take best advantage of your data: creating forms, which let you present your data in an attractive format and enter new table records; building reports, which summarize your data and make it easy to create mailing labels; defining queries, which let you ask specific questions of your table data; and changing the appearance of your forms and reports to make them more attractive or to conform with a company’s color scheme . Section 10 describes how you can use condi-tional formatting, which is new in Access 2010, to add visual cues that enable you and your colleagues to evaluate report data at a glance . summarize large data collections quickly, compare historical data to current trends, and provide context for when you dis-cuss specific data points from your tables . Section 12 is about interacting with other programs, such as by including files created in other programs in your databases, adding pictures to forms and reports, or includ-ing Microsoft Excel charts in your database . You will also find out how to exchange data with other programs, whether that means exporting Access 2010 table or query data to another program or reading data from another program’s files into Access 2010 . The final sections, 13 through 15, deal with more advanced topics: administering your database so you can, if necessary, identify the data that is open for anyone to look over and separate that data from tables or queries that might contain more sensitive information; creating navigation forms that make it easier to move around in your databases; custom-izing Access 2010 by changing the items that appear on the program’s Quick Access Toolbar and the Ribbon user interface; and creating forms that let you dynamically reorganize your data . If you think these tasks sound complex, rest assured they’re not—Access 2010 makes them so easy that you’ll sail right through . A Quick Overview 3
A Few Assumptions
I had to make a few educated guesses about the audience when I started writing this book . Perhaps you use Access just for personal reasons—keeping track of your books, music, contacts, and so on . Perhaps you use Access at work to main-tain records of your inventory, customers, and the orders they place . Or maybe you run a small home-based business . Taking all these possibilities into account, I assumed that you’d need to know how to create, modify, and work with all the basic Access database objects; to administer the database; and to share the data on the Internet or over your company’s internal network . Another assumption I made is that you have an inherent curiosity about what you can do with Access 2010 . Rather than show you how to perform specific tasks, such as designing a database to track client interactions or customer orders, I assumed you wanted a broad base of experience from which to work . I hope that the tasks in this book provide a solid foundation for you to learn more about Access and what it can do for you .A Final Word (or Two)
I had three goals in writing this book:
■
■ Whatever you want to do, I want the book to help you get it done .
■
■ I want the book to help you discover how to do things you didn’t know you wanted to do .
■
■ And, finally, if I’ve achieved the first two goals, I’ll be well on the way to the third, which is for my book to help you enjoy using Access 2010 . I think that’s the best gift I could give you to thank you for buying my book .
I hope you’ll have as much fun reading and using Microsoft
Access 2010 Plain & Simple as I’ve had writing it . The best way
to learn is by doing, and that’s how I hope you’ll use this book . Jump right in!
In this section:
5In this section:
What’s New in
Access 2010?
O
ne of the first things you’ll notice about Microsoft Access 2010 is that the program incorporates the Microsoft Fluent interface, also known as the Ribbon, which was introduced in Access 2007 . If you used Access 2003 or an earlier version of Access, you’ll need to spend only a little bit of time working with the new user interface to bring yourself back up to your usual proficiency . If you used Access 2007, you’ll immediately notice one significant change: the Microsoft Office Button, located at the top left corner of the program window in Access 2007, has been replaced by the File tab . This section of the book introduces many of the new features in Access 2010: the new Backstage view you use to manage your files; new templates and Application Parts you can use to build databases more quickly; new abilities to customize the Ribbon user interface; new Office Themes, which make formatting simpler and more consistent; and new data visualization techniques, which include Office Themes and conditional formatting .2
■■ Managing Access Settings and Files in Backstage View
■
■ Customizing the Access 2010 User Interface
■
■ Creating Databases Using Improved Templates
■
■ Building Databases by Re-Using Application Parts
■
■ Creating Navigation Forms ■
■ Formatting Database Objects Using Office Themes
■
■ Gaining Insights into Data Using Conditional Formatting
■
■ Defining Calculated Fields Using the Improved Expression Builder
All these improvements combine to make Access 2010 a friendly yet powerful program you can use effectively right out of the box, with a little help from Microsoft Access 2010 Plain &
Simple .
Managing Access Settings and Files
in Backstage View
After releasing the 2007 Microsoft Office System, the Microsoft Office User Experi-ence team re-examined the programs’ user interfaces to determine how they could be improved . During this process, they dis-covered that it was possible to divide user tasks into two categories: “in” tasks, such as table and report creation, which affect the contents of the database directly; and “out” tasks, such as saving and printing, which could be considered file manage-ment tasks . When the User Experience and Access teams focused their attention on the Access 2007 user interface, they discovered that several file management tasks were sprinkled among the Ribbon tabs that con-tained content-related tasks . The Access team moved all the file management tasks under the File tab, which users can click to display these commands in the new Back-stage view . 6 Managing Access Settings and Files in Backstage ViewInterface
When the Microsoft Office User Experience team designed the Ribbon interface for Access 2007, they allowed you to modify the program window by adding and removing commands on the Quick Access Toolbar . In Access 2010, you can still modify the Quick Access Toolbar, but you also have much more ability to change the Ribbon interface . Users can hide or display built-in Ribbon tabs, change the order of built-in Ribbon tabs, add custom control groups to a Ribbon tab, and create custom Ribbon tabs that, in turn, can contain custom groups . These custom groups provide easy access to existing Ribbon controls as well as custom controls that run macros stored in the database .Creating Databases Using Improved
Templates
Every user is different, but the Access product team has dis-covered through its research that users frequently create the same types of databases . Some users create databases to track upcoming events; others use them to track the students and faculty at an educational institution; and still others track mar-keting projects, sales, and tasks they’ve been assigned . Access 2010 comes with a number of built-in database templates you can use to create a database that is prepopu-lated with objects suited for your application . Access 2010 includes two types of database templates: regular databases and Web databases . A regular database is stored on your computer and can just be used just by someone who has per-mission to use your computer and to view your files . A Web database can be used on your computer, or it can be stored on a Microsoft SharePoint server . Uploading a database to a SharePoint server enables other users to interact with the database using a Web browser . Uploading a database to a SharePoint site is beyond the scope of this book, but you canBuilding Databases by Re-Using
Application Parts
After you’ve used Access 2010 for a while, you’ll almost cer-tainly find that you can use database objects you created previously as the basis for new database objects . Access 2010 comes with a number of built-in form templates, called Appli-cation Parts, which you can use to accelerate your work . When you click the Create tab on the Ribbon and then click Appli-cation Parts, Access 2010 displays the Application Parts that are available to you . Some of the Application Parts are blank forms you can link to another table, but the Contacts, Issues, and Tasks templates in the Quick Start section of the gallery enable you to create and link forms by moving through the steps of a form creation wizard . Customizing the Access 2010 User Interface 7Creating Navigation Forms
Most databases contain multiple tables, forms, reports, and queries . If you created the database, it can be difficult to remember which elements perform which functions within your database . Your life gets even more complicated if you’re using a database someone else created, or even one you cre-ated some time ago . In those instances, you’ll need to exam-ine the database’s contents to discover (or rediscover) how to enter and analyze your data . In Access 2007 and earlier versions of the program, you could create switchboard forms to guide users to the objects they should use for certain tasks . The procedure for creating switchboards was cumbersome, so the Access program team created navigation forms . You can drag forms and reports onto a navigation form, which means you and your colleagues can gain easy access to the elements that enable you to enter, edit, and summarize your database’s contents .Formatting Database Objects Using
Office Themes
Access 2010 includes new capabilities to apply attractive color schemes to your database objects . You could always change the appearance of your forms and reports, but Access 2010 includes collections of complementary colors called Office Themes . You can use the Office Themes that are installed with Access 2010 to format your database objects, or you can create your own themes that reflect your personal design choices or your company’s official color scheme . 8 Creating Navigation FormsConditional Formatting
Microsoft Access has always provided powerful tools you can use to manage and summarize substantial data collections . Access 2010 extends those capabilities by allowing you to apply conditional formats that summarize your data visually . A conditional format changes to reflect the numeric values in a report field . For example, if you create a report to summarize your company’s sales performance, you could define condi- tional formats that indicate whether your company’s perfor-mance exceeded its goals, met expectations, or fell short .Improved Expression Builder
In Access 2007 and earlier versions, you could define a field’s value as a function of the values in other fields . For example, you could calculate sales tax due on a purchase by multiply-ing the order’s total price by the appropriate sales tax rate . To create this type of field, called a calculated field, you use the Expression Builder to define the calculation . Unfortunately, the Expression Builder in Access 2007 was somewhat difficult to use because of its crowded user interface and lack of contex-tual help tools . In Access 2010, the improved Expression Builder pro-vides a much cleaner user experience . Rather than force you to guess which commands you can use when you create an expression, the Expression Builder makes available only those commands that can be used in the current expression . The Expression Builder also offers to complete the commands you enter using IntelliSense, which enables you to enter your expressions more quickly and with fewer typing errors . Gaining Insights into Data Using Conditional Formatting 9
In this section:
11In this section:
Introducing
Access 2010
M
icrosoft Access 2010 is designed to help you store, com-bine, and ask questions of large collections of data relevant to your business or your home life . You can create databases to track products and sales for a garden supply company, or, just as easily, build databases to keep track of your books and holiday card lists . Regardless of the specific use you have in mind, Access 2010 is a versatile program you can use to store and retrieve data quickly . Working with Access 2010 is pretty straightforward . The program has a number of templates you can use to create entire databases or just parts of them . You also have the freedom to create databases and their components from scratch, giving you the flexibility you need to build any database . This section of the book covers the basics: what a data-base is and how it works, starting Access 2010, shutting it down, opening databases, displaying database objects, and getting help using Access 2010 . There’s also an overall view of the Access 2010 window with labels for the most important parts of the program . You can use that image as a touchstone for learning more about Access 2010 .3
■ ■ Introducing Databases ■ ■ Starting Access 2010 ■■ Surveying the Access 2010 Window ■
■ Opening a Database ■
■ Viewing Multiple Database Objects ■
■ Closing a Database and Exiting Access 2010
■
■ Displaying and Managing Database Objects
■
Introducing Databases
Storing Data on Index CardsBefore computers, a popular way to store data was on index cards . If you ran a gardening supply store, you could keep track of your products by creating a card for each product, dividing the cards into product categories, and then alphabetizing the cards in each section by product name . Each card would contain relevant data such as the product’s name, unique identifier, category, price, description, and the supplier’s name and phone number . To find all the products from a specific supplier, you either needed to keep track of the products on a separate sheet of paper or go through the cards and pull every one representing a product made by that supplier .
Storing Data on the Computer
If you store the same data on the computer, however, you can find all the products from a specific supplier much more easily . As an example, you might create a Microsoft Word table with a column for each type of data you want to store . With the list in a Word table, you can change the order of the table rows to group all the products from one supplier together; all you need to do then is scroll down through the table until you find the products from the supplier you want . Using Word to store your data isn’t the best solution, how-ever . One limitation is that there’s no way to combine informa-tion from two tables, so you need to write the supplier’s phone number in every row representing a product from that supplier . If that phone number changes, you need to change the phone number entry in every table row representing a product from that supplier .
Storing Data in a Database
Databases, by contrast, are designed to combine data from several sources into a single table . Once data is entered into a table, it can be combined with other tables in the database to produce valuable information . It’s possible, for example, to store information about suppliers in one table and information about purchase orders in another table . If a supplier changes its phone number, you need to change the phone number only once . Subdatasheet Datasheet 12 Introducing Access 2010
Start Access 2010
1 Click the Start button on the taskbar . 2 Click All Programs . 3 Click Microsoft Office . 4 Click Microsoft Access 2010 . 2 1 3 4 After you install Access 2010 on your computer, you can run it to create new databases or to work with existing databases . There are two easy ways to run Access 2010: You can start it from the Start menu or by double-clicking a shortcut on your desktop . Regardless of the method you choose, you end up with the power of an Access 2010 database at your fingertips in the few seconds it takes your computer to start the program . Starting Access 2010 13Create a Shortcut for Access 2010
1 Click the Start button on the taskbar . 2 Click All Programs . 3 Click Microsoft Office . 4 Right-click Microsoft Access 2010 . 5 Point to Send To . 6 Click Desktop (create shortcut) . 7 Press the Escape key twice to close the menu . 2 1 3 5 4 6 You can rename a shortcut by right-clicking it, clicking Rename on the shortcut menu that appears, typing a new name for the shortcut, and pressing Enter . Tip 14 Starting Access 2010■
■ The title bar displays the name of the database and the window control buttons .
■
■ The tabs on the Ribbon enable you to display different types of commands based on the category you select . ■
■ The Ribbon contains commands that reflect the active Contextual tab, your position in the database, and the selected objects .
■
■ The Navigation Pane displays database objects of the type you select .
■
■ The object window displays any open database objects . ■
■ The status bar indicates the progress of any ongoing processes .
Main tab Ribbon Title bar
Close
Navigation Pane Status bar Object window Restore Minimize
Opening a Database
Open a Database on Startup
1 Start Microsoft Access 2010 . 2 Click Open . 3 Navigate to the folder with the data-base you want to open . 4 Double-click the file you want to open . 2 3 4 When you start Access 2010, the program opens in Backstage view . A list of recently opened files appears on the left edge of the window, below the File tab . You can select the file you want to open from the list that appears . If the file you want isn’t on that list, click Open to display the Open dialog box . From the Open dialog box, you can navigate to the folder with the data-base you want to open . 16 Opening a DatabaseOpen a Recently Used Database
1 Start Microsoft Access 2010 . 2 Click Recent . 3 Click the database you want to open . 2 3 The names of the last four databases you opened appear in the File tab menu, just above the Info item . Tip Opening a Database 17Viewing Multiple Database Objects
Access 2010 enables you to work with multiple database objects efficiently . In Access 2010, the program displays a tab representing each open object at the top of the object window . If you want to display an object that is currently hidden, you can click that object’s tab to display it . When you display an object in the object window, you can close the object by click-ing its Close button (located at the top-right corner of the object window) .Close a Database Object
1 Click the Close button to close the
displayed database object .
Scroll Within a Database Object
• Click the up or down arrow on the vertical scroll bar to scroll up and down within the object . • Click the left or right arrow on the horizontal scroll bar to scroll left or right within the object . 1
Tab Close button Vertical scroll bar
Horizontal scroll bar
If you prefer to have Access 2010 display data-base objects in the same manner that Windows 7 displays program windows (with maximize, minimize, and close buttons), click the File tab and then click Options . In the Access Options dialog box, display the Current Database tab . Then, in the Document Window Options sec-tion of the tab, select the Overlapping Windows option button and click OK . Tip 18 Viewing Multiple Database Objects
When you finish working with an Access 2010 database, close it to free up system resources and let your computer run other programs more quickly . By the same token, after you com-plete all the work you need to do in Access 2010, you should exit the program entirely . If you work in a corporate environ-ment or if your database contains sensitive information such as client addresses or account numbers, you should always close your database any time you walk away from your computer . Although it’s unlikely that someone will wander by and copy or write down sensitive information, it’s better that you make it as difficult as possible for anyone with bad intentions to make off with your data .
Close a Database
1 Click the File tab . 2 Click Close Database . 1 2 If you finish working with the current data-base and want to open another, save your work (if necessary), click the File tab, and then click Open . Tip Clicking the Close button at the top-right corner of the Access 2010 window will exit Access 2010, not just close the active database . CautionExit Access 2010
1 Click the File tab . 2 Click Exit . 1 2 Closing a Database and Exiting Access 2010 19Displaying and Managing Database Objects
Access databases typically contain lots of objects: tables, where you store your data; forms, which enable you to enter data into tables quickly; queries, which enable you to extract subsets of data from your tables; and reports, which summarize your table data and query results . Access 2010 organizes your objects in the Navigation pane, found at the left edge of the program window . You can choose the order in which Access 2010 dis-plays the objects and even choose whether to display a subset of your objects, but you always know where your objects are if you want them . The Shutter Bar, located at the top of the Navigation pane, enables you to select how you want to view your database’s objects . If you want to maximize the size of the object window, you can hide the Shutter Bar . When you have more than one database object open, Access 2010 represents the objects as a series of tabs across the top of the database window . All you have to do to display an object is click its tab, and closing an object is as simple as clicking the Close button at the top-right corner of the object’s window .Open a Database Object
1 If necessary, click the Shutter Bar Open/Close button to display the Navigation pane . 2 Click the Shutter Bar . 3 If there’s no check mark next to All Access Objects, click All Access Objects . 4 If necessary, click the Show Details button to display the objects in the desired category . 5 Double-click the object you want to open . 1 3 2 4 5 20 Displaying and Managing Database Objects
Control Object Display in the
Navigation Pane
1 If necessary, click the Shutter Bar Open/Close Button to display the Navigation pane . 2 Click the Shutter Bar . 3 Follow one of these steps: • Select Tables and Related Views to display only your data tables and views . • Select Object Type to display all the objects in your database, sorted by object type . • Select Created Date to display all the objects in your database, sorted by the date the objects were created . • Select Modified Date to display all the objects in your database, sorted by the date the objects were last changed . 1 2 Displaying and Managing Database Objects 21Using the Access 2010 Help System
If you need to get help using Access 2010, there are a number of places you can look . One option is to right-click an object (such as a database object or a graphic) to see a list of things you can do with the object . You can also open the Access 2010 Help files and browse through them to find the answer to a specific question or just to explore .Get Suggested Commands from
Shortcut Menus
1 Right-click any Access 2010 object to
see the shortcut menu of commands .
2 Click a command .
Get Microsoft Access 2010 Help
• Click the Microsoft Access Help button on the Ribbon .
1 2
Get Help on the Web
1 Click the Microsoft Access Help button on the Ribbon . 2 Click the Search button’s down arrow and select where you want to look for help . 3 Type your search terms . 4 Click the Search button . 5 Browse the topics displayed for help and resources . 3 4 2 5 1 You can visit the Microsoft Office Assistance Center site directly by opening your Web browser and typing http://office.micro-soft.com/en-us/help/FX100485361033.aspx in the Address box . Tip If you use Access 2010 at work, you should definitely visit the Microsoft Office Online site at http://office.microsoft.com/ en-us/FX100647101033.aspx . The Office Online site offers help files and product summaries, so you should have no trouble finding articles and demonstrations that can help you get your work done quickly . Tip Using the Access 2010 Help System 23In this section:
Creating a Database 25In this section:
Creating a
Database
I
f none of the databases you can create using one of the Access 2010 database templates meets your needs, you can create a database and the tables it contains from scratch . When you choose to create a new table, you have complete control over the number of fields, the names of the fields, and what sort of data they can store . After you create the table, you can also add fields from the Field Templates task pane, which contains a wide variety of fields to store such data as times, dates, phone num-bers, postal codes, names, addresses, and comments . Once you create a table, whether in Design view or by using a template, you can change its structure as needed, such as renaming an existing field or adding an entirely new field to store additional data .4
■■ Viewing a Sample Database ■
■ Creating a New Database ■
■ Creating Databases Using Database Templates
■
■ Creating a New Table in Design View ■
■ Creating a New Table by Typing ■
■ Creating a New Table Using a Template ■
■ Creating a New Table Using Application Parts
■
■ Setting a Primary Key ■
■ Getting Data from Other Access Tables ■
■ Creating Relationships Between Tables ■
Designing a Database
The most basic object in a database is the table in which you store your data . You might be tempted to jam every type of data you want to store into a single table, but that’s hardly ever the right way to design tables in a database . The follow-ing guidelines will help you create efficient tables .One Table per Object
The first rule in creating database tables is to ensure that every table stores data about one type of object, whether that object is a person, a product, or an order . As an example, consider the Suppliers table from the Northwind sample database . This table has a field for everything you want to know about a supplier, with nothing extra . Consider this alternative design, which adds fields to describe the supplier’s products . Aside from repeated data, deleting the record represent-ing the last product from a supplier removes all information about that supplier from your database . Rather than risk losing that information, it is much more efficient to create one table for the suppliers and another for the products . ID field 26 Creating a Database
Give Every Table a Primary Key
Another important consideration in creating a table is to assign a primary key . This field contains a value that sets a record apart from all other records in the table . In the Northwind database’s Shippers table, that role is filled by the ID field . It’s also possible to create a primary key made up of more than one field, as in the table shown in the following illustra-tion . (Please note that this table is just a sample; it isn’t in the Northwind database .) The ProductID field identifies the product and the Sup-plierID field identifies the product’s supplier . Because you can order the same product from more than one supplier, both the ProductID and SupplierID fields are needed to distinguish a record from all other records in the table .
Include Foreign Keys
A final thing you can do to make your tables more efficient is to include primary key fields from other tables, as with the SupplierIDs field in the Products table .
When a primary key from one table is stored in another table, it is called a foreign key . As you see later in this chapter, you can use foreign keys to create relationships between tables .
Viewing a Sample Database
One of the best ways to get a feel for using Access is to work with an existing database . The Northwind database, which you install along with Access, is a complete database with tables, queries, reports, and forms you can examine to see what goes into a solid database design .Open a Sample Database
1 Start Access 2010 . 2 Click Sample Templates . 3 Click Northwind . 4 Click Create . 2 3 4 The Northwind database contains a form that appears when you open the database . The form enables you to log in using different user accounts . If a database you create doesn’t include accounts for individual users, that form won’t appear . Tip 28 Viewing a Sample DatabaseIf you want to create a new database from scratch, you can do so by creating a blank database and then adding your own tables and other objects . You can also create a new
database based on an existing database, saving yourself lots of time and effort .
Begin a New Database
1 Click the File tab . 2 Click New . 3 Click Blank Database . 4 Type a name for your database . 5 Click the folder icon . 6 Navigate to the folder where you want to store the database . 7 Click OK . 8 Click Create . 1 3 6 7 8 5 2 4 Creating a New Database 29Create a New Database Based on
Another Database
1 Open the database you want to use as a model and then click the File tab . 2 Click Save Database As . 3 Navigate to the directory where you want to save the new database . 4 Type a different name for the new database . 5 Click Save . 1 2 3 4 5 If you don’t type a new name for your database, you will just save the existing database, not create a new file . Caution 30 Creating a New DatabaseThe Microsoft Access product team has one primary goal: to make it as easy as possible for you to create powerful and useful databases that help you get your work done quickly . You must have a great database program to meet that goal, but it also helps if the developers draw on the rest of the Microsoft community to design databases you can use without modifica- tion (for tasks such as tracking your contacts, sales, or work-outs) or to use as the basis for your own custom databases . You can find those preexisting databases, called templates, installed on your computer and also on the Office .com Web site .
Create a Database from a Template
1 If necessary, click the File tab and then choose Close Database to close any open database . 2 Click a Template Category . 3 Click the desired template . 4 Type a name for your database . 5 Click Create . 1 2 3 5 4 You can select the folder where you want to save your data-base file by clicking the folder icon next to the File Name box in Backstage view . Tip Creating Databases Using Database Templates 31Find Database Templates
Online
1 If necessary, click the File tab and then click Close Database to close any open database . 2 Click an Office .com template category . 3 Click the desired template . 4 Type a name for your database . 5 Click Create . 1 2 3 5 4 You can search for templates on the Office .com Web site by typing one or more keywords in the Search Office .com For Templates box and clicking the Start Searching button . Tip 32 Creating Databases Using Database TemplatesAfter you plan your database, you can start creating tables to store your data . One good way to define your table’s struc-ture is to start out in Design view . When you look at a table in Design view, you can define the name of a field, select the type of data to go into that field (numerical values, currency values, text, dates, phone numbers, and so on), and write a description for that field . Field descriptions are one of the most overlooked parts of database design . It’s easy to overlook them or see them as unnecessary, but the reality is that you might forget what sort of data is supposed to go into a field . You could also leave the company and force someone else to figure out what a particular field should contain . If you’ve ever been handed someone else’s database, you probably encountered a field you weren’t sure about . Please make everyone’s job easier by adding a description!
Create a Table in Design View
1 Click the Create tab . 2 In the Tables group, click Table Design . 3 Type a name for the first field and press Tab . 4 Click the Data Type down arrow . 1 2 Creating a database table that doesn’t include a primary key field makes it difficult to get data out of your table by using queries and in relationships with other tables . You should always have a pri-mary key field in every table you create . Caution Creating a New Table in Design View 335 Select a data type . 6 Type a description for the field . 7 Press Tab and repeat steps 4 through 7 to add fields . 8 Click the Save button . 9 Type a name for the table . 10 Click OK . 11 When asked whether you want Access 2010 to assign a primary key to your table, follow one of these steps: • Click Yes to have Access 2010 assign a primary key . • Click No to have Access 2010 create the table without a primary key . • Click Cancel to continue without saving your table . 12 Click the Close box for the table . 8 3 5 4 6 12 9 10 34 Creating a New Table in Design View
It might seem strange to want to enter data into a table with-out first defining the table’s structure, but there’s one scenario where it makes perfect sense: when you’re in a hurry and you have to get the data into the database quickly . When you type data into a blank table, Access 2010 assigns generic names to the fields, such as Field1, Field2, and so on . After the data’s in the table, you can open the table in Design view and name the fields, define data types, and so on .
Create a New Table by Typing
1 Click the Create tab . 2 In the Tables group, click Table . 3 Type the data for the first new field and press Tab . 4 Repeat step 3 until you have typed all the data for one record, and then press Enter twice to return to the first field . 5 Click Save . 6 Type a name for your table . 7 Click OK . 8 Click the Close box for the table . 5 2 1 3 8 6 7 When you create a field by typing, Access 2010 defines the table’s first field as a sequentially numbered field named ID, which serves as the table’s primary key field . It’s a good idea to leave the field in the table, but you should rename it to some-thing more descriptive so you can identify it if you use it as a foreign key . If the table’s name is Warehouses, for example, you could name the field WarehouseID . Tip Creating a New Table by Typing 35Add Fields from the Add & Delete
Ribbon Group
1 Open a table in Datasheet view . 2 In the table, click a cell in the Click to Add column . 3 Click the Fields contextual tab . 4 Click the type of field you want to add . 4 3 1 2Creating a New Table Using a Template
Rather than force you to create a new table from scratch when you start a new database, Access 2010 provides a number of templates you can use to create commonly used fields and tables quickly . When you display a table in Datasheet view, the Fields contextual tab on the Ribbon displays buttons you can use to add the five basic field types: text, number, currency, date & time, and yes/no . You can select from more built-in field types, or insert a set of multiple related fields by clicking the More Fields button and clicking the desired option from the menu that appears . 36 Creating a New Table Using a Template
Add More Types of Fields
1 Open a table in Datasheet view . 2 In the table, click a cell in the Click to Add column . 3 Click the Fields contextual tab . 4 Click More Fields . 5 Click the type of field you want to add . 4 3 2 1 5Add Multiple Related Fields at
One Time
1 Open a table in Datasheet view . 2 In the table, click a cell in the Click to Add column . 3 Click the Fields contextual tab . 4 Click More Fields . 5 In the Quick Start section of the list, click the set of fields you want to add . 4 3 2 1 5 Creating a New Table Using a Template 37Creating a New Table Using Application Parts
Access 2010 enables you to create databases and fields using templates . One new capability in Access 2010, Application Parts, enables you to create a table and several related forms, queries, and reports that display and summarize the data from that table . For example, if you want to maintain a list of contacts, you can use the Access 2010 Contacts Application Part to create a Contacts table, three forms, a query, and four reports to view your contacts, display a phone list, and create address labels based on that information .Create a Table, Forms, and Reports
Using Application Parts
1 Click the Create tab . 2 Click Application Parts . 3 Click the Application Part you want to use . 4 Select the There Is No Relationship option . 5 Click Create . 1 2 3 4 5 If the Application Part you used relates to an existing table in your database, such as if your Contacts table tracks your contacts with the individuals named in your Customers table, you can use the Create Relationship Wizard to define that relationship . Tip 38 Creating a New Table Using Application Parts
One aspect of sound table design is to have a field (or group of fields) with a value unique to each row in the table . The fields that have the unique value are called the primary key field(s) . Because each row’s primary key field (or fields) contains a unique value, Access 2010 can distinguish each row from all the other rows in the table . Why is that important? Consider what would happen if a store used your phone number to identify you when you called in to place an order . If you change phone numbers and the next person assigned your phone number calls in before you do, the store replaces your information with the new caller’s, potentially wiping out any record you had of doing business with the store .
Assign a Primary Key
1 Display the tables in your database . 2 Right-click the desired table . 3 Click Design View . 4 Click any cell in the row representing the field to be the primary key . 5 Make sure the Design tab is dis-played, and click the Primary Key button . 6 Click the Save button to save your work . 7 Click the Close box . The primary key shouldn’t contain meaning-ful information . The best value for a primary key field is a number that Access 2010 incre-ments for each row in a table . That type of field is called an AutoNumber field . Tip You can create a multiple-field primary key by Ctrl-clicking the row selectors of the fields you want to include in the primary key . Tip 3 1 2 6 5 4 7 Setting a Primary Key 39