Creating a New Table

Creating database tables is as much art as it is science. A good working knowledge of the user’s requirements is a primary requirement for any new database project. Let’s take a look at the steps required to create basic Access tables. In the following sections, you’ll study the process of adding tables to an Access database, including the relatively complex subject of choosing the proper data type to assign to each field in a table.

It is always a good idea to plan tables on paper first, before sitting down at Access and using the Access tools to add tables to the database. Many tables, especially small ones, really don’t require a lot of fore-thought before adding them to the database. After all, not much planning is required to design a table holding lookup information such as the names of cities and states. However, more complex entities such as customers and products usually require considerable thought and effort to properly implement.

Although you can create the table interactively without any forethought, carefully planning a database system is a good idea. You can make any changes later, but doing so wastes time; generally, the result is a system that is harder to maintain than one that is well planned from the beginning. Before you get started, you should understand the table design process.

In the following sections, we’ll be exploring the new, blank table added by Access as the new database was created. It’s important to understand the steps required to add new tables to an Access database. Because the steps required to add tables have changed so dramatically from earlier versions of Access, even experienced Access developers will want to read the following sections.

The importance of naming conventions

As your databases grow in size and complexity, the need to establish a naming convention for the objects in your databases increases. As you already know, changes to the name of an object are not propagated throughout the database. Even with the Name AutoCorrect option turned on (Office Button Access Options Current Database Name AutoCorrect Options), Access only corrects the most obvious name changes. Changing the name of a table breaks virtually every query, form, and report that uses the information from that table. Your best defense is to adopt reasonable object names and use a naming convention early on as you begin building Access databases and to stick with the naming convention throughout the project.

Access imposes very few restrictions on the names assigned to database objects. Therefore, it is entirely possible to have two distinctly different objects (for instance, a form and a report, or a table and a macro) with the same name. (You can’t, however, have a table and a query with the same name, because tables and queries occupy the same namespace in the database.)

Although simple names like Contacts and Orders are adequate, as a database grows in size and complexity you may become confused about which object a particular name refers to. When working with Visual Basic for Applications (VBA), the programming language built into Access 2007, there must be no ambiguity or confusion between referenced objects. Having both a form and a report named Contacts might be confusing to you or your code.

The simplest naming convention is to prefix object names with a three- or four-character string indicating the type of object carrying the name. Using this convention, tables are prefixed with tbl and queries with qry. The prefix for forms, reports, macros, and modules are frm, rpt, mcr, and bas or mod, respectively.

In this book, most compound object names appear in mixed case: tblBookOrders, tblBookOrderDetails, and so on. Most people find mixed-case names easier to read and remember than names that appear in all-uppercase or all-lowercase characters (such as TBLBOOKORDERS or tblbookorderdetails).

Also, at times, we’ll use informal references for database objects. For instance, the formal name of the table containing contact information in the previous examples is tblContacts. An informal reference to this table might be “the contacts table.”

In most instances, your users never see the formal names of database objects. One of your challenges as an application developer is to provide a seamless user interface that hides all data-management and data-storage entities that support the user interface. You can easily control the text that appears in the title bars and surfaces of the forms, reports, and other user interface components to hide the actual names of the data structures and interface constituents.

Take advantage of the long object names that Access permits to give your tables, queries, forms, and reports descriptive, informative names. There is no reason why you should confine a table name to ConInfo when ContactInformation is handled just as easily and is much easier to understand.

Descriptive names can be carried to an extreme, of course. There’s no point in naming a form frmUpdateContactInformation if frmUpdateInfo does just as well. Long names are more easily misspelled or misread than shorter names, so use your judgment.

Finally, although Access lets you use spaces in database object names, you should avoid spaces at all costs. Spaces do not add to readability and can cause major headaches, particularly when upsizing to client-server environments or using OLE automation with other applications. Even if you don’t anticipate extending your Access applications to client-server or incorporating OLE or DDE automation into your applications, get into the habit of not using spaces in object names.

The table design process

Creating a table design is a multistep process. By following the steps in order, your table design can be created readily and with minimal effort:

1.	Create a new table.
2.	Enter field names, data types, and (optionally) descriptions.
3.	Enter properties for the fields.
4.	Set the table’s primary key.
5.	Create indexes for necessary fields.
6.	Save the table’s design.

Generally speaking, some tables are never really finished. As users’ needs change, or the business rules governing the application change, you may find it necessary to open an existing table in Design view. This part of the book, like most texts on Access, describes the process of creating tables as if every table you ever work on is brand new. The truth is, however, that most of the work that you do on an Access application is performed on existing objects in the database. Some of those objects you have added yourself, while other objects may have been added by another developer at some time in the past. However, the process of maintaining an existing database component is exactly the same as creating the same object from scratch.

Adding a new table to the database

Begin by selecting the Create tab on the Ribbon at the top of the Access screen. The Create tab (see Figure 34-12) contains all of the tools necessary to create not only tables, but also forms, reports, and other database objects.

There are two main ways to add new tables to an Access database, both of which are invoked from the Tables group on the Create tab:

• Clicking on the Table button: Adds a complete new table to the database.

• Clicking on the Table Design button: Adds a table in Design view to the database.

For our example, we’ll be using the Table Design button, but first, let’s take a look at the Table button.

Clicking on the Table button adds a new table to the Access environment. The new table appears in Datasheet view in the tabbed region of the Access screen. A portion of the new table is shown in Figure 34-13. Notice that the new table appears in Datasheet view, with an ID column already inserted, and an Add New Field column to the right of the ID field.

The Add New Field column is intended to permit users to quickly add tables to an Access database. All that is needed is to simply begin entering data into the Add New Field column. You assign the field a name by right-clicking the field’s heading, selecting Rename Column, and entering a name for the field. In other words, building an Access table can be very much like creating a spreadsheet in Microsoft Excel. This approach was usually referred to as “creating a table in Datasheet view” in previous versions of Microsoft Access.

Although it is entirely possible to build an access table without ever switching to Design view, we believe that this is a terrible idea. Building tables by entering data and casually providing names for the table’s fields circumvents one of the most critical steps in building a serious database system.

Relational database systems such as Access are constructed by breaking data into constituent entities, and then building a table for each entity. The tables in an Access database should carefully and accurately reflect the entities they describe. Seemingly small issues, such as deciding which data type to assign to a field, has a dramatic impact on the utility, performance, and integrity of the database and its data.

Each table added to an Access database, and each field added to every table, should have a purpose in the overall database design. Even when adding tables using the Table button, it is far too easy to add tables that do not conform to good design rules, and which do not fit well into the database’s design.

The second approach to add new tables is to use the Table Design button, located on the right side of the Tables grouping on the Create tab. Access opens a new table in Design view, as shown in Figure 34-14.

The table designer is quite easy to understand, and each column is clearly labeled. At the far left is the Field Name column, where you input the names of fields you add to the table. You assign a data type to each field in the table and (optionally) provide a description for the field.

For this chapter’s example, we’ll create the Contacts table for the Access Auto Auctions application. The basic design of this table is outlined in Table 34-1.

Table 34-1. The Access Auto Auctions Contacts Table Design

Field Name	Data Type	Description
ContactID	AutoNumber	Primary key
ContactType	Text 50	Type of contact (Wholesaler, dealer, parts store, other)
FirstName	Text 50	Contact’s first name
LastName	Text 50	Contact’s last name
Company	Text 50	The Contact’s employer or other affiliation
Address	Text 50	Contact’s address
City	Text 50	Contact’s city
State	Text 50	Contact’s state
ZipCode	Text 50	Contact’s zip code
Phone	Text 50	Contact’s phone
Fax	Text 50	Contact’s fax
E-Mail	Text 100	Contact’s e-mail address
WebSite	Text 100	Contact’s Web address
OrigCustDate	DateTime	The date the contact first purchased something from Access Auto Auctions
TaxLocation	Text 50	Used to determine the applicable sales tax
CreditLimit	Currency	Customer’s credit limit in dollars
CurrentBalance	Currency	Customer’s current balance in dollars
CreditStatus	Text	A description of the customer’s credit status
LastSalesDate	DateTime	The most recent date the customer purchased something from Access Auto Auctions
DiscountPercent	Double	The customary discount provided to the customer
Notes	Memo	Notes and observations regarding this customer
Active	Yes/No	A yes/no value, indicating whether the customer is still buying or selling to Access Auto Auctions

Some of the fields in the preceding table are rather generous in the amount of space allocated for the field’s data. For instance, it is unlikely that anyone’s name occupies 50 characters, but there is no harm in providing for very long names. Access only stores as many characters as are actually entered into a text field. Therefore, allocating 50 characters does not actually use 50 characters for every name in the database.

The design spelled out in Table 34-1 is a good starting point for the Contacts table.

Looking once again at Figure 34-14, you see that the Table Design window consists of two areas:

• The field entry area (top): Use the field entry area to enter each field’s name and data type. You can also enter an optional description.

• The field properties area (bottom): The property area at the bottom of the window is for entering more different specifications, called properties, for each field. These properties include field size, format, input mask, and default value, among others. The actual properties displayed in the properties area depend upon the data type of the field. You learn much more about these properties later in this book.

Using the Design Ribbon tab

The Design tab on the Access Ribbon, shown in Figure 34-15, contains many controls that assist in creating a new table definition.

We will only mention a few of these buttons at this time. You’ll learn much more about the other buttons later as needed.

Primary Key

Use this button to designate which of the fields in the table you want to use as the table’s primary key. By tradition, the primary key appears at the top of the list of fields in the table. Moving a field is easy: Simply left-click on the gray selector to the left of the field’s name to highlight the field in the Table Designer, and drag the field to its new position.

Insert Rows

Although it makes very little difference to the database engine, many developers are fussy about the sequence of fields in a table. Also, particularly when assigning an index or composite index to a table, you want the fields to be next to each other in the table’s field list (composite keys consist of multiple fields combined as a single key). The Insert Row button inserts a blank row just above the position occupied by the mouse cursor. For instance, if the cursor is currently in the second row of the Table Designer, clicking the Insert Row button inserts an empty row in the second position, moving the existing second row to the third position.

Delete Rows

Conversely, the Delete Rows button removes a row from the table’s design. Be careful, however, because Access does not ask you to confirm the deletion before actually removing the row.

Property Sheet

The Property Sheet button opens the Properties window (see Figure 34-16) for the table. These properties enable you to specify important table characteristics such as a validation rule to apply to the entire table, or an alternate sort order for the table’s data.

Indexes

Indexes are discussed in much more detail later in this chapter. Clicking the Indexes button opens the Indexes dialog box, enabling you to specify the details of indexes on the fields in your table.

Working with fields

Fields are created by entering a field name and a field data type in each row of the field entry area of the Table Design window. The field description is an option to identify the field’s purpose. The description appears in the status bar at the bottom of the screen during data entry. After entering each field’s name and data type, you can further specify how each field is used by entering properties in the property area.

Naming a field

A field name should be descriptive enough to identify the field to you as the developer, to the user of the system, and to Access. Field names should be long enough to quickly identify the purpose of the field, but not overly long. (Later, as you enter validation rules or use the field name in a calculation, you’ll want to save yourself from typing long field names.)

To enter a field name, position the pointer in the first row of the Table Design window under the Field Name column. Then type a valid field name, observing these rules:

• Field names can be from 1 to 64 characters.

• Field names can include letters, numbers, and many special characters.

• Field names cannot include a period (.), exclamation point (!), brackets ([ ]), or accent grave (`).

• You can’t use low-order ASCII characters, for example Ctrl+J or Ctrl+L (ASCII values 0 to 31).

• You can’t start with a blank space.

• You can’t use a double quotation mark (“) in the name of a Microsoft Access project file.

You can enter field names in upper-, lower-, or mixed case. If you make a mistake while typing the field name, position the cursor where you want to make a correction and type the change. You can change a field name at any time—even if it’s in a table and the field contains data—for any reason.

Specifying a data type

The next step is to actually create your tables and define your fields for those tables. You must also decide what type of data each of your fields will hold. In Access, you can choose any of several data types (these data types are detailed later in this chapter):

• Text: Alphanumeric characters, up to 255 characters

• Memo: Alphanumeric characters, very long strings up to 65,538 (64K) characters

• Number: Numeric values of many types and formats

• Date/Time: Date and time data

• Currency: Monetary data

• AutoNumber: Automatically incremented numeric counter

• Yes/No: Logical values, Yes/No, True/False

• OLE Object: Pictures, graphs, sound, video, word processing, and spreadsheet files

• Hyperlink: A field that links to a picture, graph, sound, video, and word processing and spreadsheet files

One of these data types must be assigned to each of your fields. You must also specify the length of the Text fields, or accept the default of 50 characters for Text fields.

Designing data-entry rules

The last major design decision concerns data validation, which becomes important as users enter data. You want to make sure that only good data (data that passes certain defined tests) gets into your system. You have to deal with several types of data validation. You can test for known individual items, stipulating that the Gender field can accept only the values Male, Female, or Unknown, for example. Or you can test for ranges, specifying that the value of Weight must be between 0 and 1,500 pounds.

Designing lookup tables

Sometimes you need to design entire tables to perform data validation or just to make it easier to create your system; these are called lookup tables. For example, because Access Auto Auctions needs a field to determine the customer’s tax rate, you may decide to use a lookup table that contains the tax location, and tax rate. Another example is when a customer pays an invoice using some specific method—cash, credit card, money order, and on and on.

Because the tax rate can change, storing tax rates makes much more sense than hard-coding tax rates into the application. Using lookup tables, Access looks up the current tax rate in tblTaxRates whenever an invoice is created. The tax rate applied to an invoice is stored along with the other invoice data in the Invoice/Sales table because it is time-dependent data, and the value stored in tblTaxRates may be different in the future.

Another purpose of a lookup table is to limit data entry in a field to a specific value. For example, you can use a table containing payment types (cash, check, MasterCard, and so on). The payment types table (tblPaymentTypes) can be used as a lookup table to ensure only approved payment methods can be entered in the Invoice table.

Although you can create a field on a data-entry form that limits the entry of valid contact types to seller, buyer, or both, you create a table with only one field—ContactType—and use the ContactType field in tblContacts to link to this field in the ContactType lookup table.

Several lookup tables are included in the Access Auto Auctions application: tblPaymentType, tblTaxRates, and tblCategories.

Assigning field data types

After you name a field, you must decide what type of data the field holds. Before you begin entering data, you should have a good grasp of the data types that your system will use. Ten basic types of data are shown in Table 34-2; some data types (such as numbers) have several options.

Table 34-2. Data Types Available in Microsoft Access

Data Type	Type of Data Stored	Storage Size
Text	Alphanumeric characters	0–255 characters
Memo	Alphanumeric characters	0–65,536 characters
Number	Numeric values	1, 2, 4, or 8 bytes, 16 bytes for Replication ID (GUID)
Date/Time	Date and time data	8 bytes
Currency	Monetary data	8 bytes
AutoNumber	Automatic number increments	4 bytes, 16 bytes for Replication ID (GUID)
Yes/No	Logical values: Yes/No, True/False	1 bit (0 or –1)
OLE Object	Pictures, graphs, sound, video	Up to 1GB (disk space limitation)
Hyperlink	Link to an Internet resource	0–64,000 characters
Attachment	A special field (new in Access 2007) that enables you to attach external files to an Access database	Varies by attachment
Lookup Wizard	Displays data from another table	Generally 4 bytes

Figure 34-17 shows the Data Type drop-down list used to select the data type for any field you just created.

Here are the basic rules to consider when choosing the data type for new fields in your tables:

• The data type should reflect the data stored in the field. For instance, you should select one of the numeric data types to store numbers like quantities and prices. Do not store data like phone numbers or Social Security numbers in numeric fields, however. Your application will not be performing numeric operations like addition or multiplication on phone numbers, so this data should not be stored in numeric fields. Instead, use text fields for common data such as Social Security numbers and phone numbers.

  Also, numeric fields never store leading zeros. Putting a zip code such as 02173 into a numeric field means only the last four digits (2173) are actually stored.

• Consider the storage requirements of the data type you’ve selected. Although you can use a long integer data type in place of a simple integer or byte value, the storage requirements of a long integer (4 bytes) is twice that of a simple integer. This means that twice as much memory is required to use and manipulate the number and twice as much disk space is required to store its value. Whenever possible, use byte or integer data types for simple numeric data.

• Will you want to sort or index the field? Because of their binary nature, memo and OLE object fields cannot be sorted or indexed. Use memo fields sparingly. The overhead required to store and work with memo fields is considerable.

• Consider the impact of data type on sorting requirements. Numeric data sort differently than text data. Using the numeric data type, a sequence of numbers will sort as expected: 1, 2, 3, 4, 5, 10, 100. The same sequence stored as text data will sort like this: 1, 10, 100, 2, 3, 4, 5. If it’s important to sort text data in a numeric sequence, you’ll have to first apply a conversion function to the data before sorting.

  Note

  If it’s important to have text data representing numbers to sort in the proper order, you may want to prefix the numerals with zeros (001, 002, and so on). Then the text values will sort in the expected order: 001, 002, 003, 004, 005, 010, 100.

  
  

• Is the data text or date data? When working with dates, you’re almost always better off storing the data in a Date/Time field than as a Text field. Text values sort differently than date data (dates are stored internally as numeric values), which can upset reports and other output that rely on chronological order.

• Keep in mind the reports that will be needed. You won’t be able to sort or group memo or OLE data on a report. If it’s important to prepare a report based on memo or OLE data, add a Tag field like a date or sequence number, which can be used to provide a sorting key, to the table.

Text data type

Any type of data that is simply characters (letters, numbers, punctuation). Names, addresses, and descriptions are all text data, as are numeric data that are not used in a calculation (such as telephone numbers, Social Security numbers, and zip codes).

Although you specify the size of each Text field in the property area, you can enter no more than 255 characters of data in any Text field. Access uses variable length fields to store text data. If you designate a field to be 25 characters wide and you use only 5 characters for each record, then only enough room to store 5 characters is used in your database.

You will find that the .accdb database file may quickly grow quite large, but text fields are not the usual cause. However, it is good practice to limit text field widths to the maximum you believe they will be used for. Names are tricky because some cultures have long names. However, it is a safe bet that a postal code might be less than 12 characters wide while a U.S. state abbreviation is always 2 characters wide. By limiting the size of the text width, you also limit the number of characters that users can enter when the field is used in a form.

Memo data type

The Memo data type holds a variable amount of data from 0 to 65,536 characters for each record. Therefore, if one record uses 100 characters, another requires only 10, and yet another needs 3,000, you use only as much space as each record requires.

Notice that you did not specify a field size for the Memo data type. Access allocates as much space is necessary for the memo data.

Number data type

The Number data type enables you to enter numeric data; that is, numbers that will be used in mathematical calculations. (If you have data that will be used in monetary calculations, you should use the Currency data type, which performs calculations without rounding errors.)

The exact type of numeric data stored in a number field is determined by the Field Size property. Table 34-3 lists the various numeric data types, their maximum and minimum ranges, the decimal points supported by each numeric data type, and the storage (bytes) required by each numeric data type.

Table 34-3. Numeric Field Settings

Field Size Setting	Range	Decimal Places	Storage Size
Byte	0 to 255	None	1 byte
Integer	−32,768 to 32,767	None	2 bytes
Long Integer	−2,147,483,648 to 2,147,483,647	None	4 bytes
Double	−1.797 × 10308 to 1.797 × 10308	15	8 bytes
Single	−3.4 × 1038 to 3.4 × 1038	7	4 bytes
Replication ID	N/A	N/A	16 bytes
Decimal	1–28 precision	15	8 bytes

Many errors are caused by choosing the wrong numeric type for number fields. For instance, notice that the maximum value for the Integer data type is 32,767. We once saw a database that ran perfectly for several years and then started crashing with overflow errors. It turned out that the overflow was caused by a particular field being set to the Integer data type, and when the company occasionally processed very large orders, the 32,767 maximum was exceeded.

It is best to design your tables very conservatively and allow for larger values than you ever expect to see in your database. This is not to say that it is a good idea to use the Double data type for all numeric fields. The Double data type is very large (8 bytes) and is quite slow when used in calculations for another numeric operation. Instead, the Single data type is probably best for most floating-point calculations, and the Long Integer is best for most data where decimal points are irrelevant.

Date/Time data type

The Date/Time data type is a specialized number field for holding dates or times (or dates and times). When dates are stored in a Date/Time field, it is easy to calculate days between dates and other calendar operations. Date data stored in Date/Time fields sort and filter properly as well.

Currency

The Currency data type is another specialized number field. Currency numbers are not rounded during calculations and preserve 15 digits of precision to the left of the decimal point and 4 digits to the right. Because the Currency data type uses a fixed-decimal-point position, it is faster in numeric calculations than doubles.

AutoNumber

The AutoNumber field is another specialized Number data type. When an AutoNumber field is added to a table, Access automatically assigns an integer value to the field (beginning at 1) and increments the value each time a record is added to the table. Alternatively (determined by the New Values property), the value of the AutoNumber field is a random integer that is automatically inserted into new records.

Only one AutoNumber field can appear in a table. Once assigned to a record, the value of an AutoNumber field cannot be changed programmatically or by the user. AutoNumber fields are equivalent to the Long Integer data type and occupy 4 bytes, but display only positive values. The range of possible values for AutoNumber fields range from 1 to 4,294,967,296—more than adequate to use as the primary key for most tables.

Yes/No

Yes/No fields accept only one of two possible values. Internally stored as a 1 or 0 bit, the Yes/No field is used to indicate yes/no, on/off, or true/false. A Yes/No field occupies a single bit of storage.

OLE Object

The OLE Object field stores OLE data, highly specialized binary objects such as Microsoft Word documents, Excel worksheets, sound or video clips, and images. The OLE object is created by an OLE server application and can be linked to the parent application or embedded in the Access table. OLE objects can only be displayed in bound object frames in Access forms and reports. OLE objects can be as large as 1GB or more in size. OLE fields cannot be indexed.

Attachment

The Attachment data type is new for Access 2007. In fact, the Attachment data type is one of the reasons Microsoft changed the format of the Access data file. The older MDB format is unable to accommodate attachments.

The Attachment data type is relatively complex, compared to the other type of Access fields.

Hyperlink data type

The Hyperlink data type field holds combinations of text and numbers stored as text and used as a hyperlink address. It can have up to three parts:

• The visual text that appears in a field (usually underlined).

• The Internet address—the path to a file (UNC, or Universal Naming Convention, path) or page (URL or Uniform Resource Locator).

• Any sub-address within the file or page. An example of a sub-address is the name of an Access 2000 form or report. Each part is separated by the pound symbol (#).

Lookup Wizard

The Lookup Wizard data type inserts a field that enables the end user to choose a value from another table or from the results of an SQL statement. The values may also be presented as a combo box or list box. At design time, the Lookup Wizard leads the developer through the process of defining the lookup characteristics when this data is assigned to a field.

Dragging a field created by the Lookup Wizard from the field list as you design a form, a combo box or list box is automatically created on the form. The list box or combo box also appears on a query datasheet that contains the field.

Entering a field description

The field description is completely optional; you use it only to help you remember a field’s uses or to let another developer understand the field’s purpose. Often you don’t use the description column at all, or you use it only for fields whose purpose is not obvious. If you enter a field description, it appears in the status bar whenever you use that field in Access—in the datasheet or in a form. The field description can help clarify a field whose purpose is ambiguous or give the user a fuller explanation of the values valid for the field during data entry.