In the previous tutorial I discussed some guidelines for creating data entry screens that are easy to use. In this tutorial, we’ll put those guidelines into action by refining the membership database we introduced in part two of this series. I’ll step through the process in Microsoft Access 2000 and Microsoft Access 2010. If you’re using FileMaker Pro or Alpha 5 or some other database management system, read through the tutorial to get an understanding of the types of validation, lookups and other shortcuts at your disposal. I’ll give some brief directions to show you how to achieve the same results using FileMaker Pro.
[Please note: I have not yet updated this article from its original version. I plan to update the information and include info about more recent versions of Microsoft Access. In the meantime, you should still find these instructions useful for giving you a general approach to streamlining data entry, no matter which database management system you use.]
At this stage, we’ll still be working on the data structure (or data definition), rather than the data entry form itself. That’s because Access (as well as most other database systems) lets us lay the foundations for efficient form design while we’re working on the table definition process. By adjusting field properties and options, we can build data entry shortcuts and safeguards right into the structure of our database.
The membership table
We’re going to use the same table structure we used in part two of this series (see Table 1). If you need to create the table from scratch, do so using Design View and enter the structure shown in Table 1. If you already have a Members table ready to modify, open the table in Design View (click the Tables section in the Objects list, highlight the Members table, and click the Design button).
|Field name||Data type||Size|
Table 1. The Members table structure (* the asterisk denotes a key field). Note: This table structure was created for Australian use. Increase the size of the PostalCode field if you live in the US or another country which uses more than four characters for postal/zip codes, or if your membership table needs to accommodate members from more than one country. (In that case, of course, you’d also need to add a Country field to the table design.)
Describing our fields
There are several changes we need to make to our original table structure to streamline data entry design.
Our fieldnames are descriptive enough but they won’t make very readable captions on a data entry form. So highlight the MembershipID field and type Membership # in the Caption field property near the bottom of the screen (this time we include the space as it’s merely a label on a data entry form, not part of our table structure).
Move to the next field and add the caption First name. Continue adding the labels:
Street # and name
Postcode (you may want to label this Zip if you’re in the US)
List work number?
List home number?
Now scoot back up to the MembershipID field and set its Required property to Yes. This forces the data entry operator to type in a value for this field, ensuring that each member is assigned a number.
Forcing data into a mold
If you wish, you can add an input mask to the Work phone and Home phone fields. An input mask restricts the way data is entered into a field, so that it is consistent. For instance, if you want all phone numbers to be entered in the standard Australian format (nn) nnnn-nnnn, you can define an input mask to ensure that. If you’re US-based, you might want to use an input mask that looks like this: (nnn) nnn-nnnn.
Be wary about using input masks. Don’t use them if there’s any chance at all that some data will not fit the mask. For instance, if some of the phone numbers in your table are Australian national numbers in the form nn nn nn or US toll-free numbers in the form 1 nnn nnn-nnnn, your input mask won’t fit.
Creating an input mask
Where you’re sure the data will be consistent, feel free to add a mask. To add a mask with the Australian (nn) nnnn-nnnn format to the Work phone field, for instance, you:
- Click the Work Phone field and then click in the Input Mask property box.
- Click the button displaying an ellipsis (…) to display the Input Mask Wizard.
- Click the Phone Number mask in the list and click Next.
- Edit the Input Mask to read (99)9999 9999 and make sure the underscore (_) character is selected in the Placeholder list. You can test your mask by typing sample data in the Try It box. Click Next. (If you’d like to know more about input mask symbols, open Help and type inputmask in the Index keyword box .)
- You can choose to store the data either with or without the parentheses. Choose Without The Symbols and click Next, then Finish.
Assuming common values
If one of your fields usually contains a particular value, you can speed up data entry by specifying that value as the default for the field. Access will then fill in that value for you – allowing you to modify it whenever you need to.
Say, for instance, that our organisation is based in Tasmania and the vast bulk of our members live locally. We can save time by doing this:
1. Clicking the State field.
2. Typing TAS in the Default Value field.
We can get rid of a lot of errors in our database at the source by validating data as it is entered. To do so, you define a validation rule for a field. All data entered will be checked to see whether it conforms to the rule; if it doesn’t, the user will be prompted to correct the error and won’t be able to continue until it has been fixed.
Say, for example, each membership number takes the form of a single, uppercase letter followed by four numbers. Further, the uppercase letter represents the membership type – F for Full, A for Associate, L for Life. To add this rule:
- Click the MembershipID field.
- Click in the Validation Rule property and type Like “[FAL]####”.
If you’d like to know more about validation rules, click F1 while your cursor is in the Validation Rule box. You can find out more about the use of wildcard characters (like the # used in the rule above) by opening Access Help and typing wildcard in the Index or Answer Wizard.
Adding validation help
If you do use validation rules, make sure you also create a matching validation text property. The validation text is the message that pops up if the user types in an invalid entry. If you don’t create your own validation text, you’ll leave your users at the mercy of Access’s default error messages, which are quite likely to cause heart arrhythmia (see Figure 1). So, click in the Validation Text box and type something friendly and useful.
Adding lookup tables
We’re going to do one more very cool thing before we finish with our table structure. We’re going to help speed up data entry by providing pre-defined choices for certain fields (see Figure 2).
For example, we have three membership types – Full, Associate and Life. We’re going to make it so our data entry operator can pick the membership from a drop-down list, instead of having to type the value each time.
To do this, we need to define a list of the permissible membership types and then allow the operator to ‘look up’ the appropriate value. Fortunately, Access provides a Lookup Wizard that steps us through this process.
- Click in the Data Type box beside the MembershipType field. A drop-down arrow will appear. Click it and select Lookup Wizard.
- Select I Will Type In The Values That I Want and click Next. This option lets us create a quick list of our three values. The other option (I Want The Lookup Column To Look Up The Values In A Table Or Query) is useful when you have either a changeable or very large lookup list – say a list of all postcodes. With this option, you can store the lookup values in their own updateable table.
- In the Number Of Columns box type 1 and then type Full, press Tab and type Associate, press Tab and type Life under the Col1 heading.
- Adjust the column width so it just fits the longest value by dragging the right edge of the column header leftwards, then click Finish.
Note, if you want to restrict the values to those contained in this list, you should either select MembershipType in the field list, click the Lookup tab, and change the Display Control from Combo Box to List Box; or you should create a Validation Rule that limits the permissible values to Like “Full” Or Like “Life” Or Like “Associate”.
Now that you’ve created one lookup list, try doing the same thing for the MailingTitle (Ms, Mr, Dr, Professor, and so on), State (TAS, VIC, NSW, QLD, et cetera) and Committee (Membership, Volunteers, Fund Raising, et cetera) fields.
Lookup lists in FileMaker
FileMaker Pro and Lotus Approach offer similar features for data defaults, masks, validation and lookup lists. For example, here’s how you can define a lookup list for the MembershipType field in FileMaker Pro:
- Open the Members database and choose Define Value Lists from the File Menu.
- Click New, type Membership Types in the Value List Name box, and then type Full, Associate and Life (separated by carriage returns) in the Use Custom Values box.
- Click OK and then Done.
- Switch to Layout Mode (via the View Menu) and right-click the Membership Type field.
- Select Field Format from the pop-up list.
- Select Pop-up List, and select the Membership Types value list from the drop-down Using Value List box.
- Click OK, revert to Browse Mode, and test out your new value list.