Access 2007 Forms & Reports For Dummies®
Published by
Wiley Publishing, Inc.
111 River Street
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, the Wiley Publishing 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, 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. Microsoft is a trademark or registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
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 317-572-4002.
For technical support, please visit www.wiley.com/techsupport.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.
Library of Congress Control Number: 2006936765
ISBN: 978-0-470-04659-3
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
Brian Underdahl is the well-known, bestselling author of over 70 computer books on a broad range of subjects. He has appeared on a number of TV shows, including the Computer Chronicles, and several TechTV programs, and is the subject of an hour-long interview on the syndicated Computer Outlook radio program. One of his titles was recently recommended to viewers by Scott Gurvey, Bureau Chief, on the Nightly Business Report on PBS, and his books have won awards including an Award of Merit from the Northern California Technical Communications Competition and the Referenceware Excellence Award for best title in the Graphic Design and Multimedia category.
Brian has been involved in personal computers from their beginning and has a background in electrical engineering. His last “real” job was as a Senior Programmer/Analyst for a large company, where he worked hard to bring the power and freedom of PCs to the desktops.
Brian figures that it’s the author who should do the work so that readers can get their money’s worth. That’s why his books are different — he takes the time to explain what’s going on so that readers can understand the subjects easily.
Darlene Underdahl spent many years as a Quality Assurance expert. She is applying that “attention to detail” in the writing she does today. In addition to technical writing, she has written a memoir of her early years and is gathering notes for future projects. She works and lives with her husband, the bestselling author Brian Underdahl, in the mountains above Reno, Nevada.
Darlene: For Brian . . .
No book is the product of one person, even if one person has the title of author and gets to have his or her name on the cover. I’m very lucky to have a lot of people I can thank for all the hard work and effort they put into this book. They include Kyle Looper and Pat O’Brien.
I’d also like to thank the many people who provided me with the wonderful Access tools and add-ons so that I could give you an idea about how you can get even more from Access. Please do check out the Web sites I list for these great products — you owe it to yourself to see how much more productive you can be.
We can’t work without a functioning computer, so special thanks go out to Allison Wagda at ZoneLabs for providing ZoneAlarm Internet Security Suite. I sleep a lot better at night knowing that I don’t have to worry about viruses and spyware, and I hope all my readers realize the importance of this type of protection.
Finally, many thanks to Fred Holabird of Holabird Americana, www.holabirdamericana.com, for allowing me to use samples from the Holabird Americana databases in order to be able to show some real-world examples in this book. Fred is a good friend and I’ll always be grateful for his help!
We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Media Development
Project Editor: Pat O’Brien
(Previous Edition: Nicole Haims)
Acquisitions Editor: Kyle Looper
Copy Editors: Andy Hollandbeck, Mary Lagu
Technical Editor: Michael Alexander
Editorial Manager: Kevin Kirschner
Media Development Specialists: Angela Denny, Kate Jenkins, Steven Kudirka, Kit Malone, Travis Silvers
Media Development Coordinator: Laura Atkinson
Media Project Supervisor: Laura Moss
Media Development Manager: Laura VanWinkle
Media Development Associate Producer: Richard Graves
Editorial Assistant: Amanda Foxworth
Sr. Editorial Assistant: Cherie Case
Cartoons: Rich Tennant (www.the5thwave.com)
Composition Services
Project Coordinator: Jennifer Theriot
Layout and Graphics: Claudia Bell, Carl Byers, Lavonne Cook
Proofreaders: Susan Moritz, Techbooks
Indexer: Kevin Broccoli
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
Diane Graves Steele, Vice President and Publisher
Joyce Pepple, Acquisitions Director
Composition Services
Gerry Fahey, Vice President of Production Services
Debbie Stailey, Director of Composition Services
Title
Introduction
About This Book
Conventions Used in This Book
What You Don’t Have to Read
Icons Used in This Book
Foolish Assumptions
How This Book Is Organized
Part I : Accessing Both Ends: Getting Data In and Info Out
Chapter 1: Getting to Know Forms and Reports
Finding Forms Fascinating
Recognizing Why Reports Rock
Seeing Why Both Forms and Reports Have a Place
Understanding the Value of Queries
Queries, Forms, and Reports Basics You Need to Know
Access Add-Ons and Extra Cool Tools
Chapter 2: Getting Started with Queries
Understanding the Types of Queries
Creating Queries Using a Wizard
Creating Queries in Design View
Modifying Queries for Better Results
Chapter 3: Creating Simple Forms
Creating Forms with the Help of a Wizard
Building Forms the Hard Way: Using Design View
Modifying Forms You’ve Created
Chapter 4: Building Basic Reports
Getting to Know the Types of Access Reports
Creating Reports by Using a Wizard
Designing Reports from Scratch
Modifying Report Layouts
Adding Data from External Linked Tables
Part II : Creating Effective Queries
Chapter 5: Creating Multi-Table Queries
Understanding Multi-Table Queries
Adding Multiple Tables to Queries
Linking to External Databases in Your Queries
Understanding Relationships in Queries
Chapter 6: Modifying Data with Queries
Understanding What You Can Modify with a Query
Called to Action: Data-Modifying Queries
Creating New Tables with Queries
Updating Data by Using Queries
Deleting Data with Queries
Adding New Data with Queries
Chapter 7: Expanding Queries with SQL
Getting the Scoop on SQL
Understanding How SQL Can Help
Viewing the SQL You’ve Already Created and Didn’t Know About
Getting Your Feet Wet with Simple SQL Statements
Making Useful SQL Statements
A Nod to VBA Programmers
Chapter 8: Dealing with Quarrelsome Queries
Troubleshooting Queries That Don’t Quite Work
Troubleshooting Sassy SQL
Avoiding Improper Relationships
Junking Joins That Don’t Join
Steering Around Empty Query Results
Preventing Data Destruction
Part III : Building Really Useful Forms
Chapter 9: Building Better Forms by Using Controls
Getting to Know the Controls
Common Controls You Can Use
Adding Controls to Your Forms
Incorporating ActiveX Controls
Modifying the Characteristics of Controls
Chapter 10: Using Multiple Tables in Forms
Understanding Forms and Subforms
Adding Fields from Multiple Tables to Your Forms
Choosing the Best Subform View
Adding Multiple Subforms
Navigating Records in Multi-Table Forms
Chapter 11: Fixing Form Faults
Preventing Invalid Data with Input Masks
Limiting a User to Specific Values with Validation Rules
Preventing Users from Changing Records
Looking Out for Poorly Formatted Data
Part IV : Designing Great Reports
Chapter 12: Adding Fancy Touches to Reports
Adding Descriptive Titles to Your Reports
Getting Dynamic with Your Titles
Coming Up with Page Title Ideas
Adding Dates and Page Numbers
Making Records Easier to Read with Alternating Bands of Color
Making Records Stand Out
Chapter 13: Creating Reports That Summarize Data
Discovering How to Group Records
Adding Subtotals and Totals
Using Some Advanced Summarizing Methods
Chapter 14: When Reports Go Wrong . . .
Troubleshooting Reports
Avoiding Bad Report Layouts
Eliminating Unnecessary Labels for Tighter Reports
Part V : Way Cool Advanced Queries, Forms, and Reports
Chapter 15: Tricks for Finding Data with Queries
Finding Just the Data You Need
Creating Queries That Ask What to Find
Excluding Data You Don’t Want
Chapter 16: Tricks for Updating with Queries
Creating New Fields with a Query
Adding Default Values by Using a Query
Performing Calculations
Updating Records by Using Data from Another Table
Chapter 17: Killer Ways to Improve Forms
Prequalifying the Data Entered
Crafting Forms That Are Easier to Use
Chapter 18: Expert Methods for Sharing Reports
Sharing Data with Microsoft Excel
Sending Reports to Microsoft Word
Using OLE Objects in Reports
Chapter 19: Making Reports Look Better
Enhancing the Appearance of Important Information
Printing on Standard Labels
Keeping Related Data Together
Adding Your Logo to Reports
Chapter 20: Adding Efficiency with Great Tools
Using Cool Tools to Document Everything
Souping Up Your Database
Using Software to Share Stuff
Part VI : The Part of Tens
Chapter 21: Ten Great Access Sites Online
Access Database Tips
Access Monster
Easy Access Database Directory
Jamie’s Software
Lebans.com
Microsoft Office Online Access
MSDN — Microsoft Developer Network
Roger’s Access Library
The Access Web
Chapter 22: Ten Things to Know about Queries (That Also Help You with Forms and Reports)
Queries Act like Tables
Select Queries Are Always Safe
All Queries Are SQL Queries
Action Queries Can Harm Your Data
You Need Joins to Use Multiple Tables in Queries
Queries Select All Records Unless You Specify Criteria
Datasheet View Shows Results without Running the Query
Make-Table Queries Always Begin with an Empty Table
Queries Need a Record Source
Delete Queries Are Forever
: Further Reading
Microsoft Access 2007 can be an incredibly useful application, but it can also be very frustrating if you don’t know how to make it do what you want it to do. In a perfect world, you’d have a guru around, 24/7 — someone who knows Access 2007 inside and out and is willing to guide you along the way, showing you handy little tricks and useful techniques that help you get the results you need.
Well, I may not be there with you, but this book is the next best thing. I’ve gathered the really useful pieces of information that you need to create powerful queries, very easy-to-use forms, and reports that actually tell the story of what is going on inside your data. Along the way, I make sure to show you the special tricks and techniques that I use.
Access 2007 Forms & Reports For Dummies is a hands-on guide that uses real-world examples to show you just what you need to know about Access and why you need to know it. You won’t find a bunch of buzzwords and jargon. Rather, you do find the solid information you really need and can’t find elsewhere about creating queries, forms, and reports. Yes, I do give you good, solid information about queries in addition to forms and reports because queries are an essential element that will help you create better forms and reports.
Access 2007 Forms & Reports For Dummies is also a reference that you can use as you like. If you have a specific problem you need to solve right now, you can jump directly to the related topic and skip around as much as you want. But if you really want to make Access work for you, I suggest that you read through the entire book because you will discover many things you don’t already know.
Finally, Access 2007 Forms & Reports For Dummies is specifically for users of Access 2007. Although the basics of queries, forms, and reports haven’t changed much from the earlier versions of Access, there are enough important changes in Access 2007 so you’ll find this book very useful even if you’ve used previous versions of Access.
We’ve used a few conventions in this book to make it easier for you to spot special information. Here are those conventions:
New terms are identified by using italic.
Web sites addresses (URLs) are designated by using a monospace font.
Any command you enter at a command prompt is shown in bold and usually set on a separate line. Setoff text in italic represents a placeholder.
Command arrows, which are typeset as ⇒, are used in a list of menus and options. For example, Tools⇒Options means to choose the Tools menu and then choose the Options command.
Key combinations are shown with a plus sign, such as Ctrl+F2. This means you should hold down the Ctrl key while you press the F2 key.
All Access properties and fields are set apart in monospace font, as well, like this: Use the Input Mask property of the Data tab to create an input mask.
Wherever I instruct you to use a snippet of code, I set it apart like this:
INSERT INTO LIVEWINBID
SELECT [Auction 67].*
FROM [Auction 67];
I always have a hard time telling people that they don’t have to read certain parts of a book if they don’t care to. You can find some really useful information hidden away in things like the text next to Technical Stuff icons, but I understand if you feel that there isn’t room in your brain for one more bit of technical information. Maybe the best thing that I can recommend is that if you don’t want to read the whole book now, start by reading what looks the most interesting and then, after you’ve discovered how much really cool stuff I include, go back and have a look at what you missed the first time. You’ll be glad you did!
Access 2007 Forms & Reports For Dummies includes icons that point out special information. Here are the icons I use and what they mean:
Making assumptions is always a gamble because assumptions can quickly come back to haunt you. That said, in writing this book I made some assumptions about you. This book is for you if:
You have Access 2007 and want to know how to use it more effectively.
You don’t yet have Access 2007, but are wondering if getting Access 2007 can help you organize all of that data you’re currently trying to manipulate with Excel or some other spreadsheet.
You have a bunch of data that you want to turn into useful information.
You want to see how you can create more efficient Access reports and quit wasting so many trees.
You would like to know how to use data from an external database without having to get down on your knees to beg permission every time you need to run a report.
You have finally realized that you are a 5th Wave cartoon addict and simply can’t pass up the chance to see what is hidden in these pages.
Access Forms & Reports For Dummies has six parts. Each part is self- contained, but all the content is somewhat interconnected. That way you’ll see the most useful information without a lot of boring repetition.
This part shows you the basics of queries, forms, and reports. You see how these pieces fit together, and I make sure that you have the fundamentals down pat so that you have a good foundation for the rest of the topics.
Queries enable you to work with sets of data instead of simply dumping everything into the pot. This part shows you how to create effective queries that enable you to pick and choose what shows up in your forms and reports. You even see how you can step beyond simple queries by having a look underneath the fancy face that Access throws onto your queries.
Forms make data entry and editing into a much easier task. This part shows you how to create forms that really are useful, and it tells you how to create forms that tackle data from more than one table at a time — for even greater efficiency.
With Access reports you can turn data into useful information. This part shows you how to create great reports that look good and that provide a wealth of understandable information.
Ordinary techniques are for ordinary people. This part takes you well beyond the ordinary and shows you how to get so much more from your queries, forms, and reports. This part also shows you some great tools you can use to make Access into an even more powerful partner for your database needs.
This part tells you about some places on the Web where you can find even more information about Access. It concludes with some vital tips to remember as you work with Access.
In this part . . .
You need a good foundation if you’re going to build anything useful and long lasting. In this part, I make sure that you have a good foundation for the topics in the rest of the book. Here you discover the basics of queries, forms, and reports that you really need but that you may have missed up until now.