eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473182 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 07/01/2008 |
| Series: | On Office series |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 512 |
| File size: | 8 MB |
About the Author
George Hepworth is a writer and instructional designer who has designed, researched, and written more than a dozen self-study courses. He is the founder of Grover Park Consulting, a database development company. He lives in Mountlake Terrace, Washington.
Read an Excerpt
Grover Park George on Access
By George Hepworth, Linda H. DeLonais
Holy Macro! Books
Copyright © 2003 George HepworthAll rights reserved.
ISBN: 978-1-932802-58-0
CHAPTER 1
What This Book Will Do for You
On more than one occasion, I've worked with programmers and database developers who dismissed Access as a "toy database". I don't hold it against them and I certainly can't argue against the advantages of Oracle or SQL Server for managing corporate Data Warehouses and Customer Relationship Management systems. The truth is many, many Fortune 100 corporations, small mom-and-pop businesses, schools and other nonprofit groups, and ordinary individuals use Access to store and report on a huge variety of data in an amazing number of environments. Not one of them considers Access a toy because it does the job they need it to do.
That's basically what this book is all about; through a combination of examples, explanations and do-it-yourself exercises, I will help you learn how to use Access to control and manage the information that matters to you.
Why You Need this Book
I assume most people who have bought this book or are reading it for the first time have a specific project in front of them. Therefore, I wrote the book with that in mind. It follows the steps I would follow in evaluating a new database project, designing, building, implementing, and evaluating the database for a client or myself. If you're looking for specific tips on creating queries, for example, you won't find a query chapter; instead you'll find discussions of queries in several places: creating interfaces for entering new data, providing for lookup of existing data, and creating reports. Use the Index at the back of this book to pursue specific topics.
A Professional Approach
In leading you through the development process, I will share with you my insights into the most productive and professional ways to accomplish those tasks. In other words, if you just want to hack out a quick database and you don't really care much about how you go about it, this book is not for you.
I tried to select examples for the book that would be familiar to you, maybe even useful if you complete them yourself. I also tried to select examples simple enough to complete in a reasonably short period of time. This doesn't mean, however, that what you will learn is simplistic in any way. On the contrary, keeping the subject matter simple allows us to concentrate on the underlying principles so that you can really understand both what you need to do to create an Access database and why you need to do it.
What's in this Book
This book concentrates on Access versions 2000, 2002/XP, and 2003; the examples in it were created in Access 2002. I have seen estimates that Access '97 still makes up somewhere between one-fourth and one-third of existing installations. I also suspect it probably accounts for an even larger percentage of working Access databases. Still, after some deliberation, I elected to focus on the newer versions of Access in this book. For one thing, this book is primarily intended for Access users just starting out, so I assume most of you will be using one of the newer versions (2000/2002/2003). For another, this book is mostly about using Access to create reliable, flexible, and scalable relational databases; the concepts underlying that goal generally do not depend on which version of Access you use. As you incorporate more advanced features into your databases, of course, the differences between versions become more important. Therefore, as we get further into the book, we'll make clear any differences between versions where appropriate.
Finally, I've been working with Access 2003 for some months. Although there are some interesting new features, I haven't found anything compelling enough to suggest you run out and upgrade if you haven't already. Smart Tags, for example, which have been available in other members of the MS Office family for some time, are an interesting addition to Access. Still, they tend to fall into the "so what" category in my opinion. On the other hand, there are some enhancements of interest to more advanced developers, but frankly, most of them are beyond the scope of this book anyway. So if you have Access '97, 2000, or 2002, you'll do just fine with this book.
Get Normal
Officially, this is a book on using Access. Much of the book is devoted to defining, building, and using the objects in an Access database. However, I feel quite strongly that it would be entirely unprofessional for me not to make a concerted effort to help you understand some fundamental concepts about relational databases to prepare you for that effort. The first and most important, concept is that the tables in your database must be normalized. You may not know what that means yet, but you will after you finish reading this book! I can't make this point strongly enough.
To be useful, your database has to be properly normalized.
No combination of clever interface design, sophisticated SQL queries or complex VBA code can ever compensate for an un-normalized database. If you learn nothing else from this book, learn what it means to normalize your databases and implement that knowledge in your databases.
How to Use this Book
If you wish, you can use this book like a self-study course, working your way through the sections and chapters, one at a time, building your skills as you go. If you prefer to skip around, looking for tips on dealing with issues such as naming conventions, table design, subqueries, VBA code or whatever, please feel free to do so. In any case, the purpose of this book is to help you accomplish one thing: building Access tools that work for you.
You are welcome, even encouraged, to email me your comments or suggestions about this book. I will do my best to answer each and every email. And if I really like your suggestion, I'll see that it gets "recycled" into the next edition.
ghepworth@gpcdata.com
CHAPTER 2Why Do You Want to Use Access Anyway?
You may be saying to yourself, "That's a very strange question to ask at the beginning of a book on Access." I assure you, though; it's a very important question to ask before you launch into a database project, especially if you have never built one. Actually, there are two parts to the question. Let's address them one at a time.
Do I Really Need a Database?
Building an Access database is not a trivial task; before you invest your time and effort in doing so, you should be quite sure you're solving the right problem with the right tool.
Understanding the Problem
Take your time and answer the following questions thoughtfully about the project you have in mind. We'll discuss the alternative answers in the next section.
True or False: My project requires calculating or updating a few values on a regular basis and I need to use formulas to do that. Neither the formulas I use nor the items on my list change much and, frankly, I'm not worried about keeping track of what happened in the past. Example: I have to calculate and publish a weekly report summarizing sales for my department's salespeople so their manager can decide who has earned their weekly performance bonus and who hasn't.
True or False: It's important to the success of my project that I can easily locate and report data from several weeks, months, or years in the past. Moreover,I need to filter and sort the data in several different ways, depending on who will see the reports. Example: I have to create and publish a monthly report showing Employee Counts for each of the preceding 24 months, by Company, by Operating Division, by Department, and by Team.
True or False: I need a database of critical company information so our employees can look things up quickly and easily. Example: Our phone staff continually misdirects incoming calls to Customer Service Reps in the wrong Department or Team. I need a database of call types and the departments that handle each type so CSR's can look them up and direct calls properly.
What Do My Answers Mean?
Let's review the alternative answers to these three questions and what they might tell you about your project.
Frequent, One-Of Reporting
True or False: My project is mostly about calculating or updating a few values on a regular basis and I need to use formulas to do that. Neither the formulas I use nor the items on my list change much and, frankly, I'm not worried about keeping track of what happened in the past.
If you said "True"
The most appropriate tool for this task might be a spreadsheet — or possibly even a table in a Word document — rather than a database. As you probably know, in a spreadsheet, you embed formulas — sometimes very complicated formulas — into the cells on the worksheet right next to the cells containing the values used in those formulas. You can quickly scroll down a column or across a row, changing values in cells as appropriate. When you enter or change the value in a cell or alter a formula, the calculated results display immediately. Moreover, an advanced Word user could use a table with formulas in the appropriate cells to accomplish a lot of the same functions. The Word document also has the advantage of being easier to format for print.
There's really no hard and fast rule about when you need to create a database versus a spreadsheet or other type of file, but one useful way to look at it is to balance the time and effort required to set it up and maintain it against the results it provides. If you don't need functions specific to a database, it doesn't make much sense to incur the extra effort and expense of creating one when a spreadsheet or Word table will do the job just fine.
If you said "False"
A database probably is the right tool for your job. While you can keep historical data, even very large amounts of historical data, by saving generations of a Word table or spreadsheet, keeping track of that ever expanding directory full of files quickly becomes complicated and, more importantly, risky.
Historical and Multi-Level Reporting
True or False: It's important to the success of my project that I can easily locate and report data from several weeks, months, or years in the past. Moreover, I need to filter and sort the data in several different ways, depending on who will see the reports.
If you said "True"
You're definitely going to be building a database. One key characteristic of all databases is the ability to keep large amounts of data over extended periods of time. The only real limit is the amount of storage space available. A second key characteristic of a database is the ability to filter and sort data into many different combinations. One source of data can feed an almost infinite variety of reports.
If you said "False"
You probably can use a spreadsheet or even a Word document to create a one time only snapshot of data. Again, the key is that the data is needed one time, in one format, and you don't need to re-use that same data over time in other formats.
Job Support Tools
True or False: I need a database of critical company information so our employees can look things up quickly and easily.
If you said "True" or "False"
I'm sorry; this one is sort of a trick question. I threw it in to make an important point about one of the occupational hazards of database development. On more than one occasion, I've been approached by a manager with a request like this one. And on more than one occasion, we've decided that they really didn't need a new database at all.
In this example, the business problem is that employees are misdirecting calls. That wastes everyone's time and causes frustration for customers and for employees who get the misdirected calls. However, the problem doesn't necessarily arise out of lack of access to the appropriate information. There are at least three other possible causes that should be evaluated and, if possible, eliminated before starting a database development project.
First, it may simply be a result of incomplete or inappropriate training provided to the call screeners. Perhaps those employees answering phones are simply directing calls to the first available Customer Service Representative. In fact, to them, getting calls to a CSR as quickly as possible makes sense in the event that they don't realize that it also makes a difference to whom they direct the calls.
Second, it may be a result of inappropriate or ineffective management. If managers have failed to communicate that the current behavior is not appropriate, or if they have failed to offer direction and provide rewards for appropriate behavior or impose consequences for inappropriate behavior, the employees answering phones have no incentive to change the way they do it.
Third, inappropriate or poorly thought-out workflows or limitations in tools often prevent people from doing their jobs the way they know they should. In this case, for example, it may be a limitation of the phone system that it automatically assigns all transferred calls to the next available CSR by default. Call screeners have no choice about who will get that call. They know there is a problem, but can't do anything about it.
What does all of this have to do with building a database? Well, to repeat a point I made a little earlier, creating a database is not a trivial task, even with Access. If you want to be a competent, professional database developer, the last thing you want to do is invest your time, effort and other resources solving the wrong problem!
At best, the database will go unused because it doesn't meet a real business need. At worst, the managers who paid you for that time and effort are going to ask you some very unpleasant questions when they don't see any return on that investment.
The Right Tool for the Job
Okay, then. At this point you're sure a database is the right tool for the job ahead of you and you're ready dive in and start building Access tables.
Stop, take a deep breath, step away from your PC, and read the next section first.
Why do you want to use Access?
If you're a new Access user, you probably bought this book for a reason not too different from one of the following:
* The person for whom you work has asked you to "do something about ____".
Fill in the blank with the appropriate crisis in your organization: misplaced phone messages and letters from customers, hassles getting timely attendance reports from classroom teachers, keeping track of scheduled vacation days for everybody in our department, and so forth.
* Someone who used to work for your organization built the Access database you all use every day, but that person left months ago. Now it doesn't work anymore, your predecessor didn't bother with documentation of any kind, and it's up to you to fix it — now!
* You're tired of using an Excel spreadsheet (or a series of spreadsheets) to track dozens (or hundreds) of pieces of equipment used by your department. It's confusing and time -consuming to keep them all updated. You've been told an Access database would be a better choice, but you've never used Access before.
* The organization you work for has a formal (and seemingly unending) process for evaluating all proposed IT projects. Your friends in IT have told you Access is just a toy database and what you really need is SQL Server with a web -based front end, but, frankly, you can't wait six months just to get your project on the Resource Allocation Committee's agenda for discussion. With Access, you can build yourself something that works right now, even if it isn't perfect.
* You just upgraded your old PC, including a copy of MS Office. Now, to keep peace in your family, you need to justify that expensive software by building a database to track your spouse's collection of ____________. Fill in the blank with the appropriate hobby: exotic coffee mugs, vacations photos from the south of France, bird sightings in all fifty states, race walking results for their Race Walking club, etc.
These projects share four common characteristics. First, according to the criteria we discussed in the preceding section, they all appear to call for a database, even if it's only by management fiat! In addition, you started them because you
• need to solve a specific, more or less well-defined problem,
• have little or no experience or training with relational databases in general and Microsoft Access in particular and
• have little or no time or money to invest in training or professional solutions.
You can take some comfort from the fact you are by no means the first person in this position, you won't be the last, and, above all, you are not alone. You'll get a lot of help from this book, and from some of the other resources described in it. As my mentor, Red Green, likes to say, "We're all in this together."
(Continues...)
Excerpted from Grover Park George on Access by George Hepworth, Linda H. DeLonais. Copyright © 2003 George Hepworth. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Table of Contents
| Foreword | a | |
| 1. | What This Book Will Do for You | 1 |
| Why You Need this Book | 1 | |
| A Professional Approach | 1 | |
| What's in this Book | 2 | |
| Get Normal | 2 | |
| How to Use this Book | 3 | |
| 2. | Why Do You Want to Use Access Anyway? | 1 |
| Do I Really Need a Database? | 1 | |
| Understanding the Problem | 1 | |
| What Do My Answers Mean? | 2 | |
| The Right Tool for the Job | 5 | |
| Why do you want to use Access? | 5 | |
| Who Will Use the Database | 7 | |
| How Much Data Will the Database Hold | 7 | |
| What Hardware and Software is Available | 8 | |
| Networked Environments | 9 | |
| Access It Is, Then! | 10 | |
| 3. | Data Modeling 101 | 1 |
| The Cardboard Box Metaphor | 1 | |
| It's All About the Data Model | 2 | |
| Okay, Cardboard Box Guy, Model This | 2 | |
| What Do I Want to Keep Track of--the Entities | 3 | |
| Tech Talk--Formal Entity Definition | 3 | |
| Hierarchies and Entity Groups | 4 | |
| Choosing a Model--Workflow and Business Rules | 7 | |
| Entities Have Attributes | 9 | |
| Enough Attributes | 10 | |
| Tech Talk--Entities, Attributes and Relationships | 10 | |
| Entity | 11 | |
| Attributes | 11 | |
| Domain | 12 | |
| Attribute/Data Value Pairs | 12 | |
| Relationships | 13 | |
| Relationship Set | 15 | |
| Relationship Types | 15 | |
| One-to-One Relationships | 15 | |
| One-to-Many Relationships | 16 | |
| Many-to-Many Relationships | 17 | |
| Key Attributes | 19 | |
| Further Study | 20 | |
| Creating an Informal Data Model | 20 | |
| Your Data Model | 20 | |
| My Data Model | 24 | |
| Entities, Definitions, and Attributes | 24 | |
| Managing Data--Delete vs. Inactive | 26 | |
| Status | 27 | |
| Who's Related to Whom | 28 | |
| Gray Areas, Trade-Offs, and Lessons Learned | 28 | |
| Lessons Learned | 29 | |
| Trade-Offs | 30 | |
| Summary | 30 | |
| 4. | Let's Get Physical | 1 |
| Naming Conventions | 1 | |
| It's Finally Time to Open Access | 1 | |
| Create a New Database | 4 | |
| Setting Options | 6 | |
| Create a New Table | 9 | |
| Your First Table | 13 | |
| Naming Conventions | 14 | |
| Data Type | 16 | |
| Primary and Foreign Keys | 19 | |
| Key Attributes and Natural Keys | 20 | |
| Primary Keys | 20 | |
| Summary--What are Primary Keys | 22 | |
| AutoNumbers for Primary Keys | 23 | |
| Primary Key Notation | 24 | |
| Indexes, With and Without Duplicates | 26 | |
| Ignore or Allow Nulls | 27 | |
| Foreign Keys | 28 | |
| Views | 29 | |
| Add a Related Table | 31 | |
| Can You Relate to This? | 42 | |
| Creating Relationships in the Relationship Window | 42 | |
| Taking Stock | 51 | |
| 5. | Try it Yourself--Create Tables | 1 |
| Data and Look Up Tables | 2 | |
| Additional Look Up Tables | 2 | |
| Additional Data Tables | 5 | |
| Person Table | 8 | |
| Many-to-Many Relationships and Junction Tables | 10 | |
| Household Head | 13 | |
| Relationships | 15 | |
| Wrapping Up The Table Design | 17 | |
| 6. | Normalize Your Data | 1 |
| Why Normalization Matters | 1 | |
| The Goals of Normalization | 2 | |
| Eliminating Redundant Data | 2 | |
| Selecting the Smallest Meaningful Values | 3 | |
| Storing Only Related Data | 3 | |
| The Rules of Normalization | 4 | |
| Cutting You Some Slack | 4 | |
| First Normal Form | 5 | |
| First Normal Form Defined | 10 | |
| Second Normal Form | 11 | |
| Third Normal Form | 14 | |
| Summary--The Normalization Process | 17 | |
| 7. | Try It Yourself--Normalization | 1 |
| Steps to Normalization | 4 | |
| Calculated Fields | 14 | |
| Taking Stock | 17 | |
| Creating the Relationships | 23 | |
| Address Table Relationships | 25 | |
| We're All Normal Here | 25 | |
| Backup, Backup, Backup | 26 | |
| 8. | Getting Data Into Your Database--Simple Forms | 1 |
| Forms, the Primary Data Input Tool | 1 | |
| Bound and Unbound Forms | 2 | |
| Main Forms and Sub Forms | 2 | |
| Unbound Forms | 2 | |
| Learning Strategy for Forms | 4 | |
| Controls | 11 | |
| Moving and Aligning Controls | 17 | |
| Create a Form from Scratch | 19 | |
| Other Form Properties | 36 | |
| Add a Command Button | 36 | |
| How the Button Works | 40 | |
| The Builder Button | 40 | |
| Shortcut to Creating New Forms | 45 | |
| Change the Record Source and Control Source | 46 | |
| Navigation on Forms | 47 | |
| Triggering Events on a Form | 48 | |
| From Simple to Intermediate | 48 | |
| Backup, Backup, Backup | 49 | |
| 9. | Getting Data Into Your Database--Intermediate Forms | 1 |
| Drag and Drop Fields | 1 | |
| Navigating with a Searching Combo Box | 3 | |
| Combo Boxes and Visual Basic for Applications Code | 7 | |
| Conditional Instructions in VBA | 13 | |
| What this Conditional Statement Does | 15 | |
| Why I Prefer Combo Boxes to Scroll Bars and Navigation Buttons | 18 | |
| VBA Behind the Combo Box--Further Study | 18 | |
| Creating SQL Statements | 19 | |
| Housekeeping Chores--Control Names | 27 | |
| Try it Yourself | 29 | |
| My Design for frmParent | 30 | |
| Command Buttons | 36 | |
| Further VBA Study | 40 | |
| Tab Order and Tab Stop | 40 | |
| Intermediate Form Complete | 45 | |
| Backup, Backup, Backup | 45 | |
| 10. | Getting Data Into Your Database--Complex Forms | 1 |
| Add Sample Data | 1 | |
| Main Form--Sub Form Strategy | 2 | |
| Unrelated Sub Forms | 2 | |
| Add a Subform | 2 | |
| Pre-defined Relationships | 5 | |
| M | 6 | |
| Subform Control versus Subform | 7 | |
| Bound Column, Column Count, and Column Width | 14 | |
| Assigning New Persons to a Household | 20 | |
| Handling Errors in a Combo Box Not In List Event | 26 | |
| Help with Visual Basic for Applications | 28 | |
| What the Custom Message Box Does | 30 | |
| Try It Out | 31 | |
| Adding New Records through the Form | 32 | |
| Timing Issues with Record Sets | 33 | |
| Modify the VBA for the Add Button | 33 | |
| Designating Heads of Households | 35 | |
| User Interaction Strategy | 39 | |
| Independent Data Entry Forms | 39 | |
| Interrelated Data Entry Forms | 40 | |
| Designing Forms to Support Workflow | 40 | |
| Generalizing the Lesson | 41 | |
| Where Were We Just Now? | 41 | |
| Move a Command Button from One Form to Another | 42 | |
| Remaining Issues | 48 | |
| Try it Yourself | 49 | |
| Additional Data Entry Forms | 49 | |
| My Suggested Subforms | 51 | |
| Phone Number Subform | 53 | |
| Forms Wrap Up | 57 | |
| Additional Enhancements and a Self Study Opportunity | 58 | |
| Backup, Backup, Backup | 58 | |
| 11. | Getting Data out of Your Database--Reports | 1 |
| Useful Reports and Forms | 1 | |
| Mailing Labels Report Wizard | 2 | |
| String Concatenation in Reports | 11 | |
| Data Display Versus Data Input | 12 | |
| Hey, What Was that "Trim" Thing You Skipped Over? | 12 | |
| Adding a Second Table to the Report's Record Source | 12 | |
| The Expression Builder | 18 | |
| Creating Expressions | 19 | |
| Click to Paste Expressions | 19 | |
| Functions in the Expression Builder | 21 | |
| Using the Expression Builder in our Labels | 22 | |
| Adding Text to an Expression | 22 | |
| Using The Expression Builder | 24 | |
| Field Names | 24 | |
| Sorting Records | 25 | |
| Page Set Up | 27 | |
| Wrap up the Label Report | 30 | |
| Birth dates by Household Report | 30 | |
| Create a Query | 30 | |
| Report Requirement--Query Contents | 31 | |
| Inner Joins | 37 | |
| Report Wizard--Birthday List | 42 | |
| Report Wizard, Field Layout on the Report | 46 | |
| Report Wizard, Report Style | 46 | |
| Headers and Footers | 48 | |
| Creating a Calculated Field on a Report | 58 | |
| Debugging Existing Report | 65 | |
| The Root of the Problem | 65 | |
| Fixing the Bug | 67 | |
| Report Filters | 70 | |
| Wrap Up Report | 71 | |
| Backup, Backup, Backup | 71 | |
| 12. | Getting Data out of Your Database--Display-only Forms | 1 |
| Display-only Form | 1 | |
| Creating an Event to Requery the Birthday List | 11 | |
| Other Filtered List Forms | 13 | |
| Filtering Records for a Report | 13 | |
| Design View | 14 | |
| Backup, Backup, Backup | 17 | |
| 13. | Create a User Interface | 1 |
| Hands Off the DB Objects, Please | 1 | |
| Ah, It All Makes Sense Now | 2 | |
| A User Interface for Our Contacts Database | 2 | |
| Keep it Simple, Stupid | 2 | |
| Navigation by Menus | 3 | |
| A Menu-Based Switchboard | 5 | |
| The Grover Park Menu Switchboard | 6 | |
| Try it Yourself | 18 | |
| Using the Menu to Close Forms | 19 | |
| Coding Efficiency | 22 | |
| Assign the Function to the On_Click Event | 22 | |
| Forms Consistency | 24 | |
| Maintenance Forms | 25 | |
| Control Form for Printing Reports | 31 | |
| Filtering List box for the Printed Report | 41 | |
| Let's Get Outta Here | 45 | |
| Consistency and Workflow | 47 | |
| Background and Foreground Colors | 47 | |
| Background Images and Icons | 48 | |
| Screen Resolution | 48 | |
| Form Height and Width | 49 | |
| Form Open Event Procedures | 50 | |
| Menu and Tool Bars | 50 | |
| A Custom Menu Bar | 52 | |
| Open the Toolbar Customization Dialog | 53 | |
| Create a New Toolbar | 54 | |
| Assign the Custom Menu Bar as the Default Menu | 59 | |
| Hide and Lock the Menu Bar | 60 | |
| Bypassing Startup | 61 | |
| An Application Ready to Test | 62 | |
| Identify Entities and Attributes | 62 | |
| Normalization | 62 | |
| Identify Relationships, Primary and Foreign Keys | 63 | |
| Get Data in, Creating Input Forms | 63 | |
| Get Data Out, Creating Reports and Display Forms | 63 | |
| Manage Records and Forms | 63 | |
| Control the Application | 64 | |
| Where Do You Go From Here | 64 | |
| Download Personal Contacts | 65 | |
| Appendix A | 1 | |
| Index | 1 |