Copying Table Structure

Databases often consist of several tables with the same general characteristics. For example, the database you created in Chapter 8 for a roofing company might include one table with information about employees and another with information about customers. You don t want to spend a lot of time re-creating the structure of the Employees table for the Customers table, and with Access you don t have to. You can copy the structure of an existing table to create a new one.

To give you a new table to work on in the following sections, here s how to copy the Employees table structure to create the Customers table:

1. If necessary, click the Start button, click My Documents , and then double-click Roofs to start Access and open the Roofs database.

2. With Tables selected on the Objects bar and Employees selected in the Database window, click the Copy button on the Standard toolbar.

3. Click the Paste button on the Standard toolbar to display the Paste Table As dialog box.

4. In the Table Name text box, type Customers , select the Structure Only option, and click OK .

   Access adds the Customers table to the list in the Database window.

5. With Customers selected, click the Design button on the window s toolbar to display the new table in Design view.

6. Change the EmployeeID field name to CustomerID .

7. In the Description column, change the word employee to customer , and then change the Caption property to Cust# .

8. Select the SocialSecurityNumber field by clicking its row selector, and then press Delete to delete it. Also delete the FirstName and DateHired fields.

9. Change the LastName field to Name , change the field s size to 30 , and then change the Caption property to Name .

10. Change the HomePhone field name to Phone .

    The table structure now looks like the one shown in this graphic:

    

11. Click the View button to switch to Datasheet view, and when prompted to save the table, click Yes .

12. Close the Customers table.

Copy with caution

Copying the structure of simple tables is relatively easy, but as you begin to create more complex tables, you ll need to consider carefully such characteristics as primary keys and validation rules, as well as the applicability of other Access table features such as referential integrity, which we discuss later in the chapter.