Creating Access Tables

Let's turn now to the specifics of creating Access tables for storing your data. Basically five ways exist for creating a new Access table:

• Use the Table Wizard

• Enter data directly into a datasheet

• Import data or link to an external source

• Use a Make Table query

• Build the table in Design view

I will focus on the last choice of starting from scratch in Design view, which is usually preferred. Importing and linking are discussed in Chapter 13, "Importing and Exporting," and make-table queries are described in Chapter 9, "Queries, Part II." That leaves using the Table Wizard and entering data into a datasheet, both of which I quickly dispose of now.

Table Wizard

The Table Wizard is of some use, but not for its intended purpose of creating a table. In the Database window of any database, click Tables and double-click Create Table by Using Wizard. The first dialog box of the wizard includes sample fields for both business and personal tables. As you move from table to table in the Sample Tables pane, you can see the possible fields for each. This is one check for making sure you've included all the fields you need.

The Table Wizard gives you some ideas for fields and field names. But unlike some other wizards that truly make object creation simpler and more efficient, I think the Table Wizard offers relatively few benefits. Tables are the foundation of your database, so you should spend the time to build them from scratch rather than use shortcuts.

Direct Entry

To use the direct-entry method, you double-click Create Table by Entering Data in the Tables section of the Database window. In the direct-entry method, you enter column names (which become the field names) and a few rows of sample data into a datasheet. For example, let's say you want to create a table for investments. You might enter the following column headings and data for the first few fields:

Security Name	Security Symbol	Shares Owned
Boeing	BA	700
DuPont	DD	550
Ford Motor	F	1,600

If you have entered the values with some consistency, Access will make a reasonable stab at creating a table with fields of the appropriate data type (in this case, Text in the first two columns and Number in the third) and display format.

There seems little to gain by this process because Access can easily suggest the wrong data types. In addition, by using column headings to name fields, the direct-entry method mingles field names and captions in a way that is disconcerting. I mention this issue again in the "Captions" section.

Table Design Procedure

Table creation is such a vital component of making an Access database, so you usually want to create fields and specify field properties one by one in the Design view of a table. Part of this process is reviewing all field property settings entered by default, to make certain they are the best choices for your fields.

At this point, you have already designed your database; you know the tables you are going to include, the primary and foreign keys in each, and most or all of the fields and their data types. Here is the basic procedure for creating tables:

1.	In the Database window, click the Tables button and double-click Create Table in Design View.
2.	Enter a field name in the Field Name column.
3.	If applicable, designate the field (or fields) as the primary key.
4.	Select the data type in the Data Type column.
5.	Provide a description.
6.	Assign field properties in the Field Properties pane.
7.	Save the table with an appropriate name. As you've seen, I'm using the Leszynski naming convention, as described in the sidebar. Thus, the tables in the sample databases always have the prefix tbl, as in tblSuppliers, tblCountries, and so on. If you find this baffling and confusing, don't use these prefixes.
8.	Enter additional fields as necessary.
9.	Designate table properties.

All of the steps except the first are described in detail in the rest of this chapter.

Leszynski Naming Convention

If you've opened the NiftyLions database, you've noticed that the object names are prefixed with an abbreviation that identifies the object. For example a table of suppliers is tblSuppliers, consisting of the prefix tbl, which identifies the object as a table, and the subject of the table, suppliers.

This spelling follows the Leszynski naming convention, a system for naming Access objects. The system actually suggests prefixes for a wide range of Access elements, but I have adopted it only for tables (tbl), queries (qry), forms (frm), reports (rpt), and macros (mcr).

I debated whether to implement the convention to even this small of an extent. Naming a table tblSuppliers instead of Suppliers might seem to be a device to make your life more complicated or needlessly make your database more "techie." But I eventually decided to use it because (a) you'll often see objects named this way, (b) it does identify the object precisely within the Database window and outside it, and (c) when you get used to seeing objects named this way, an object without a prefix seems slightly naked. I think these reasons outweigh the initial confusion you might have already faced by including them. Please note that the use of the Leszynski convention is by no means universal; many firms and users have their own conventions for naming Access elements, including tables.