|  Before you add a table to a database that you've created or to one of the sample databases supplied with Access, you need to know the terms and conventions that Access uses to describe the structure of a table and the fields that contain the table's data items. With Access, you specify property values for tables and fields.
  Properties of Jet tables apply to the table as a whole. You enter properties of tables in text boxes of the Table Properties window (see Figure 5.3), which you display by clicking the toolbar's Properties button in Table Design view. Setting table property values is optional unless you have a specific reason to override the default values.
Figure 5.3. The Table Properties dialog for Northwind.mdb's Orders table uses default values for all but the Description property.  Following are brief descriptions of some of the 12 properties of Access 2003's Jet tables. The properties related to subdatasheets are detailed in the next section. Description is a text explanation of the table's purpose. If you choose View, Details, the Database window displays this description. This description also is useful with a data dictionary, which you use to document databases and database applications.  Default View lets you select from Datasheet, PivotTable, and PivotChart views of a table, a feature introduced by Access 2002. The default selection is Datasheet view. PivotTable and PivotChart views of tables seldom are meaningful. Chapter 12, "Working with PivotTable and PivotChart Views," describes how to design queries that optimize the usefulness of these two views.
Validation Rule is an expression (formula) used to establish domain integrity rules for more than one field of the table. The Validation Rule expression that you enter here applies to the table as a whole, instead of to a single field. Validation rules and domain integrity are two of the subjects of Chapter 6, "Entering, Editing, and Validating Jet Table Data."
 For more information on validation rules for tables, see "Adding Table-Level Validation Rules with the Expression Builder," p. 230.
Validation Text specifies the text of the message box that opens if you violate a table's Validation Rule expression. Filter specifies a constraint to apply to the table whenever it's open. Filters restrict the number of records that appear, based on selection criteria you supply. Chapter 7, "Sorting, Finding, and Filtering Data," discusses filters.
To learn more about filters, see "Filtering Table Data," p. 249.
Order By specifies a sort(ing) order to apply to the table whenever it's opened. Chapter 7 also explains sort orders. If you don't specify a sort order, records display in the order of the primary key, if a primary key exists. Otherwise, they appear in the order in which you enter them. The "Working with Relations, Key Fields, and Indexes" section, later in this chapter, discusses primary key fields.
For the details of applying sort order to a table, see "Sorting Table Data," p. 242.
Orientation, another Access 2002 property, lets you specify right-to-left display of data in languages such as Hebrew and Arabic. Orientation is an Access-only data display property, and doesn't affect how Jet stores the data.
Note | SQL Server 2000 has a new extended properties feature to support special Jet and Access table and field properties, such as subdatasheets and lookup fields. The Table Properties dialog differs greatly from Access's version, but Table Design view of SQL Server's da Vinci toolset also called the project designer is similar to Access's Jet Table Design view. |
Table Properties for Subdatasheets   Access 2000 introduced subdatasheets to display sets of records of related tables in nested datasheets. You can use subdatasheets in the Datasheet view of tables and queries, and also in forms and subforms. Access, not Jet, implements subdatasheets. Figure 5.4 illustrates Northwind.mdb's Orders table in Datasheet view with a subdatasheet opened to display related records from the Order Details table. The Orders Details (child) table has a many-to-one relationship with the Orders (master) table. To open a subdatasheet, click the + symbol adjacent to a record selection button.
Figure 5.4. Opening a subdatasheet displays records of the child table (Order Details) that are related to the selected record in the master table (Orders).  If you're not familiar with relationships between tables, see "Removing Data Redundancy with Relationships," p. 130.
The following table properties apply to subdatasheets: Subdatasheet Name determines whether and how subdatasheets display data in related records. The default value is [Auto], which automatically adds subdatasheets for records linked from a related table that has a many-to-one relationship with the open table. You also can select a name from a list of the database's tables and queries. A value of [None] turns off subdatasheets in the master table. Link Child Fields specifies the name of the linked field of the related (subordinate) table whose records appear in the subdatasheet. You don't need to specify a value if the Subdatasheet Name property value is [Auto] and a many-to-one relationship exists with the master table. Link Master Fields specifies the name of the linking field of the master table, if you specify a Subdatasheet Name value. Subdatasheet Height, if supplied, specifies the maximum height of the subdatasheet. A value of 0 (the default) allows the subdatasheet to display all related records, limited only by the size of the master datasheet or subdatasheet. Subdatasheet Expanded controls the initial display of the subdatasheet. Setting the value to Yes causes the datasheet to open with all subdatasheets expanded (open).
Note You can nest subdatasheets within other subdatasheets. For example, the Customers table has an Order table subdatasheet that, in turn, has an Order Details table subdatasheet.
For information on how to create a subdatasheet for a table, see "Adding Subdatasheets to a Table or Query," p. 419.
Field Properties  Selecting a table in the Database window and clicking the Design button opens the Table Design window. You assign each field of a Jet table a set of properties. You specify values for the first three field properties in the Table Design grid, the upper pane of the Table Design window shown in Figure 5.5. The primary key of the Customers table is the CustomerID field, indicated by a small key symbol in the field selection button. You set the remaining property values in the Table Design window's lower pane, Field Properties. Figure 5.5. Northwind.mdb's Customers table's CustomerID field is designated the primary key field. All fields of the Customer tables are of the Text data type.  If you're not familiar with the term primary key, see "Defining the Structure of Relational Databases," p. 129.
The following list summarizes the properties you set in the Table Design grid: Field Name You type the name of the field in the Table Design grid's first column. Field names can be as long as 64 characters and can include embedded (but not leading) spaces and punctuation except periods (.), exclamation marks (!), and square brackets ([]). Field names are mandatory, and you can't assign the same field name to more than one field in the same table. It's good database programming practice not to include spaces or punctuation characters in field names. (Substitute an underscore (_) for spaces or use uppercase and lowercase letters to improve the readability of field names.) Minimizing the length of field names conserves resources. Data Type You select data types from a drop-down list in the Table Design grid's second column. Data types include Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, and Lookup Wizard. (The Lookup Wizard is an Access feature, not a data type). Choosing a data type is the subject of the next section. Description You can enter an optional description of the field in the text box in the Table Design grid's third column. If you add a description, it appears in the status bar at the lower left of Access's window when you select the field for data entry or editing. Description is a special property of Jet and SQL Server 2000 databases and is for informative purposes only. Primary Key To choose a field as the primary-key field, select the field by clicking the field-selection button to the left of the Field Name column, and then click the Primary Key button on the toolbar.
Depending on the specific data type that you choose for a field, you can set additional properties for a table field. You set these additional properties on the General page of the Table Design window's Field Properties pane by selecting from drop-down or combo lists or by typing values in text boxes. You use the Field Properties pane's Lookup page to set the control type for lookup fields on forms list box, combo list, and so on. Chapter 15, "Designing Custom Multitable Forms," describes how to use lookup fields. The following list summarizes the General field properties of Jet tables: Field Size You enter the field size for the Text data type in this text box. (See the "Fixed-Width Text Fields" section later in this chapter to learn how to select a text field size.) For most Numeric data types, you determine the field size by selecting from a drop-down list. The Decimal data type requires that you type values for Precision and Scale. Field size doesn't apply to the Date/Time, Yes/No, Currency, Memo, Hyperlink, or OLE Object data type. Format You can select a standard, predefined format in which to display the values in the field from the drop-down combo list that's applicable to the data type that you selected (except Text). Alternatively, you can enter a custom format in the text box (see "Custom Display Formats" later in this chapter). The Format property doesn't affect the data values; it affects only how these values are displayed. The Format property doesn't apply to OLE Object fields. Precision This property appears only when you select Decimal as the data size of the Number data type. Precision defines the total number of digits to represent a numeric value. The default is 18, and the maximum value is 28 for Jet .mdb files. Scale Like Precision, this property appears only for the Decimal data size selection. Scale determines the number of decimal digits to the right of the decimal point. The value of Scale must be less than or equal to the Precision value. Decimal Places You can select Auto or a specific number of decimal places from the drop-down combo list, or you can enter a number in the text box. The Decimal Places property applies only to Number and Currency fields. Like the Format property, the Decimal Places property affects only the display, not the data values, of the field. Input Mask Input masks are character strings, similar to the character strings used for the Format property, that determine how to display data during data entry and editing. If you click the Builder button for a field of the Text, Currency, Number, or Date/Time field data type, Access starts the Input Mask Wizard to provide you with a predetermined selection of standard input masks, such as telephone numbers with optional area codes. Caption If you want a name (other than the field name) to appear in the field name header button in Table Datasheet view, you can enter an alias for the field name in the Caption list box. The restrictions on field name punctuation symbols don't apply to the Caption property. (You can use periods, exclamation points, and square brackets, if you want.) Default Value By entering a value in the Default Value text box, you specify a value that Access automatically enters in the field when you add a new record to the table. The current date is a common default value for a Date/Time field. (See "Setting Default Values of Fields" later in this chapter for more information.) Default values don't apply to fields with AutoNumber or OLE Object field data types. Validation Rule Validation rules test the value entered in a field against criteria that you supply in the form of a Jet expression. Unlike table-level validation rules, the field validation expression operates only on a single field. The Validation Rule property isn't available for fields with AutoNumber, Memo, or OLE Object field data types.
For an example of applying field-level validation rules, see "Adding Field-Level Validation Rules," p. 228.
Validation Text You enter the text that is to appear in the status bar if the value entered does not meet the Validation Rule criteria. Required If you set the value of the Required property to Yes, you must enter a value in the field. Setting the Required property to Yes is the equivalent of typing Is Not Null as a field validation rule. (You don't need to set the value of the Required property to Yes for fields included in the primary key because Jet doesn't permit Null values in primary-key fields.) Allow Zero Length If you set the value of the Allow Zero Length property to No and the Required property to Yes, the field must contain at least one character. The Allow Zero Length property applies to the Text, Memo, and Hyperlink field data types only. A zero-length string ("") and the Null value aren't the same. Indexed From the drop-down list, you can select between an index that allows duplicate values or one that requires each value of the field to be unique. You remove an existing index (except from a field that is a single primary-key field) by selecting No. The Indexed property is not available for Memo, OLE Object, or Hyperlink fields. (See "Adding Indexes to Tables" later in this chapter for more information on indexes.) New Values This property applies only to AutoNumber fields. You select either Increment or Random from a drop-down list. If you set the New Values property to Increment, Access generates new values for the AutoNumber field by adding 1 to the highest existing AutoNumber field value. If you set the property to Random, Jet generates new values for the AutoNumber field by producing a pseudo-random long integer.  | The "Gaps in AutoNumber Field Values" element of the "Troubleshooting" section near the end of the chapter discusses issues when you delete records from a table that has an AutoNumber field. |
Unicode Compression Unicode is a method of encoding characters in multiple alphabets with two bytes, instead of the conventional single-byte ASCII or ANSI representation. Ordinarily, the use of two-byte encoding doubles the space occupied by values typed in Text, Memo, and Hyperlink fields. The first Unicode character of languages using the Latin alphabet is 0. If Unicode compression is set to Yes, the default value, Jet stores all Unicode characters with a first-byte value of 0 in a single byte. IME Mode and IME Sentence Mode These two properties apply only to fields having Text, Memo, and Hyperlink data types. IME is an abbreviation for Office 2003's Input Method Editor, which governs the method of inputting characters of East Asian languages. IME Sentence Mode is applicable only to the Japanese language. A discussion of IME features is beyond the scope of this book.  Smart Tags You can add smart tags, which usually link Internet resources to a specific field. For example you can add a Financial Symbol smart tag to a Jet table field containing New York Stock Exchange or NASDAQ stock symbols to let users select from stock quotes, company reports, and recent company news from the MSN Money Central Web site. Access 2003 also uses a smart tag to apply changes in a property value to dependent database objects.
For details on Access 2003's use of smart tags to propagate field property value changes to other database objects, see "Working with Object Dependencies and Access Smart Tags," p. 200.
As illustrated later in this chapter, adding the first example table, HRActions, to the Northwind.mdb database, requires you to specify appropriate data types, sizes, and formats for the table's fields. |
