Managing Data Fields

Table Design and Fields

Introduction

So far, to get acquainted with database fields, we have used two techniques of creating a table and some techniques of adding fields to a form. One of the techniques we used, the Datasheet View, provides a very simplistic way. The Datasheet View is mainly used to perform data entry or to test some fields. It has so many limitations that you will hardly use it. For example, you cannot control or customize the behavior of fields if you create the table in Datasheet View. The other technique we used, the Table Wizard, allows you to use some already created and customized fields. For one thing, you would not know what exactly those fields are configured to do and whether they can control data entered in them. For another thing, if you do not like the way they behave, you would have to modify the existing configuration that was already done. This is why you should know how to design your tables.

Table Design consists of creating a list of columns, specifying the type of information they can receive, and controlling their behavior. To design a table, you should display it in Design View, which is a display other than Datasheet View. To create a new table in Design View, on the menu bar, you can click Insert -> Table. You can also click the arrow of the New Object button 

on the Database toolbar and select Table. As another alternative, you can click the New button on the Database Window's toolbar while in the Tables section. Any of these actions would call the New Table dialog box. In the New Table dialog box, you can click Design View and click OK.

The quickest way to create a table in Design View when using Microsoft Access 2000 and later versions is to double-click the Create Table In Design View link from the Database Window.

Practical Learning:  Introducing Table Design

1. Open the Clarksville Ice Cream database that you started
2. In the Database Window, if necessary, click the Tables button
   From the Database Window toolbar, click the New button to create a new table
    
   
3. On the New Table dialog box, click Design View and click OK

The Table in Design view

The title bar of a table in Design View presents the table’s icon on the left section. Like most other windows, the table's icon holds the table's system menu. This system icon can also be double-clicked to close the table. The long empty section of the title bar is different from a classic window. Instead of being used to maximize, minimize, move, or close the table, it provides features adapted for the database object it is. For example, at a minimum, it can be used to switch views. To use its function, you can right-click it. This would present a menu you can select from:

The right section of the title bar presents the system buttons of a regular window. They can be used to minimize, maximize, restore, or close the table.

On the left side of the view, the row headers allow you to select a row completely. The top left gray button allows you to select everything on the table while in Design View:

The Field Name column is used to type a name for each field. The rules we used when in Datasheet View apply here. A field can have any name but keep a name with one word and no space.

Practical Learning:  Creating Columns in Design New

1. While in Design View, click the first empty box under Field Name, type EmployeeID
2. Press the Tab key three times and type DateHired 
3. Press the down arrow key and type EmployeeNumber 
4. Press the down arrow key and type FirstName 
5. Press the down arrow key and type MiddleInitial 
6. Press the down arrow key and type LastName 
7. Press the down arrow key and type WorkPhone 
8. Press the down arrow key and type Extension 
9. Press the down arrow key and type EmailAddress 
10. Press the down arrow key and type Address 
11. Press the down arrow key and type City 
12. Press the down arrow key and type State 
13. Press the down arrow key and type ZIPCode 
14. Press the down arrow key and type Country 
15. Press the down arrow key and type Salary 
16. Press the down arrow key and type MaritalStatus 
17. Press the down arrow key, type Notes and press Tab
18. To save the table, on the Table Design toolbar, click the Save button
19. Change the name of the table to Employees and click OK
     

    

20. You will receive a long message box about a lack of a primary key (we are not there yet). Click No
21. Close the table

Field Renaming

One of the jobs involved with database design and maintenance is to review fields and make sure they are explicit enough for the user. There are three issues related to a field's name. Some fields, as you will find out, are involved in relationships, and you will have to be careful when deciding to change their name. The other issues are related to a field's actual name and its caption.

A field's name is mainly made of two parts from the developer's standpoint: the actual name and the caption. A field's name is stored as part of the table and is involved with any business dealing the table has to cope with. The caption simply guides the user in identifying the use of a particular field. These two items are not strictly related. For example, you could have a field whose name is SocSecNbr while its caption displays Nat Number From Gvt and this will not affect the functionality of the table. Overall, as a developer, you should pay attention to your fields names because these are the names you will refer to when performing calculations and other programming issues.

To rename a field in Datasheet View, use any of the techniques we learned to change a field's name. After you have typed the name, the new name will replace the old one. To rename a field in Design View, click it and type the new name.

Practical Learning: Renaming a Field

1. Open the Rockville Techno database. In the Database window, if necessary, click the Tables button
2. Double-click the Employees table to open it
3. Complete the records as follows:
    

   First Name	Last Name	Title	Extension	Billing Rate
   Christine	Barley	Administrative Assistant	106	16.82
   Joseph	Kumar	Network Administrator	105	20.65
   Joanne	Herbie	Accountant	108	22.48

4. To switch the table to Design View, right-click its title bar and click Table Design
5. In StateOrProvince, click between the most left e and O. Then press and hold Delete until the Field Name displays State
6. Press the down arrow key to select PostalCode and type ZIPCode
7. Double-click EmailName, type EmailAddress and press Tab
8. Save the table and close it

Field Selection in Table Design View

In the Design View of a table, there are various types of operations you can perform for database maintenance. Operations include naming or renaming fields, moving or deleting fields, and changing various other properties. In order to perform some of the operations on a field or a group of fields, you must first select one.

• To select one field, you can position the mouse on the gray box to its left and click.
• To select more than one column, you can click and hold your mouse on one of the left gray boxes, then drag up or down to cover the other desired field names. When all desired field names are highlighted, release the mouse.
• To select more than one column, you can also click one column that will be at one end, press and hold Shift, then click the column that will be at the other end.
• To select columns at random, click the left gray box of one of them, press and hold Ctrl, then click the gray box of each of the desired boxes.

Column Moving in Table Design View

The columns of a table are displayed in the sequence in which they were created. We learned earlier how to change this sequence in Datasheet View. You can also change the sequence of fields in the Design View of a table.

To move a column or a group of columns, after making the selection, click the gray box of the field name or one of the gray boxes of one of the selected fields and hold the mouse down. Then drag up or down. A horizontal line will guide you. Once it gets to the desired location, you can release the mouse.

Practical Learning: Maintaining Fields in Table Design View

1. The Rockville Techno database should still be opened.
   To open the Employees table in Design View, in the Tables section of the Database window, right-click the Employees table and click Design
2. While in the Design View of the table, click the gray box on the left of Extension to select it
    
   
3. Click the same box again and hold the mouse down. Then drag it down until the guiding horizontal line is positioned under WorkPhone
    
   
4. Release the mouse
5. To select various fields, position the mouse pointer to the left gray box of the HomePhone field until the mouse pointer changes into a right pointing arrow:
    
   
6. Click and hold the mouse down. While holding, drag down to Extension to select those 3 fields, and release the mouse
7. To move the selected fields, click and hold the mouse on one of the black selected boxes on the left of the field names
8. Drag up until the guiding horizontal line is positioned on top of Title:
    
   
9. Then release the mouse
10. Save and close the table

Field Insertion

When it comes to tables, the best place to add new fields is in Design View because this view provides more flexibility. In Design View, you can insert a new field at any position. You can also add a new field to the end of the table, a feature that is not available in Datasheet View. You can also use one of the same fields as we saw when using the Table Wizard: To insert a new column, you can right-click the field that will succeed it (you can right-click anywhere on the horizontal boxes of the column, that is, on the Field Name, the Data Type, or the Description columns), click Insert Rows, and type a name for the new column. To insert a new column, you can also click anywhere on the column that will succeed it. Then, on the main menu, click Insert -> Rows. Alternatively, when a column has focus, on the Table Design toolbar, you can also click the Insert Rows button. To add a new column at the end of the table, click the first empty field under Field Name and type the desired name. To use one of the fields available from the Table Wizard, right-click either the first empty field under Field Name or right-click the field that will succeed it and click Build… On the Field Builder dialog box, select the category (Business or Personal), select the desired field, and then click OK.

Practical Learning: Inserting New Fields in Table Design View

1. The Rockville Techno database should still be opened.
   To open the Employees table in Design View, in the Database window, click it to select it if necessary. Then, on the Database window, click the Design button
2. To add a new field at the end of the table, click the first empty box under BillingRate. Type Observations and press Enter
3. To insert a new field before LastName, right-click LastName and click Insert Rows
4. Type MiddleName and press Tab
5. To insert an already configured field, right-click EmployeeNumber and click Build…
6. In the Field Builder, click the Business radio button if necessary. In the Sample Tables list, click Employees
7. In the Sample fields list, scroll down and click DateHired
    
   
8. Click OK
9. To add another field, click Address
10. On the main menu, click Insert -> Rows
11. Type WebSite and press Enter
12. Save the table

Field Deletion

When in Design View, you can delete a field you don't need anymore or if it was added by mistake. To delete a column, you can right-click anywhere on its line (on the Field Name, the Data Type, or the Description columns) and click Delete Rows. When a column has focus, you can also click the Delete Rows button on the Table Design toolbar to delete it.

Practical Learning: Deleting a Field in Table Design View

1. To get rid of a column, right-click WebSite and click Delete Rows
2. When asked whether you want to permanently delete the field(s), click Yes
3. Save and close the table