Altering Fields and Relationships

When you are designing your own database, you often discover that you must alter the original choices that you made for the sequence of fields in a table, data types, or relationships between tables. One reason for adding substantial numbers of records to tables during the testing process is to discover any necessary changes before putting the database into daily use.

You can change formats, validation rules and text, lengths of Text fields, and other minor items in the table by changing to design mode, selecting the field to modify, and making the changes in the property boxes. Changing data types can cause a loss of data, however, so be sure to read the later section Changing Field Data Types and Sizes before you attempt to make such changes. Changing relationships between tables is considered a drastic action if you have entered a substantial amount of data, so this subject also is covered later in "Changing Relationships Between Tables."

Rearranging the Sequence of Fields in a Table

If you are manually entering historical data in Datasheet view, you might find that the sequence of entries isn't optimum. You might, for example, be entering data from a printed form with a top-to-bottom, left-to-right sequence that doesn't correspond to the left-to-right sequence of the corresponding fields in your table. Access makes rearranging the order of fields in tables a matter of dragging and dropping fields where you want them. You can choose whether to make the revised layout temporary or permanent when you close the table.

To rearrange the fields of the Personnel Actions table, follow these steps:

1.	Click the Datasheet View button. Rearranging the sequence of fields is the only table design change that you can implement in Access's Datasheet view.
2.	Click the field name button of the field you want to move. This action selects the field name button and all the field's data cells.
3.	Hold down the left mouse button while over the field name button. The mouse pointer turns into the drag-and-drop symbol, and a heavy vertical bar marks the field's far-left position. Figure 4.39 shows the paScheduledDate field being moved to a position immediately to the left of the paEffectiveDate field. Figure 4.39. Dragging a field to a new position in Datasheet view.
4.	Move the vertical bar to the new position for the selected field and release the mouse button. The field assumes the new position shown in Figure 4.40.
5.	When you close the Personnel Actions table, you see the familiar Save Changes message box. To make the modification permanent, click OK; otherwise, click No.

To reposition fields in Design view, click the select button of the row of the field that you want to move and then drag the row vertically to a new location. Changing the position of a table's field doesn't change any of the field's other properties.

Changing Field Data Types and Sizes

You might have to change a field data type as the design of your database develops, or if you import tables from another database, a spreadsheet, or a text file. If you import tables, the data type automatically chosen by Access during the importation process probably won't be what you want, especially with Number fields. Chapter 7, "Linking, Importing, and Exporting Tables," discusses importing and exporting tables and data from other applications. Another example of altering field properties is changing the number of characters in fixed-length Text fields to accommodate entries that are longer than expected, or converting variable-length Text fields to fixed-length fields.

Numeric Fields. Changing a data type to one that requires more bytes of storage is, in almost all circumstances, safe. You do not sacrifice your data's accuracy. Changing a numeric data type from Byte to Integer to Long Integer to Single and, finally, to Double does not affect your data's value because each change, except for Long Integer to Single, requires more bytes of storage for a data value. Changing from Long Integer to Single and Single to Currency involves the same number of bytes and decreases the accuracy of the data only in exceptional circumstances. The exceptions can occur when you are using very high numbers or extremely small decimal fractions, such as in some scientific and engineering calculations.

On the other hand, if you change to a data type with fewer data bytes required to store it, Access might truncate your data. If you change from a fixed-point format (Currency) or floating-point format (Single or Double) to Byte, Integer, or Long Integer, any decimal fractions in your data are truncated. Truncation means reducing the number of digits in a number to fit the new Field Size property that you choose. If you change a numeric data type from Single to Currency, for example, you might lose your Single data in the fifth, sixth, and seventh decimal places (if any exists) because Single provides as many as seven decimal places and Currency provides only four.

You cannot convert any field type to an AutoNumber-type field. You can use the AutoNumber field only as a record counter; the only way that you can enter a new value in an AutoNumber field is by appending new records. You cannot edit an AutoNumber field. When you delete a record in Access, the AutoNumber values of the higher-numbered records are not reduced by 1. Sequential Access AutoNumber field values are assigned to records in the order in which the records were entered, not the order of the primary key. Access also provides a random assignment of AutoNumber field values, primarily for use with replicated databases. The randomly assigned AutoNumber values help ensure that, when replicated database copies are reconciled, no values are duplicated in AutoNumber fields.

Text Fields. You can convert Text fields to Memo fields without Access truncating your text. Converting a Memo field to a Text field, however, truncates characters beyond the 255-character limit of Text fields. Similarly, if you convert a variable-length Text field to a fixed-length field, and some records contain character strings that exceed the length that you chose, Access truncates these strings.

Conversion Between Number, Date, and Text Field Data Types. Access makes many conversions between Number, Date, and Text field data types for you. Conversion from Number or Date to Text field data types does not follow the Format property that you assigned to the original data type. Numbers are converted with the General Number format, and dates use the Short Date format. Access is quite intelligent in the methods it uses to convert suitable Text fields to Number data types. For example, it accepts dollar signs, commas, and decimals during the conversion, but ignores trailing spaces. Access converts dates and times in the following Text formats to internal Date/Time values that you then can format the way that you want:

1/4/97 10:00 AM
04-Jan-97
January 4
10:00
10:00:00

Changing Relationships Between Tables

Adding new relationships between tables is a straightforward process, but changing relationships might require you to change data types so that the related fields have the same data type. To change a relationship between two tables, complete the following steps:

1.	Close the tables involved in the relationship.
2.	If the Database window is not active, click the Show Database Window button, or choose Window, 1 Database.
3.	Display the Relationships window by clicking the Relationships button of the toolbar or by choosing Tools, Relationships.
4.	Click the join line that connects to the field whose data type you want to change. When you select the join line, the line becomes darker (wider), as shown in Figure 4.41. Figure 4.41. Selecting a relationship to delete or modify.
5.	Press Delete to clear the existing relationship. Click Yes when the message box asks you to confirm your deletion.
6.	If you are changing the data type of a field that constitutes or is a member field of the primary table's primary key, delete all other relationships that exist between the primary table and every other table to which it is related.
7.	Change the data types of the fields in the tables so that the data types match in the new relationships.
8.	Re-create the relationships by using the procedure described earlier in the section "Establishing Relationships Between Tables."