Access 2013 For Dummies E-Book

Laurie A. Ulrich

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 506

Veröffentlichungsjahr: 2013

Beschreibung

The easy guide to Microsoft Access returns with updates on the latest version!

Microsoft Access allows you to store, organize, view, analyze, and share data; the new Access 2013 release enables you to build even more powerful, custom database solutions that integrate with the web and enterprise data sources. Access 2013 For Dummies covers all the new features of the latest version of Accessand serves as an ideal reference, combining the latest Access features with the basics of building usable databases. You'll learn how to create an app from the Welcome screen, get support for your desktop databases, and much more.

• Includes coverage of all the new features of Access 2013, including the updated interface
• Shows you how to create and share reports
• Features special videos and materials created by the authors to help reinforce the lessons included in the book
• Helps you build data analysis and interface tools for your specific needs
• Offers plenty of techniques and tips for solving common problems

Access 2013 For Dummies provides you with access to the latest version of this database tool.

Leseprobe

Access® 2013 For Dummies®

Published byJohn Wiley & Sons, Inc.111 River StreetHoboken, NJ 07030-5774

www.wiley.com

Copyright © 2013 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published by John Wiley & Sons, Inc., Hoboken, New Jersey

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, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley, the Wiley logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc., is not associated with any product or vendor mentioned in this book.

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 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 [phone].

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

Wiley also publishes its books in a variety of electronic formats and by print-on-demand. Not all content that is available in standard print versions of this book may appear or be packaged in all book formats. If you have purchased a version of this book that did not include media that is referenced by or accompanies a standard print version, you may request this media by visiting http://booksupport.wiley.com. For more information about Wiley products, visit us www.wiley.com.

Library of Congress Control Number: 2013932118

ISBN 978-1-118-51638-6 (pbk); ISBN 978-1-118-56850-7 (ebk); ISBN 978-1-118-56864-4 (ebk); ISBN 978-1-118-56851-4 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

About the Authors

Laurie Ulrich Fuller has been writing about and teaching people to use Microsoft Office for more than 20 years. She’s been there through every new version of Access as Office has evolved to meet the needs of users from all walks of life — from individuals to huge corporations, from growing businesses to non-profit organizations.

In the meantime, Laurie has personally trained more than 20,000 people to make better, more creative use of their computers, has written and co-written 30+ nationally published books on computers and software — including several titles on Microsoft Office. In the last few years, she’s also created several online training courses, teaching online students to use Microsoft Office and Adobe Photoshop.

Laurie’s own firm, Limehat & Company, offers training and educational materials as well as graphic design, marketing, promotions, and web-development services. She invites you to contact her with your Office-related questions at [email protected] and to visit her website at www.limehat.com.

Ken Cook has built and managed a successful computer consulting business (now called Cook Software Solutions, LLC) since 1990. He began as a trainer — training numerous users (too many to count!) on a variety of software packages — specializing in Microsoft Office. Currently he “dabbles in training” (specializing in online synchronous training), but his main focus is creating expert Microsoft Office solutions and Microsoft Access database solutions for Fortune 500 and small business clients.

Ken is also a published author on Microsoft Excel, having contributed chapters on macros and VBA to Special Edition: Using Excel 2000 and Special Edition: Using Excel 2002 published by Que. Ken also contributed Microsoft Access chapters to the book How to Do Everything with Office XP published by Osborne and coauthored the previous edition of this book: Access 2010 For Dummies published by Wiley.

Prior to his career in computers, Ken was a Product Manager for Prince Manufacturing, Inc. He is a graduate of Syracuse University with a bachelor’s degree in Marketing. He can be contacted through his web site (www.kcookpcbiz.com) or by e-mail (mailto:[email protected]).

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments at http://dummies.custhelp.com. For other comments, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

Some of the people who helped bring this book to market include the following:

Acquisitions and Editorial

Sr. Project Editor: Mark Enochs

Executive Editor: Steve Hayes

Copy Editor: Melba Hopper

Technical Editor: Brian Underdahl

Editorial Manager: Leah Michael

Editorial Assistant: Annie Sullivan

Sr. Editorial Assistant: Cherie Case

Cover Photo: © pagadesign / iStockphoto.com

Composition Services

Project Coordinator: Sheree Montgomery

Layout and Graphics: Jennifer Creasey, Christin Swinford

Proofreader: Evelyn Wellborn

Indexer: BIM Indexing & Proofreading Services

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Kathy Nebenhaus, Vice President and Executive Publisher

Composition Services

Debbie Stailey, Director of Composition Services

Access 2013 For Dummies®

Visit www.dummies.com/cheatsheet/access2013 to view this book's cheat sheet.

Table of Contents

Introduction

About This Book

Conventions Used in This Book

What You Don’t Have to Read

Foolish Assumptions

How This Book Is Organized

Part I: Getting Started with Access 2013

Part II: Setting the Table

Part III: Data Management Mania

Part IV: Ask Your Data, and Ye Shall Receive Answers

Part V: Simple and Snazzy Reporting

Part VI: More Power to You

Part VII: The Part of Tens

Appendix: Getting Help

Icons Used in This Book

Where to Go from Here

Part I: Getting Started with Access 2013

Chapter 1: Access 2013 Basic Training

What Is Access Good For, Anyway?

Building big databases

Building apps

Creating databases with multiple tables

Databases with user forms

Databases that require special reporting

What’s New in Access 2013?

New features

Reach out with SharePoint

How Access Works and How You Work with It

Opening Access

Selecting a starting point

Now What?

Chapter 2: Navigating the Access Workspace

Diving Right In

Working with Onscreen Tools in Access

Clicking tabs

Using buttons

The File tab and Quick Access tools

Accessing panes, panels, and context-sensitive tools

Customizing the Access Workspace

Repositioning the Quick Access toolbar

Adding buttons to the Quick Access toolbar

Removing buttons from the Quick Access toolbar

Minimizing the Ribbon

Working with ScreenTips

Mousing Around

Navigating Access with the Alt Key

Chapter 3: Database Basics

Database Lingo

Data, no matter how you pronounce it

Fields of dreams (or data)

Records

Tables

The database

Field Types and Uses

Choosing Between Flat and Relational Databases

Isolationist tables

Tables that mix and mingle

Building a Database

Adding and Removing Tables

One more, please

Oops, I didn’t mean to do that

Part II: Setting the Table

Chapter 4: Table Tune Ups

The Primary Key to Success

The lowdown on primary keys

Creating a primary key

Making Tables Get Along

Rules of relationships

Relationship types

Building Table Relationships

The Relationships window

Table relationships

Indexing for Faster Queries

Create your own index

Adding and removing indexes

Chapter 5: Remodeling Your Data

Opening a Table for Editing

Inserting Records and Fields

Adding a record

Inserting a field

Deleting a field

Modifying Field Content

Name-Calling

Renaming fields

Renaming a table

Turn Uh-Oh! into Yee-Hah!

Chapter 6: Types, Masks, and Triggers

Access Table Settings

Field Data Formats

Text fields

Number and currency fields

Date/time fields

Yes/No fields

Gaining Control of Data Entry

You really need to put a mask on those fields

To require or not to require

Making your data toe the line with validation

Give Your Fingers a Mini Vacation by Default

Part III: Data Management Mania

Chapter 7: A Form for All Reasons

Generating Forms

Keeping it simple: Form tools

Granting most wishes: the Form Wizard

Customizing Form Parts

Taking the Layout view

The theme’s the thing

Managing form controls

Chapter 8: Importing and Exporting Data

Retrieving Data from Other Sources

Translating file formats

Importing and linking

Hit the Road, Data

Export formats

Exporting table or query data

Chapter 9: Automatic Data Editing

Please Read This First!

Creating Consistent Corrections

Using Queries to Automate the Editing Process

Looking for duplicate records

Running the Find Duplicates Query Wizard

Chapter 10: Access and the Web

How Access Works with the Web

Understanding Office 365

Meet the Custom Web App

Click! Using Hyperlinks in Your Access Database

Adding a Hyperlink field to your table

Typing your hyperlinks

Fine-tuning your hyperlinks

Testing links

Embedding Web Content into Your Access Forms

Adding hyperlinks to your form

Publishing Your Data to the Web

Publishing your Access tables

Part IV: Ask Your Data, and Ye Shall Receive Answers

Chapter 11: Finding, Filtering, and Sorting Your Data — Fast

Using the Find Command

Finding anything fast

Shifting Find into high gear

Sorting from A to Z or Z to A

Sorting by a single field

Sorting on more than one field

Fast and Furious Filtering

Filtering by a field’s content

Filter by selection

Filter by Form

Unfiltering in a form

Filter by excluding selection

Chapter 12: I Was Just Asking . . . for Answers

Simple (Yet Potent) Filter and Sort Tools

Filter things first

Fact-finding with fun, fast filtering

Here’s the “advanced” part

Select Queries

Solid relationships are the key to getting it all (from your tables)

Running the Query Wizard

Getting Your Feet Wet with Ad Hoc Queries

Adding the finishing touches

Saving the query

Running your query

Chapter 13: I Want These AND Those OR Them

Working with AND and/or OR

Data from here to there

Using multiple levels of AND

Establishing criteria with OR

Combining AND with OR and OR with AND

Chapter 14: Number Crunching with the Total Row

Kissing That Calculator Goodbye via the Total Row

Adding the Total Row to Your Queries

Giving the Total Row a Workout

Putting it together with Group By

Well, that certainly sums it up

Counting, the easy way

Narrowing the results with Where

Creating Your Own Top-Ten List

Choosing the Right Field for the Summary Instruction

Chapter 15: Express Yourself with Formulas

A Simple Calculation

Complex Calculations

Calculate ’til you drop!

Using one calculation in another

Using parameter queries to ask for help

“Adding” words with text formulas

Hooray for the Expression Builder

Chapter 16: Take Charge with Action Queries

Easy Update

Add Records in a Flash

Quick Cleanup

Part V: Simple and Snazzy Reporting

Chapter 17: Fast and Furious Automatic Reporting

Quick and Not-So-Dirty Automatic Reporting

Creating a quick, one-table report

Starting the Report Wizard

Previewing Your Report

Zooming in and out and all around

Pop goes the menu

Beauty Is Only Skin (Report) Deep

The Print Options tab

The Page tab

The Columns tab

Chapter 18: Professionally Designed Reports Made Easy

Report Repairs

Report Organization

Structural devices

Page breaks

Formatting Stuff

Adding color

Relocation, relocation, relocation

One size does not fit all

Spaced-out controls

Borderline beauty

Tweaking your text

Sneaking a Peek

Getting a Themes Makeover

Adding More Design Elements

Drawing lines

Pretty as a picture

Chapter 19: Headers and Footers and Groups, Oh My!

A Place for Everything and Everything in Its Place

Layout basics

Sections

Grouping your records

So you want more?

Customizing Properties

Controlling report and page headings

Adjusting individual sections

Itemized adjustments

Chapter 20: Magical Mass Mailings

Massive Mailings with the Label Wizard

Part VI: More Power to You

Chapter 21: Analyze This!

From Flat File to Relational Tables with Table Analyzer

Record Database Object Information with the Database Documenter

Improve Database Performance without Steroids

Chapter 22: Steer Users in the Right Direction with Navigation Forms

The Comings and Goings of a Navigation Form

Am I in the Right Place? Testing Navigation Forms

Maintaining the Navigation Form

Edit a Navigation form item

Delete a Navigation Form tab item

Move a Navigation form item

Displaying the Navigation Form at Startup

Part VII: The Part of Tens

Chapter 23: Ten Common Problems

That’s Just Not Normal

You Type 73.725, but It Changes to 74

The Words They Are A-Changing

Was There and Now It’s Gone

Undo

Search for the missing record

Backup recovery

You Run a Query, but the Results Aren’t What You Expect

The Dreaded Parameter Dialog Box

The Slowest Database in Town

Your Database File Is as Big as a Whale

You Get a Mess When Importing Your Spreadsheet

We’re Sorry; Your Database File Is Corrupt

Chapter 24: Ten Uncommon Tips

Document Everything as Though One Day You’ll be Questioned by the FBI

Keep Your Fields as Small as Possible

Use Number Fields for Real Numbers

Validate Your Data

Use Understandable Names to Keep Things Simple

Delete with Great Caution

Backup, Backup, Backup

Think, Think, and Think Again

Get Organized and Stay Organized

There’s No Shame in Asking for Help

Appendix: Getting Help

Installed Help files

Asking the right questions

Office.com

Search engines and other sites

Voice calls

TDD/TT calls

Cheat Sheet

Introduction

Welcome! Thank you for picking up this book. We assume you’ve done so because you’re hoping it will explain how to use Microsoft Access 2013. And of course, as the authors, we believe this was a wise decision. We’re quite certain that this is the best book for you to use to understand Access 2013 — but not just because we wrote it. Rather, we base this belief on the fact that both of us have been teaching and using Access for a very long time, and we know how to share what we know with our students. That’s right, you’re now one of our students — at least that’s how we feel about you as our reader. Doesn’t that make you feel all warm and fuzzy? We hope so.

Of course, being a normal human being, you probably have work to do, and you’re probably juggling a whole lot of information. This means you need to use Access. You need it to organize your data. You need it to store all the information that’s currently spilling out of notebooks, file drawers, your pockets, your glove compartment, your smart phone, your brain, everywhere. You need it so you can produce reports that make you look like the genius you are. You need it so you can create cool forms that will help your staff enter all the data you’ve got stacked on their desks — and in a way that lets you know the data was entered properly so that it’s accurate and useful. You need Access so you can find little bits of data out of the huge pool of information you need to store. So that’s it. You just need it.

About This Book

With all the power that Access has (and that it therefore gives you), there comes a small price: complexity. Access isn’t one of those applications you can just sit down and use “right out of the box.” It’s not scarily difficult or anything, but there’s a lot going on — and you need some guidance, some help, and some direction to really use it and make it bend to your will. And that’s where this book — a “reference for the rest of us” — comes in.

So you’ve picked up this book. Hang on to it. Clutch it to your chest and run gleefully from the store, or click the Add to Shopping Cart button and sit back with an expression of satisfaction and accomplishment on your face, because you’ve done a smart thing. When you get home, or when the book arrives in person (or by download to your hand-held device), start reading — whether you begin with Chapter 1 or whether you dive in and start with a particular feature or area of interest that’s been giving you fits. Just read, and then go put Access to work for you.

Conventions Used in This Book

As you work with Access 2013, you’re going to need to tell it to do things. You’ll also find that, at times, Access has questions for you, usually in response to you asking it to do something. This book will show you how to talk to Access, and how Access will talk to you. To show the difference between the two sides of that conversation, we format the commands as follows:

This is something you type into the computer.

This is how the computer responds to your command.

Because Access is a Windows program, you don’t just type, type, type — you also mouse around quite a bit. Here are the mouse movements necessary to make Access (and any other Windows program) work:

Click: Position the tip of the mouse pointer (the end of the arrow) on the menu item, button, check box, or whatever else you happen to be aiming at, and then quickly press and release the left mouse button.

Double-click: Position the mouse pointer as though you’re going to click, but fool it at the last minute by clicking twice in rapid succession.

Click and drag (highlight): Put the tip of the mouse pointer at the place you want to start highlighting, and then press and hold the left mouse button. While holding down the mouse button, drag the pointer across whatever you want to highlight. When you reach the end of what you’re highlighting, release the mouse button.

Right-click: Right-clicking works just like clicking, except you’re exercising the right instead of the left mouse button.

What You Don’t Have to Read

Now that we’ve told you that you should read the book, we’re telling you that you don’t have to read all of it. Confused? That’s understandable, but don’t be. This section of the Introduction exists to put your mind at ease so that you won’t worry about having to digest every syllable of this book in order to make sense of Access. And more than just being a required section of the Introduction, the heading is true. You don’t have to read the whole book.

You should read the chapters that pertain to things you don’t know, but you can skip the stuff you do know or that you’re fairly sure you don’t need to know. If the situation changes and you eventually do need to know something, you can go back and read that part later. See? Easy.

If you only use Access at work, and you’re using an Access database that some über-geek in your IT department created, chances are you can’t tinker with it. Therefore, if you only need to know about using an existing Access database (or unless you have designs on that IT geek’s job), you can skip the chapters on designing databases.

Of course, it’s pretty useful to know what’s happening “behind the scenes,” but you don’t have to read those chapters if you don’t want to. We think you’ll find them interesting, and they’ll help you understand why certain things work the way they do in the database you use, but it’s entirely up to you.

Foolish Assumptions

You need to know only a few things about your computer and Windows to get the most out of Access 2013 For Dummies. In the following pages, we presume that you . . .

Know the basics of Windows 7 and Windows 8 — how to open programs, save your files, create folders, find your files once you’ve saved them, print, and do basic stuff like that.

Have some goals that Access will help you reach. You

• want to build your own databases

and/or

• want to work with databases that other people have created.

Want to use and create queries, reports, and an occasional form.

Have Windows 7 or 8.

If your computer uses Windows 98, 2000, or Vista, you can’t run Access 2013.

How This Book Is Organized

So, do you feel ready to dive in? Energized and excited to learn Access? Great. Check out this breakdown of the parts in this book so you know what’s coming and can figure out if there’s a section you need to look at first. Each part covers a general aspect of Access, and then each part’s individual chapters dig into the details.

Part I: Getting Started with Access 2013

In this first part of the book, you’ll find out what Access is, what it isn’t, how it works, and how you open it up and start using it. You’ll find out how to navigate and master the Access workspace — and people who’ve used previous versions of Access find out about all the new features and tools that are part of Access 2013.

Part I also takes you through the process of planning your database — deciding what to store, how to structure your database, and how to use some of Access 2013’s very helpful tools for starting a database with templates — cookie-cutters, to use a fun and accurate metaphor — for a variety of common database designs. Be prepared to pick up some helpful jargon, as you discover a bit about a few specialized terms that you really need to know.

Part II: Setting the Table

Part II takes you a bit deeper, starting out with a chapter on setting up more than one table to store related data — and moving on with chapters on setting up relationships between those tables, customizing the way data is stored in your tables, and ways to control how data is entered into the tables in your database. You’ll also find out about tools that create new data in your tables — based on existing data — automatically.

Part III: Data Management Mania

Here you find out all about forms — the customized interfaces you create to make it easier to enter, edit, and look at your database. You’ll also discover cool ways to share your Access data with other programs and how to bring content from Word documents and Excel worksheets into Access to save time, reduce the likelihood of data-entry errors, and build consistency within all the work you do in Microsoft Office.

Speaking of saving time and building consistency, you’ll also learn about the Application Parts feature, through which you can recycle parts of your existing databases to build new ones. You’ll also find out about using Access tables on the web, how Office 365 makes use of “the cloud,” and how to publish your database to the Internet. Look out, world!

Part IV: Ask Your Data, and Ye Shall Receive Answers

In Part IV, you discover how to ask questions such as “How many customers do we have in Springfield?” and “How long has that weird guy in Accounting worked here?” Of course, you already know how to form and speak sentences that go up at the end (so people know you’re asking a question), but when you ask a question in Access, the pitch of your voice rarely makes any difference. You’ll need, therefore, to know how to sort, filter, and query your data to get at the information you’re storing in your Access database. You’ll also want to know more about Action Queries — and these, too, can be found in Part IV.

Part V: Simple and Snazzy Reporting

Reports are compilations of data from one or more tables in your database. That statement might sound a bit scary, because “compilations” has four syllables and you might not be sure what a table is yet. Have no fear, however, because Access provides some cool automatic tools that let you pick and choose what you want in your report, and then it goes and makes the report for you. How neat is that?

And if automatic reports aren’t good enough for you — if your job relies upon reports not only being informative but also attractive and attention-grabbing, Part V will be like opening a birthday present. Well, not really, but you’ll find out about how to set up reports that organize your information logically, how to make sure your reports include page numbers, headings and other information to help people make quick use of the report, and how to include graphics to produce a professional-looking publication, whether viewed onscreen or in print.

Part VI: More Power to You

Part VI gives more power in the form of the Access Analyzer, a tool that tunes up your database for better performance. It also gives you more power by showing you how to create a user interface that controls what people see, which tables they can edit, and how they work with your database overall.

Part VII: The Part of Tens

The format of these chapters is designed to give you a lot of information in a simple, digestible fashion so you can absorb it without realizing you’re actually learning something. Sneaky, huh?

Appendix: Getting Help

This isn’t really a whole part, but it’s darn useful. Remember how your mom told you the only foolish question is the one you don’t ask? In this appendix, you find out where to go to ask — namely, the online and built-in help resources that Access offers.

Note: We went to the trouble of typing up a ton of records in a few sample databases that are designed to show you the tricks of the Access trade. You can find all the samples at www.dummies.com/go/access2013fd.

Icons Used in This Book

When something in this book is particularly valuable, we go out of our way to make sure that it stands out. We use these cool icons to mark text that (for one reason or another) really needs your attention. Here’s a quick preview of the ones waiting for you in this book and what they mean.

Tips are incredibly helpful words of wisdom that promise to save you time, energy, and the embarrassment of being caught swearing out loud while you think you’re alone. Whenever you see a tip, take a second to check it out.

Some things are too important to forget, so the Remember icon points them out. These items are critical steps in a process — points that you don’t want to miss.

Sometimes we give in to the techno-geek lurking inside us and slip some technical babble into the book. The Technical Stuff icon protects you from obscure details by making them easy to avoid. On the other hand, you may find them interesting. (Your inner techno-geek will rejoice.)

The Warning icon says it all: Skipping this information may be hazardous to your data’s health. Pay attention to these icons and follow their instructions to keep your databases happy and intact.

Where to Go from Here

Now nothing’s left to hold you back from the thrills, chills, and power of Access. Hold on tight to your copy of Access 2013 For Dummies and leap into Access. Not sure where to start? See if you spot yourself in these options:

If you’re brand new to the program and don’t know which way to turn, start with the general overview in Chapter 1.

If you’re about to design a database, we salute you — and recommend flipping through Chapter 4 for some helpful design and development tips.

Looking for something specific? Try the Table of Contents or the index.

Occasionally, there are updates to technology books. If this book has technical updates, they will be posted at:

www.dummies.com/go/access2013fd

Now, go ye forth and build a database!

Part I

Visit www.dummies.com for great Dummies content online.

In this part . . .

Discover what Access is and does and what’s new in Access 2013.

Learn about the objects that make up an effective database, and get started building your first table.

Master database lingo so you can speak the language and understand the terminology.

Visit www.dummies.com for great Dummies content online.

Chapter 1

Access 2013 Basic Training

In This Chapter

Deciding when to use Access

Discovering what’s new in Access 2013

Unlocking the basics of working with Access

Figuring out how to get started

Access 2013, the most recent version of the Microsoft Office database application, continues to be a very powerful program. You probably already know that, and perhaps that power — or your perceptions of all that Access can do — is what made you reach for this book. We congratulate you on your wise choice!

For all of its power, Access is also very — pardon the expression — accessible. It’s pretty easy to use at the edges, where a new user will be; you don’t have to venture all the way in to its core to get quite a lot out of the software. In fact, with just the basic functionality that you’ll discover in this book, you’ll be able to put Access through many of its most important paces, yet you’ll be working with wizards and other onscreen tools that keep you at a comfortable arm’s distance from the software’s inner workings, the things that programmers and serious developers play with. There. Don’t you feel better now?

You don’t have to use every feature and tool and push the edges of the Access envelope. In fact, you can use very little of everything Access has to offer and still create quite a significant solution to your needs for storing and accessing data — all because Access can really “do it all” — enabling you to set up a database quickly, build records into that database, and then use that data in several useful ways. Later on, who knows? You may become an Access guru.

In this chapter, you’ll discover what Access does best (and when you might want to use another tool instead), and you’ll get a look at what’s new and improved in Access 2013 (compared to Access 2010). You’ll see how it does what it does, and hopefully you’ll begin to understand and absorb some basic terminology.

Now, don’t panic; nobody’s expecting you to memorize tons of complex vocabulary or anything scary like that. The goal here (and in the next two chapters) with regard to terms is to introduce you to some basic words and general concepts intended to help you make better use of Access — as well as better understand later chapters in this book, if you choose to follow us all the way to its stunning, life-altering conclusion.

What Is Access Good For, Anyway?

What is Access good for? That’s a good question. Well, the list of what you can do with it is a lot longer than the list of what you can’t do with it — of course, especially if you leave things like “wash your car” and “put away the dishes” off the “can’t do” list. When it comes to data organization, storage, and retrieval, Access is at the head of the class.

Building big databases

Okay, what do I mean by big database? Any database with a lot of records — and by a lot, I mean hundreds. At least. And certainly if you have thousands of records, you need a tool like Access to manage them. Although you can use Microsoft Excel to store lists of records, it limits how many you can store (no more than the number of rows in a single worksheet). In addition, you can’t use Excel to set up anything beyond a simple list that can be sorted and filtered. So anything with a lot of records and complex data is best done in Access.

Some reasons why Access handles big databases well:

Typically, a big database has big data-entry needs. Access offers not only forms but also features that can create a quick form through which someone can enter all those records. This can make data entry easier and faster and can reduce the margin of error significantly. (Check out Chapter 7 for more about building forms.)

When you have lots and lots of records, you also have lots of opportunities for errors to creep — duplicate records, records with misspellings, records with missing information — and that’s just for openers. So you need an application such as Access to ferret out those errors and fix them. (Chapter 9 lays out how you can use Access to find and replace errors and search for duplicate entries.)

Big databases mean big needs for accurate, insightful reporting. Access has powerful reporting tools you can use to create printed and onscreen reports — and those can include as few or as many pieces of your data as you need, drawn from more than one table if need be. You can tailor your reports to your audience, from what’s shown on the reports pages to the colors and fonts used.

Big databases are hard to wade through when you want to find something. Access provides several tools for sorting, searching, and creating your own specialized tools (known as queries) for finding the elusive single record or group of records you need.

Access saves time by giving you new uses for existing tools you may have used to import data from other sources — such as Excel worksheets (if you started in Excel and maxed out its usefulness as a data-storage device) and Word tables. This saves you from reentering all your data and allows you to keep multiple data sources consistent.

Building apps

There are several ways to build apps — a term that’s come to mean an application that runs on a smartphone or other hand-held device — but that also applies to SharePoint, with Access 2013.

You can build an app using the Access 2013 Web App template or build a custom web app, starting from scratch. You can also download an app from the Office Store and then customize it. You can also build a standard database, just like you always have in Access, and publish that via the web.

Now, that said, this is not a book about apps or building them. The goal of this book is to show you how to use Access to build databases for use on a computer (a desktop or laptop/notebook). If you need to create a database app for use on a smartphone or tablet, you can check out any of the following publications or explore instructions available online by Googling How do I create a database app with Access 2013?.

You can also take a look at Dummies.com or check out iOS 6 Application Development For Dummies.

Creating databases with multiple tables

Whether your database holds 100 records or 100,000 records (or more), if you need to keep separate tables and relate them for maximum use of the information, you need a relational database — and that’s Access. How do you know whether your data needs to be in separate tables? Think about your data — is it very compartmentalized? Does it go off on tangents? Consider the following example and apply the concepts to your data and see if you need multiple tables for your database.

The Big Organization database

A large company has data on their customers and their orders, the products the company sells, its suppliers, and its employees. For a complex database like this one, you need multiple tables, as follows:

One table houses the customer data — names, addresses, phone numbers, and e-mail addresses.

A second table contains the customers’ orders, including the name of the customer who placed the order, the salesperson who handled the sale, shipping information, and the date of the order.

A third table contains information on the products the company sells, including product numbers, supplier names, prices, and the number of items in stock.

A fourth table contains supplier data — about the companies from which the main organization obtains its inventory of products to resell to customers. The table contains the company names, their contact person, and the address, e-mail, and phone number information to reach them.

A fifth table contains employee data — from the date they were hired to their contact information to their job title — and also contains notes about them, sort of a summary of their resumes for reference.

Other tables exist, too — to keep a list of shipping companies and their contact information (for shipping customer orders), an expense table (for the expenses incurred in running the business), and other tables that are used with the main four tables. The need for and ways to use the main tables and these additional tables are covered later in this book, as you find out how to set up tools for data entry, look up records, and create reports that provide varying levels of detail on all the data you’ve stored.

Because you don’t have to fill in every field for each record — in any table in the database — if you don’t have a phone number or don’t know an e-mail address, for example, it’s okay to leave those fields blank until you’ve obtained that information.

Fail to plan? Plan to fail

If you think carefully about your database, how you use your data, and what you need to know about your employees, customers, volunteers, donors, products, or projects — whatever you’re storing information about — you can plan

How many tables you’ll need

Which data will go into which table

How you’ll use the tables together to get the reports you need

Of course, everyone forgets something, and plans change after a system has already been implemented. But don’t worry — Access isn’t so rigid that chaos will ensue if you begin building your tables and forget something (a field or two, an entire table). You can always add a field that you forgot (or that some bright spark just told you is needed) or add a new table after the fact. But planning ahead as thoroughly as possible is still essential.

As part of thorough planning, sketch your planned database on paper, drawing a kind of flow chart with boxes for each table and lists of fields that you’ll have in each one. Draw arrows to show how they might be related — it’s sort of like drawing a simple family tree — and you’re well on your way to a well-planned, useful database.

Here’s a handy procedure to follow if you’re new to the process of planning a database:

1. On paper or in a word-processing document, whichever is more comfortable, type the following:

• A tentative name for your database

• A list of the pieces of information you plan on getting from that database on a daily or regular basis

2. Now, based on that information, create a new list of the actual details you could store:

List every piece of information you can possibly think of about your customers, products, ideas, cases, books, works of art, students — whatever your database pertains to. Don’t be afraid to go overboard — you can always skip some of the items in the list if they don’t turn out to be things you really need to know (or can possibly find out) about each item in your database.

3.Take the list of fields — that’s what all those pieces of information are — and start breaking them up into logical groups.

How? Think about the fields and how they work together:

• For example, if the database keeps track of a library of books, perhaps the title, publication date, publisher, ISBN (International Standard Book Number, which is unique for each book), price, and page count can be stored in one group, whereas author information, reviews, and lists of other titles by the same author or books on the same topic can be stored in another group. These groups become individual tables, creating your relational database of books.

• Figure out what’s unique about each record. As stated in the previous point, you need a field that’s unique for each record. Although Access can create a unique value for you if no unique data exists for each record in your database, it’s often best to have such a field already in place, or to create such a field yourself. Customer numbers, student numbers, Social Security numbers, book ISBNs, catalog numbers, serial numbers — anything that isn’t the same for any two records will do.

With a big list of fields and some tentative groupings of those fields at the ready, and with an idea of which field is unique for each record, you can begin figuring out how to use the data.

4. Make a list of ways you might use the data, including

• Reports you’d like to create, including a list of which fields should be included for each report

• Other ways you can use the data — labels for mailings, product labels, catalogue data, price lists, contact lists, and so on

5. List all the places your data currently resides — on slips of paper in your pocket, on cards in a box, in another program (such as Excel), or maybe through a company that sells data for marketing purposes.

With this planning done, you’re ready to start building your database. The particulars of that process come later in this chapter and in subsequent chapters, so don’t jump in yet. Do pat yourself on the back, though, because if you’ve read this procedure and applied even some of it to your potential database, you’re way ahead of the game, and we’re confident you’ll make good use of all that Access has to offer.

Databases with user forms

When you’re planning your database, consider how the data will be entered:

If you’ll be doing the data entry yourself, perhaps you’re comfortable working in a spreadsheet-like environment (known in Access as Datasheet view), where the table is a big grid. You fill it in row by row, and each row is a record.

Figure 1-1 shows a table of customers in progress in Datasheet view. You decide: Is it easy to use, or can you picture yourself forgetting to move down a row and entering the wrong stuff in the wrong columns as you enter each record? As you can see, there are more fields than show in the window, so you’d be doing a lot of scrolling to the left and right to use this view.

You may want to use a form (shown in Figure 1-2) instead. A form is a specialized interface for data entry, editing, and for viewing your database one record at a time, if

• Someone else will be handling data entry

• Typing row after row of data into a big grid seems mind-numbing

Figure 1-1: Datasheet view can be an easy environment for data entry. Or not.

The mind-numbing effect (and inherent increased margin for error) is especially likely when you have lots of fields in a database, and the user, if working in Datasheet view, has to move horizontally through the fields. A form like the one in Figure 1-2 puts the fields in a more pleasing format, making it easier to enter data into the fields and to see all the fields at once (or only those you want data entered into).

You find out all about forms in Chapter 7. If your database is large enough that you require help doing the data entry, or if it’s going to grow over time, making an ongoing data-entry process likely, Access is the tool for you. The fact that it offers simple forms of data entry/editing is reason enough to make it your database application of choice.

Figure 1-2: Here’s a simple form for entering new records or reviewing existing ones.

Databases that require special reporting

Yet another reason to use Access is the ability it gives you to create customized reports quickly and easily. Some database programs, especially those designed for single-table databases (known as flat-file databases), have some canned reports built in, and that’s all you can do — just select a report from the list and run the same report that every other user of that software runs.

If you’re an Excel user, your reporting capabilities are far from easy or simple, and they’re not designed for use with large databases — they’re meant for spreadsheets and small, one-table lists. Furthermore, you have to dig much deeper into Excel’s tools to get at these reports. Access, on the other hand, is a database application, so reporting is a major, up-front feature.

An example? In Excel, to get a report that groups your data by one or more of the fields in your list, you have to sort the database first, using the field(s) to sort the data, and then you can create what’s known as a subtotal report. To create it, you use a dialog box that asks you about calculations you want to perform, where to place the results, and whether you’re basing a sort and/or a subtotal on more than one field. The resulting report is not designed for printing, and you have to tinker with your spreadsheet pagination (through a specialized view of the spreadsheet) to control how the report prints out.

In Access? Just fire up the Report Wizard, and you can sort your data, choose how to group it, decide which pieces of data to include in the report, and pick a visual layout and color scheme, all in one simple, streamlined process. Without you doing anything, the report is ready for printing. Access is built for reporting — after all, it is a database application — and reports are one of the most (if not the most) important ways you’ll use and share your data.

Because reports are such an important part of Access, you can not only create them with minimum fuss but also customize them to create powerful documentation of your most important data:

Build a quick, simple report that just spits out whatever is in your table in a tidy, easy-to-read format. (See Figure 1-3 for an example.)

Create a customized report that you design step-by-step with the help of the Report Wizard. (See Figure 1-4.) The report shown in the figure has the customers sorted by their company name. These options were easily put to work with just a few clicks.

Figure 1-3: Ah, simplicity. A quick report is just one click away.

Figure 1-4: The Report Wizard creates more elaborate (but simple) reports, like this one.

You can really roll up your sleeves and design a new report, or play with an existing one, adding all sorts of bells and whistles. Figure 1-5 shows this happening in Design view. Note that the report’s title (Customer Contact Report) is selected: It has a box around it and tiny handles on the corners and sides of the box, which means you can reformat the title, change the font, size, or color of the text, or even edit the words if a new title is needed.

So, you can create any kind of custom report in Access, using any or all of your database tables and any of the fields from those tables, and you can group fields and place them in any order you want:

With the Report Wizard, you can choose from several preset layouts for your report, and you can customize all of it row by row, column by column.

You can easily add and remove fields after creating the report, should you change your mind about what’s included in the report. If you want to place your personal stamp on every aspect of your report, you can use Design view to do the following:

Add titles, instructional or descriptive text boxes, and graphics.

Set up customized headers and footers to include any information you want to appear on all the report’s pages.

Figure 1-5: Design view might look a little intimidating, but to really customize things, you’ll need it — and you might even enjoy it!

If all this sounds exciting, or at least interesting, then you’re really on the right track with Access. The need to create custom reports is a major reason to use Access; you can find out about all these reporting options in Chapters 17 through 19. That’s right: This chapter plus three more — that’s four whole chapters — are devoted to reporting. It must be a big feature in Access!

What’s New in Access 2013?

For users of Access 2010, the upgrade to 2013 won’t seem like a big deal, other than the changes to the fonts used on the ribbons, the change to a white background for the ribbons, database tabs, and the All Access Objects panel on the left side of the workspace — but these are purely cosmetic changes.

There are some great new features, but you’re not gonna run smack into the learning curve that users of Access 2003 encountered upon upgrading to 2007 or 2010.

If you’re coming from 2003, the biggest changes are found in the interface. Gone are the familiar menus and toolbars of 2003 and prior versions, now replaced by a ribbon bar divided into tabs that take you to different versions of those old standbys. It’s a big change, and it takes some getting used to.

In this book, however, we’re going to assume you already got your feet wet with 2007 or 2010 and aren’t thrown by the interface anymore. We’re figuring you upgraded to 2007 or 2010 or have played with one or both of them enough to feel comfortable diving into 2013.

New features

So what’s new in Access 2013? In the order you’re most likely to encounter them, here goes:

The File tab and its dark red panel look very different; they replace the Office button and resulting menu in Access 2007, and the panel looks different than the one you may be used to from Access 2010.

Using the panel on the left (shown in Figure 1-6), you make your choices for opening new files, accessing recently used files, saving files, printing, and exiting the application (among other momentous decisions). It’s a lot like the File menu from Access 2003 in terms of what’s available; the File panel makes the commands and features that used to live on the File menu easily accessible. To get to it, just click the File tab.

To go back to the Home tab (or whichever tab you were on before clicking the File tab), use the big left-pointing arrow at the top of the File panel.

The biggest visible change, as we mentioned, is a new look and feel for the Access workspace. It’s a change seen throughout the whole Office 2013 suite, of course, but we’re concerned only with Access in this book. How is the workspace different? It’s white, mostly. There’s no gray background to your menus or ribbons, and there are no gray bars separating the groups of tables, forms, and what not on the All Access Objects panel. In comparison to previous versions of Access, it’s a very flat-looking environment.

Figure 1-6: The File tab and panel — hauntingly familiar to those who remember the File menu, and somewhat changed from Access 2010.

Reach out with SharePoint

What the heck is SharePoint? You may be asking that, along with lots of other people who’ve been seeing the product name and hearing how it provides the ability to see and use your Access data from anywhere — using desktop applications, a web browser, or even your phone. Well, it’s a Microsoft software product that does all that and more, helping you manage your documents and collaborate with coworkers via the company network. Simply click the Save and Publish command in the File tab’s panel (see Figure 1-7), and you’re on your way to publishing your database to SharePoint, which means you can access it from pretty much everywhere, including that beach in Maui. Of course, if you or your company don’t have a SharePoint server, you won’t be able to make use of this, and you don’t need to concern yourself with this section.

Figure 1-7: The Save As command offers choices for . . . you guessed it . . . saving your database.

As shown in Figure 1-7, the Save As options include regular old Save Database As, to save your existing database with a new name or in some format other than as an Access database; and Save Object As, to save a table, form, query, or report with a new name. You can also choose from several Advanced options to save the database as a package (to distribute your Access applications) or as an executable file (a single file that when run by the recipient, opens a database application), to back up the database, and to use the aforementioned SharePoint.

How Access Works and How You Work with It

When you look at all the applications in Microsoft Office — Word, Excel, PowerPoint, Outlook, and of course, Access — you’ll see some features that are consistent throughout the suite. There are big differences, too, and that’s where books like this one come in handy, helping you deal with what’s different and not terribly obvious to a new user.

Access has several features in common with the rest of the applications in the Microsoft Office suite. You’ll find the same buttons on several of the tabs, and the Quick Access toolbar (demonstrated in Chapter 2) appears in all the applications.

If you already know how to open, save, and print in, say, Word, you’re probably ready to do the same things in Access without any difficulty.

To make sure you’re totally Access-ready, here’s a look at the basic procedures that can give you a solid foundation on which to build.

Opening Access

Access opens in any one of several ways. So, like a restaurant with a very comprehensive menu, some people will love all the choices, and others will say, “I can’t decide! There are just too many options to choose from!”

Now, you’ll run into situations in which one of the ways is the glaringly best choice — hands down, and that one will be the way to go. But what if you’ve never heard of it? You’ll be trying to find my phone number (I’m unlisted — ha!) so you can give me a piece of your mind. So to acquaint you with all your choices (so you’ll be ready for any situation), here are all the ways you can open Access:

Windows 7 users can click the Start menu button (in the lower-left corner of the screen) and choose All Programs⇒Microsoft Office 2013⇒Microsoft Access 2013.

Windows 8 users can utilize any of several methods to start an application — click the lower-left corner of the screen to display the Start icon, press the Windows key on the keyboard, or if you have a touchscreen, tap the Start button. Once the Start screen appears, tap the Access application tile.

If you’ve recently used Access, you’ll see it in the list on the left side of the Start menu. Just choose Start⇒Microsoft Access 2013, and Access opens.

Double-click any existing Access database file on your Desktop or in a folder (as shown in Figure 1-8). Access opens automatically.

Good news: Access 2013 will open database files you created with previous versions of Access, and should support whatever features are employed within those database files. All your tables should open properly, and reports, forms, and queries should all work fine, too.

If some helpful person has added Access to the Quick Launch toolbar (on the Windows 7 taskbar), you can click the Access 2013 icon (it looks like an A on the cover of a book), and there you go. Access opens for you right then and there.

Figure 1-8: Double-click an Access database file, and Access opens right up.

Selecting a starting point

So Access is open, and (assuming you opened it from the Start menu or from the Quick Launch portion of the Taskbar) you’re staring at the Access interface. You may see features whose purposes elude you or that you don’t yet know how to use. Hey, don’t worry; that’s why you’re reading this book!

You can find out more about all the tabs and buttons, panels and menus, and all that fun stuff in Chapter 2. For now, just look at the ways Access offers you to get started with your database, be it an existing one that needs work or a new one you have all planned out and ready to go.

Opening an existing database

Well, this is the easy one. If a database already exists, you can open it by clicking the File tab (at the upper-left of the workspace) and choosing Open from the list of commands that appears. As shown in Figure 1-9, a panel opens, displaying the types of files you can open (just to the right of the long red File panel) and the databases you’ve most recently used. Click the word Recent in the list to the near left and then click the database in the Recent list, and it opens listing its current tables, queries, reports, and forms on the far-left side of the window.

When the database opens, you can open and view its various parts just by double-clicking them in that leftmost panel; whatever you open appears in the main, central part of the window. Figure 1-10 shows an example: a table, ready for editing.

Figure 1-9: Pick your recently used database from the Recent list on the right.

Figure 1-10: An existing table, ready for more records.

After you open a table, you can begin entering or editing records. You can read more about how that’s done in Chapter 6, which demonstrates the different ways to edit your data and tweak your tables’ setups. If you want to tinker with any existing queries, you can open these, too, just by clicking them in the list on the left side of the workspace. (For more information on queries, check out Chapters 11 and 12. You can do simple sorting and look for particular records with the skills you pick up in Chapters 9 and 11.)

Starting a new database from scratch

So you don’t have a database to open, eh? Well, don’t let that stop you. To start a new one, all you have to do is open Access, using any of the techniques listed earlier in this chapter (except the one that starts Access by opening an existing database file, which you don’t have yet).

A database file holds all your database components. Everything associated with the data is part of the database, including

All the tables that house your data

Queries that help you search and use the data

Reports that show what your data is and what it means

Forms that allow people to view, enter, and edit data

After Access is open, click the New command in the File tab (if that’s not already the active command). From the resulting display, you can click the Blank Desktop Database button (shown in Figure 1-11) to get started.

Next, give your database a name (see the dialog box that appears in Figure 1-12), and click the Create button.

The X in Figure 1-12’s caption represents a number — Access assigns consecutive numbers to the default names, counting from any previously created databases. Figure 1-12 shows a 1 added to the filename.

If this is your absolute first database in a fresh installation of Access, the filename offered in this panel will be Database1. Note that you don’t need to type a file extension here; Access will add the correct one for you.

Figure 1-11: Click the Blank Desktop Database button in the New group.

Figure 1-12: Name your database something that replaces the generic DatabaseX.accdb.