Reviewing fields

Take a look at your field list and think about how you’ll use that data. For example, you might have Volunteer Name as one of your fields. Now, should that be one field or two? Well, if you’ll always refer to your volunteers by full name, like Ken Cook, then one field is fine. However, that seems unlikely. Suppose your database produces emails to volunteers. It’s unlikely you’ll want the email to begin with “Hi Ken Cook”. It would say “Hi Ken”. So, now you know that you need two fields, one for first and one for last name.

Address is another example. Do you want to group your volunteers by city on a report? If the answer is yes, you can’t lump the entire address in one field. You’ll need fields for street, city, state, and zip code. This gives you the most flexibility.

Take a good look at your field list and make sure you have each piece of information at the most granular level. You’ll thank yourself for taking time now to think this through. Once you have your volunteer field on two forms and five reports and it’s time to build that email functionality, if you have two fields (first name and last name), you’ll smile! If you have one field, you’ll frown because you have to split the name into two fields and change all the forms and reports that use that field to incorporate the two fields.

Determining data types

After you have a good field list, it’s time to think about the type of data each field will store. Will it store a date, a number, text, or perhaps just the choices of true or false? Setting data types correctly affects overall database size and performance. Next to each field, write down the type of data the field will store and the maximum size of that data.

Grouping fields into tables

At this point, you’ve done some great work specifying each field and the data type and size for each field. Now it’s time to organize the fields into tables. This may seem easy. Just one table for everything, right? Wrong! There are some guiding principles when grouping fields into tables:

• Make sure the data is related. The data should share a common theme like volunteer contact information, events, or locations.
• Make sure the data does not repeat. If you find yourself entering the same volunteer into the Volunteers table two times because they have two addresses, you need a new table.
• Make sure related choices do not become field names. If you have three volunteer fields in your events table called Volunteer1, Volunteer2, and Volunteer3, then you’ve got a problem. Querying data like this is difficult. If you need to specify multiple volunteers for the same event, then you need a new table.

Take your list of fields and group them by subject. Then check for duplicates per points two and three in the previous list. When you’re done, you might have something like Figure 4-1.

What is normal?

The process of building tables so you don’t repeat data or repeat fields is called normalization. A normalized table follows the guiding principles outlined in the previous section. Figure 4-2 shows a table that is not normalized. Can you figure out why?

Didn’t figure it out? OK, I’ll give you a hint. Suppose the Swim-a-thon requires a fourth volunteer? What would you do? Add a new field? That would fix the problem for now, but what happens if a fifth volunteer is needed? You can’t keep adding fields. The answer is to remove the volunteer series of fields and create a new table called EventVolunteers. This new table allows you to add as many volunteers as you need to each event.

If you know you could have two addresses per volunteer or ten volunteers per event, then you need a new table for addresses and another for event volunteers. This step is crucial to proper table design.

If the figures in this section make no sense, not to worry. Queries are covered in Part IV. I need to show you this so you understand how good table design saves time and effort!

Why? Because it becomes very difficult to get a list of events that Burrell volunteered for if you have ten volunteer fields. Figure 4-3 shows the query in Design view with the abnormal events table.

Just imagine if there were ten volunteer fields! You’d have to type Burrell’s name ten times on ten different rows (see Figure 4-4). Also, notice how Burrell’s name shows in multiple columns along with names of other volunteers that you’re not interested in at the moment. If you had ten volunteer fields, you’d see a lot of names of other volunteers.

Now let’s look at the same query with normalized tables, as shown in Figures 4-5 and 4-6.

If you compare Figure 4-4 to 4-6, you’ll see that Figure 4-6 returns the results we want without the extra names we don’t want. If you compare Figures 4-3 and 4-5, I think you’ll agree that Figure 4-5 is the easier query to write. Both queries return the same number of events, but the second one is easier to build and has cleaner results! This is why it’s so important that the table design is right the first time around. I can tell you from personal experience, getting design right the first time will save you a lot of aggravation!

Creating fields

You can’t save a table unless it has at least one field. Because you’ve done the legwork of organizing your data into fields and tables up front (if you didn’t do this, see the section, “Planning Your Database Tables” earlier in this chapter), this part should be easy. These steps assume you have a table open in Design view.

1. Click in the first blank row in the Field Name column.

2. Type your field name.

   A field name can be a maximum of 64 characters.

   Keep your field names short and descriptive of what they’ll store. You’ll sometimes need to type field names out when referring to them in queries or on forms and reports. Long field names mean more typing! Avoid using spaces in field names. Field names with spaces must be surrounded by square brackets ([]) that you’ll have to type. If your field name does not have a space and you need to type it out, Access will enter the square brackets for you. That Access is one awesome helper, don’t you think?

3. In each subsequent blank row, type each field name that belongs with your new table.

   This is shown in Figure 4-8.

4. Click File  ⇒  Save (or press Ctrl+S) to save your new table.

   Care to guess the maximum size of a table name? If you said 64, you’re a winner!

Keep table names short and avoid using spaces in them for the same reasons I mention in the field name tip. Avoid special characters in field and table names; many of them aren’t allowed. Stick to letters, numbers, and underscores (_) and you won’t have any problems. Use underscores rather than spaces if you need to differentiate words in your names.

Setting data types

Once you have your field names entered, tell Access what data they will hold by choosing a data type. If you don’t know what a data type is or what to choose, see the sections, “Field Types and Uses” in Chapter 3 and “Determining data types” earlier in this chapter. Here are the data type steps:

1. Click in the Data Type column next to the new field, as shown in Figure 4-9.

   A drop-down list appears with data type choices.

2. Select a data type from the list.

3. Enter a field size on the Field Size property row in the Field Properties section of table design.

   Not every data type needs a field size, but most do.

4. Repeat Steps 2 and 3 for each field in the table.
5. Save the table.

Take your time to build tables. Get it right the first time and you’ll save yourself a lot of aggravation down the road.