Modifying Linked and Imported Tables
Access provides a great deal of flexibility in the presentation of the Datasheet view of tables. You can rearrange the sequence of fields in Datasheet view without changing the underlying structure of the database. You also can alter the Caption property of the fields to change the names of the field name buttons. Although you cannot modify the field names, field data types, or Field Length properties of linked tables, you can use the Format property to display the data in linked or imported tables in various ways, as described in the following sections.
Restructuring the Presentation of Tables
The basic structure of the tables you link or import is controlled by the structure of the files from which the table was created. The original structure, unfortunately, may not be in the sequence you want to use for data entry. Database design, for example, often displays the key fields in a left-to-right sequence, starting with the first field. This sequence may not be the best sequence for entering data. You can change the order of the fields displayed by dragging and dropping the field name buttons of linked or imported tables to new locations, using the method described in Chapter 4, "Working with Access Databases and Tables."
◂◂See Rearranging the Sequence of Fields in a Table
Adding Fields to Tables with Imported Data
You can add fields to tables that use imported data. During the development of your database application, you may need to repeatedly import data into your table. Therefore, you should append new fields to the end of the field list in Design view rather than insert them between existing fields. The imported data fills the fields in the sequence of the rows in the field list, and the added fields contain null values. You then can rearrange the display position of the new fields in Datasheet view, as described in the preceding section.
Changing Field Data Types of Imported Files
Access converts all numeric data within imported files to the Double data type. The following are recommendations for field data type changes for data imported from any type of source file:
Use Integer or Long Integer field data types for numeric fields that do not require decimal values. Your database files consume less disk space, and your applications run faster.
Change the data type of values that represent money to the data type, Currency, to eliminate rounding errors.
Assign Field Length property values to text-type fields. You should assign Field Length values no greater than the longest text entry you expect to make. If you assign a Field Length value less than the number of characters in the field, characters in positions beyond the Field Length value are lost irretrievably.
Because Paradox versions prior to Paradox 4.x do not provide a memo field data type, you often can find comments in large, fixed-width alphanumeric fields. If you import Paradox files that contain fields of comments, you should convert the fields to memo fields unless you plan to export the data back to a Paradox 3.x file. (You cannot export a table that contains a memo-type field to Paradox 3.x files, although you can export tables with memo fields to Paradox 4.x and Paradox 5 for Windows.)
Adding or Modifying Other Field Properties
You can change the following properties of linked and imported tables:
| Property | Description |
|---|---|
| Caption | Use to change the caption of the field name buttons when using linked files. You can assign any FieldName property to imported tables. |
| Decimal Places | Use to specify the number of decimal places to be displayed for numeric values other than Byte, Integer, and Long Integer. |
| Default Value | Default values are substituted for data elements missing in the imported or linked file. The default values, however, do not replace zeroes and blank strings that represent "no entry" in the fields of most PC database files. |
| Format | Use to create custom formats to display the data in the most readable form. |
| Validation Rule | Validation rules do not affect importing data; they affect only editing the data or appending new records in Access. You cannot use a validation rule to filter imported records. Filtering, in this case, means importing only records that meet the validation rule. |
| Validation Text | Prompts created from validation text assist data-entry operators when they begin using a new table. |
Deleting Redundant Fields in Imported Tables
The purpose of a relational database is to eliminate redundant data in tables. If you design a database that doesn't use data imported or linked from existing databases, you can eliminate data redundancy by following the normalization rules of relational database design described in Chapter 22, "Exploring Relational Database Design and Implementation."
When you link files, however, you are at the mercy of the database designer who originally created the files. Any redundant data the linked file contains is likely to remain a permanent characteristic of the file. Existing database structures are substantially inert; developers of applications that work usually are reluctant to make changes. Changes may introduce new problems that can later return to haunt the developers.
If you import table data from a file or append records by pasting from the Clipboard, you can eliminate data redundancy by restructuring the resulting tables. An Invoice table, for example, may contain one record for each invoice. When a customer makes more than one purchase, the customer information is duplicated in the invoice file for each purchase. In this situation, you need to create separate tables for customers and invoices. The Customer table should contain one record per customer, with name and address information. The Invoice table should contain one record per customer with date, amount, and other information specific to the transaction.
Removing redundancy from existing tables by dividing them into two or more related tables is not a simple task, even if you use the Table Analyzer Wizard (accessed by choosing Tools, Analyze, Table). You must manually (or with the help of the Table Analyzer) create a query that establishes the relationship between two tables that contain one record for each invoice based on a new primary-key field. Then, you delete the duplicate records from the Customer table. Fortunately, the Import Errors table can do much of the work; import the data into a new table with primary-key fields or a no-duplicate index on the customer name and address.
Another type of redundancy is the presence of fields in tables that have values calculated from other fields, either within the table or in the fields of related tables. Any field with values derived from combinations of values within the table or accessible in any other related table within the database or linked to the database contains redundant data. In this case, just remove the redundant field and perform the calculation with a query. Do not remove redundant fields, however, until you verify that your calculated values match those in the redundant field to be replaced.
You need to learn about queries and joining tables—the subject of the next four chapters—to know how to eliminate redundancy in imported tables. The point of this discussion is to let you know that you can perform much of the restructuring by using specialized Access operations and to caution you not to try to remove duplicate data from imported tables prematurely.