beginners menu


Databasics IV: Streamlining data entry

In the previous tutorial we 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. If you’re using FileMaker Pro or Lotus Approach, read through the tutorial, and at the end you’ll find some brief directions to achieve the same results using those database programs.

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 Approach and FileMaker Pro) 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
MembershipNumber* Text 5
FirstName Text 25
LastName Text 25
MailingTitle Text 10
StreetAddress Text 30
Suburb Text 30
State Text 3
PostalCode Text 4
WorkPhone Text 20
HomePhone Text 20
ListWorkPhone Yes/No  
ListHomePhone Yes/No  
Email Hyperlink  
Joined Date  
Active Yes/No  
MembershipType Text 9
MembershipExpires Date  
FeesPaid Yes/No  
AmountPaid Currency  
Committee Text 12
Skills Text 255

Table 1. The Members table structure (* the asterisk denotes a key field). Note: 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.

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 MembershipNumber 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:

Last name
Mailing title
Street # and name
Zip (you may want to label this PostCode if you’re in Australia)
Work phone
Home phone
List work number?
List home number?
Member since
Membership type
Membership expires
Paid up?
Amount paid

Now scoot back up to the MembershipNumber 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 US toll-free numbers in the form 1 nnn nnn-nnnn or Australian national numbers in the form nn nn nn, 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:

1. Click the Work Phone field and then click in the Input Mask property box.

2. Click the button displaying an ellipsis (…) to display the Input Mask Wizard.

3. Click the Phone Number mask in the list and click Next.

4. 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 .)

5. 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 organization 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.

Eliminating errors

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:

1. Click the MembershipNumber field.

2. 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.

Compare Access's awful default error message (above) with a user-friendly, custom-designed message (below)

Figure 1. Which error message would you rather be confronted with? Access's in-built one (top) or the custom-made one (bottom)? The lesson is, don't rely on inbuilt error messages – create your own so that your users are informed rather than bemused.

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.

  1. Click in the Data Type box beside the MembershipType field. A drop-down arrow will appear. Click it and select Lookup Wizard.

  2. 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.

  3. 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.

  4. 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".

Lookup lists can save a lot of time during data entry

Figure 2. Lookup lists can be huge timesavers when it comes to data entry time. Use them liberally where appropriate.

Now that you’ve created one lookup list, try doing the same thing for the MailingTitle (Ms, Mr, Dr, Professor, and so on), State (VA, TX, 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:

  1. Open the Members database and choose Define Value Lists from the File Menu.

  2. 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.

  3. Click OK and then Done.

  4. Switch to Layout Mode (via the View Menu) and right-click the Membership Type field.

  5. Select Field Format from the pop-up list.

  6. Select Pop-up List, and select the Membership Types value list from the drop-down Using Value List box.

  7. Click OK, revert to Browse Mode, and test out your new value list.

Lookup lists in Approach

To do the same thing in approach:

  1. Open the Members database and click the Design button.

  2. Right-click the Membership Type field, select Field Properties from the pop-up menu, and in the Basic tab click Field Definition.

  3. Click the Options button and then the Validation tab.

  4. Click One Of, type Full, click Add, type Associate, click Add, type Life, click Add, and click OK.

top home beginners menu