Building Microsoft Access Applications
This article is an excerpt from Building Microsoft Access Applications, from Microsoft Press (ISBN 0-7356-2039-3, copyright Microsoft Press 2005, all rights reserved). The author of the book is John Viescas.
John L. Viescas is the author of Microsoft Office Access 2003 Inside Out and the popular Running Microsoft Access books from Microsoft Press. He is president of Viescas Consulting, Inc., a respected provider of database application design and editorial consulting services. He has been recognized by Microsoft Product Support Services as a Most Valuable Professional (MVP) every year since 1993 for his contributions to the community of Access users.
No part of this chapter may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic;, electrostatic, mechanical, photocopying, recording, or otherwise—without; the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.
In this chapter:
Any organization that has members, holds meetings, sends out notices, and collects dues can use the Membership Tracking application. Examples include a book club, a homeowners' association, or a software user group. Within a company, a similar application might also be usable by any ongoing task force (perhaps without the dues component) to track members assigned to the task force and the meetings held by the task force. Additional types of organizations that might use this application include:
- Neighborhood association
- School club
- Parent-teacher association
- Political action group
- Cub Scouts, Brownies, Girl Scouts, or Boy Scouts
- City council task force
- Genealogy club
- Social club
- Language club
- Officers' wives clubs
And the list goes on and on.
The Membership Tracking application in this book is specifically modeled after the activities and subjects that might be tracked by a software user group. In fact, Teresa Hennig, the president of the Pacific Northwest Access Developers Group (PNWADG) based in the Seattle area, graciously allowed me to use the group name, discussed with me some of the tasks that they track in their own membership database, and provided me with sample meeting agendas. Although the sample you'll find on the companion CD isn't actually used by PNWADG, it's based on a real-world example.
Identifying Tasks and Data
Whenever you begin to design an application, you should start by making lists of the tasks that the application must support and the data needed by those tasks. You should interview the potential users and determine how they're performing the tasks without a database or find out what they expect the database to do for them. Here are the tasks implemented in the Membership Tracking application:
- Enter member information, including home address, work address, e-mail address, and membership status.
- Collect information about the committees that perform work outside the general meetings.
- Track which members belong to which committees.
- Log both general and committee meetings.
- Track which members attended which meetings.
- Print a badge for each member when they sign in to a meeting.
- Identify which members are or were officers of the group and when they served in each position.
- Create the agendas for both general and committee meetings.
- Send out notices to the appropriate members for upcoming meetings.
- Specify special announcements to be included in meeting notices.
- Track dues paid by members.
- Inform members when their membership is about to expire.
- Print a membership roster.
- List the current officers.
- Display or print current committees.
- Print a membership expiration list.
Note Although not specifically included in this sample application, you could easily add tables to store meeting minutes and track the group's budget and expenditures.
From the preceding list, you can begin to identify the subjects and actions that should be represented by tables in the application. These include the following:
- Committee Members
- Meeting Agendas
- Meeting Announcements
- Meeting Attendees
When you see a subject name that lists the simple name of two other subjects, such as Committee Members, that subject is probably going to be the many-to-many link between Members and Committees. Likewise, Meeting Attendees (Attendee is another name for Member) is also a many-to-many link.
You might think that you don't need a separate subject for Officers, but note one of the tasks listed previously that stated: "Identify which members are or were officers of the group and when they served in each position." If you need to know only which members are currently officers, you could do that with an attribute (column) in the Members subject (table). But the requirement to also keep the history of past officers suggests that Officers should be a separate subject. So, you can see that understanding both the tasks required and the data for those tasks is critical to designing the application correctly. You cannot do one without the other.
Understanding Business Rules
As you identify the tasks that need to be supported by your application, you'll also learn something about the business rules that restrict those tasks. For example, the first task in the previous section states that the Member information should include both home and work address. Do you have a business rule that states that information is always to be mailed to the business address if it is available? Or, should you also store some indication from the member about which is the preferred address? In the sample application, I included a DefaultAddress field (an integer code indicating whether home or work is the default) in the tblMembers table and made it required, thus enforcing the rule that the members should indicate a preference.
The best way to discover business rules is to ask questions. Some of the questions that I considered as I designed the application (and the answer I chose for each) are as follows:
- Can a member belong to more than one committee? Yes.
- Can a committee have more than one chair? Yes.
- Can a member hold more than one office at a time? No.
- Can more than one member hold the same office at the same time? No.
- Should a member name be saved in all capitals, all lowercase, or proper case? Proper case.
- Can a member have more than one member identifier? No.
- Are general meetings always held on the same day of every month? No. (Actually, PNWADG meets on the third Tuesday of every month.)
- Should all announcements be listed either before or after the agenda for a meeting? The user can choose the position of the announcement, before or after the agenda.
- Must all agenda topics have a speaker? No.
- Are any membership types complimentary? Yes, honorary members and invited speakers do not have to pay dues.
- Are the dues rates the same for all members? Yes. (This might not be true for a homeowners' association.)
- Will the dues rates change over time? Yes.
- Can a member renew for a period of more or less than a year? Yes.
- Do you need to keep a history of dues rates? Yes.
- Do members with complimentary memberships have the same rights and privileges as active members? No, but not enforced within the application. The application does print separate Speaker and Guest name badges when a member signs in.
- Can members who owe dues attend a meeting? Yes, but they are issued a Guest badge unless they bring their dues up to date.
- Can a member sign in as both a Speaker and a Guest at a meeting? No.
- Are dues payable at the same time every year? No, dues payments extend membership for 6 or 12 months from the date paid or the last expiration date. (PNWADG actually runs on an annual basis with all dues owing in January.)
Keep in mind that no question is too trivial, and sometimes you have to think "outside the box" to conjure up questions that your user or client needs to answer. Think like a good investigative reporter and find out not only what the user needs to do, but also when they need to do the task, where they need to be when they do the task, how they need to perform the task, and, most importantly, why they need to do it.
If you have identified all the tasks, the data required for all the tasks, and all the business rules, designing the tables you need to store and manage the data should be easy. You should never sit down and start creating tables without all the necessary background information. If some of the data exists in other files (such as a spreadsheet), create your properly normalized tables first, and then figure out what you need to do to import the existing data into your correct table structure.
From the list of subjects you determined earlier, you can see that the Membership Tracking application needs the following tables:
When you study the answers to the business rule questions, you can see that you probably need some additional supporting tables. These tables include:
tblDuesRates to track the history of rates and different rates for different renewal periods.
tlkpMembershipStatus to list the valid member status settings.
tlkpOfficerType to store the officer titles for the organization.
tlkpTitles to provide a list of common name titles, such as Mr., Mrs., Dr., and so forth.
tlkpSuffixes to provide a list of common name suffixes, such as Jr., Sr., III, and so on.
Tip As you explore the sample databases, you'll notice that I have prefixed object names to indicate their usage. For example, tables names beginning with "tbl" indicate main data tables, "tlkp" tables are tables containing lookup data, and tables with names beginning with the letter "z" are working tables that the user cannot edit. Likewise, queries beginning with "qry" are primary record sources, and queries beginning with "qlkp" are ones used as row sources for list box controls and combo box controls. Forms beginning with "fdlg" are ones that open in Dialog mode, "fpop" indicates pop-up forms, "frm" indicates main editing forms, and "fsub" indicates forms that are used as subforms. You might find that a naming convention like this helps you organize the objects in a large application.
Now, you're ready to begin defining the tables you need and the relationships between them. As you create the tables, you should also define as many of the business rules as possible using the validation rule features of Access. Figure 4-1 shows you the final design for the tables in the Membership Tracking application. You can find a detailed listing of the fields and indexes in Appendix B, "Sample Database Schemas."
Figure 4-1. The tables in the Membership Tracking application (Click picture to view larger image)
The table design for the Membership Tracking application is actually quite simple. In some of the other sample applications, you'll see that nine main tables won't even begin to describe the data for the application. In the remainder of this part of the book, you can explore specific tasks implemented in the Membership Tracking sample database (Membership.mdb) that you can use in any similar application that you build.