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 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. Then, from the New Table dialog, you can click Design View and click OK.

The quickest way to create a table in Design View is to double-click the Create Table In Design View link from the Database Window.

1. Start Microsoft Access and, to create a new database, on the right side, click Create a New File
2. Under New, click Blank Database and set the name to Clarksville Ice Scream2
3. Click Create to create the new database
4. On the Database Window, if necessary, click the Tables button
   From the Database Window toolbar, click the New button to create a new table
    
   
5. On the New Table dialog box, click Design View and click OK

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 most left gray button allows you to select everything on the table while in Design View. To select all the fields, you can click that button:

The Filed 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.

1. While in Design View, click the first empty box under Field Name, type Employee Number
2. Press Tab three times key and type First Name
3. Press the down arrow key and type Last Name
4. Press the down arrow key and type Email Address
5. Press the down arrow key and type Hourly Salary
6. Press the down arrow key and type Marital Status
    
   
7. To save the table, on the Table Design toolbar, click the Save button
8. Set the name of the table to Employees and click OK
9. You will receive a long message box about a lack of a primary key (we are not there yet). Click No and close the table

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 with 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.

1. Open the Altair Real Estate1 database
2. In the Database window, if necessary, click the Tables button
3. Double-click the HomeInspectors1 table to open it
4. To switch the table to Design View, right-click its title bar and click Table Design
5. Double-click StateOrProvince to select it and press End
6. Press Backspace a few times to remove OrProvince
7. Double-click Postal to select it and type ZIP to display ZIP Code
8. Double-click Region to select it and type Licensed In
9. In LastName, click between t and N. Press and hold Backspace to delete Last, then type Full to get FullName
10. Save the table and close it

In the Design View of the 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.

• 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, and 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

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

1. The Altair Real Estate1 database should still be opened.
   To open the HomeInspectors2 table in Design View, in the Tables section of the Database window, right-click the HomeInspectors2 table and click Design View
2. While in the Design View of the table, click the gray box on the left of LicenseNumber to select it
    
   
3. Click the same box again and hold the mouse down. Then drag it up until the guiding horizontal line is positioned above FullName
    
   
4. Release the mouse
5. To select various fields, position the mouse pointer to the left gray box of the EmailAddress field until the mouse pointer changes into a right pointing arrow:
    
   
6. Click and hold the mouse down. While holding, drag down to BillingRate 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 down until the guiding horizontal line is positioned under ZIP Code
    
   
9. Then release the mouse
10. Save and close the table

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 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) and select the desired field. Then click OK

1. The Altair Real Estate1 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 OfficeLocation
3. Type Observations and press Enter
4. To insert a new field before LastName, right-click LastName and click Insert Rows
5. Type MiddleName and press Tab
6. To insert an already configured field, right-click FirstName and click Build…
7. In the Field Builder, click the Business radio button if necessary. In the Sample Tables list, click Employees
8. In the Sample fields list, scroll down and click DateHired
    
   
9. Click OK
10. Save the table

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.

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

We previously learned that fields could be easily added to a query by using the Query Wizard. Fields can also be added while designing a query. To proceed with this approach, the query should be displayed in Design View. To display a query in Design View, if you are using Microsoft Access 2000, from the Queries section of the Database Window, you can double-click Create Query in Design View. Alternatively, do one of the following:

• On the Queries section of the Database Window, you can click the New button
• On the main menu, you can click Insert -> Query
• On the Database toolbar, you can click the arrow of the New Object button and select Query 

Any of these techniques would call the New Query dialog box where Design View is selected by default. Otherwise you can select Design View and click OK.

To start a fresh query, the database needs to know where data would come from. Therefore, the Design View displays a list of already existing tables and query so you can choose which one(s) would provide the needed information:

A simple query can have its data originate from a single table. Therefore, to choose the table that holds the information needed for this query, you can click that table and click Add. You can also double-click it. A simple query can also be built from an existing query. To use such a query, in the Database window, click the Queries button and select the query you want. You can also create a query whose data would originate from more than one table. In this case, the tables must have been previously joined. To select the tables, from the Tables property page of the Show Table dialog box, you can double-click each on the tables you need. More advanced queries can be built from more than two queries, a table and a query, or a combination of tables and queries.

After selecting the table, tables, query, or queries, you can click the Close button of the Show Tables dialog box. If the Show Tables dialog box is closed or for any reason you want to display it, on the menu bar, you can click Query -> Show Table...

1. Open the Bethesda Car Rental1 database and, in the Database Window, click the Tables button
2. Click the Cars table to select it. Then, on the toolbar of the Database window, click the Open button
3. Scroll to the right of the table to review the columns
4. Close the Cars table
5. On the Database Window, click Queries and click the New button
    
   
6. In the New Query dialog box, click Design View and click OK. Notice that you are presented with a list of existing tables
7. On the Show Table dialog box, click Cars
8. Click Add and click

The Select Query window presents another classic window. Its title bar displays its system button on the left section. This can be used to minimize, maximize, restore, move, resize, or close the window. Like all Microsoft Access window objects, the title bar displays a special menu when right-clicked:

The right section of the title bar displays the classic system buttons of a regular window.

In the top wide area of the Select Query window, the query displays an object (table(s), query (queries)) or a group of objects that was selected to create the query. The lower portion of the query displays boxes that would be used to perform various operations related to the query. The upper and the lower sections of the Select Query window are separated by a splitter bar that you can use to resize them by dragging the splitter bar up or down:

To create the fields for a query, you use the table, query, tables, or queries displayed in the upper section of the window. Once you have decided on the originating object or objects, you can select which fields are relevant for your query:

• To select one field from the list, just click it
• To select many fields on the same range, you can click one of them, press and hold Shift. Then click one field on the other end of the desired list
• To select fields at random, click one of the desired fields, press and hold Ctrl; then click each one of the desired fields
• To select all fields, you can either double-click the title bar of the list or click the * line on the list of fields.

Once you have made your selection on the list, you can drag it and drop it on the query. Instead of dragging a field or all fields, you can either double-click a field to add it to the query, or double-click the * line to add all fields to the query.

Practical Learning: Select Fields to Build a Query

1. From the list of fields, click and drag Make, then drop it anywhere on the first empty field on the lower section of the view:
    
   
2. On the list, scroll down and click AMFM
3. Press and hold Shift, then click CDPlayer and release Shift. Notice that three fields have been selected
4. Drag the group of items and drop it on the empty column right to Make
    
   
5. Notice all the selected fields have been added to the query
    
   
6. To view the query, on the Query Design toolbar, click the View button
7. After viewing the query, to save it, on the Query Datasheet toolbar, click the Save button
8. Type Cars Audio Options as the name of the query and press Enter
9. After viewing the query, to close it, click its Close button

Sometimes, the idea of using a query is to test data or verify a condition. Therefore, a query, as compared to a table, could provide just a temporary means of studying information on your database. When performing such assignments or when testing values before isolating an appropriate list, you can add, insert, delete, replace or move fields at will. We have already covered different techniques of adding or inserting fields:

• To select a field in the lower section of the view, click the tinny gray bar of the column header. The whole column will be selected
• To select a range of columns, click the column header of one at one end, press and hold Shift, then click the column header at the other end

As seen above, a query is built by selecting columns from the originating list and adding them. If you do not need a column anymore on a query, which happens regularly during data analysis, you can either delete it or replace it with another column:

• To delete a column, once it is selected, you can press Delete
• To delete a group of columns, select them and press Delete
• To replace a column, click the arrow on the combo box that displays its name and select a different field from the list

Columns on a query are positioned incrementally as they are added to its list. If you do not like the arrangement, you can move them and apply any sequence of your choice. Before moving a column or a group of column, you must first select it:

• To move a field, click its column header once. Click it again and hold your mouse. Then drag in the direction on your choice
• To move a group of columns, first select the group and then proceed as if it were one column

1. The Bethesda Car Rental1 database should still be opened
   In the Database Window, click the Tables button and click Cars to select it
2. On the menu bar, click Insert -> Query
3. In the New Query dialog box, double-click Design View
   Notice that the Cars table is already selected as the originating object
4. In the list of fields, double-click Make, Model, NbrOfDoors, AMFM, Cassette, and CDPlayer
5. To view the query, on the Query Design toolbar, click the View button
6. After viewing the query, to switch back to Design View, on the Query Datasheet toolbar, click the View button 
7. To replace a field, in the lower section of the Design View, click NbrOfDoors and notice that an arrow of a combo box appears
8. Click the arrow of the NbrOfDoors combo box and select CarYear
    
   
9. Scroll to the right on the lower section of the view to display the first empty field
10. To add a new field, click CDPlayer and press Tab. Notice the combo box
11. Press and hold Alt, then press the down arrow key and release Alt. This displays the list of the combo box
12. Press the down arrow key a few times until Picture is selected, then press Enter
13. Scroll back to the left
14. To insert a field, drag TagNumber from the Cars list and drop it on top of AMFM
15. Notice that the newly inserted field has been added to the left of the field it was dropped on
    (In the same way, you can select various fields and decide to insert them to the left of a field of your choice)
16. Scroll to the right side of the lower section of the view until you can see Picture.
    To delete a field, in the lower section of the view, position the mouse on the tinny horizontal bar above Picture until the mouse turns into a down pointing arrow:
     
    
17. Click. Notice that the whole column is selected
18. Press Delete. Notice that the field is removed from the query
19. To view the query, click the View button
20. To switch the query back to Design View, on the main menu, click View -> Design View
21. Scroll back to the left of the lower view and make sure you can see the TagNumber and the Make columns
    In the lower section of the view, click the bar on top of TagNumber and release the mouse
22. Click the TagNumber header bar again and hold your mouse down
23. Notice a vertical line that guides you. Drag left until the vertical guiding line gets between Make and Model:
     
    
24. Release the mouse 
25. To view the query, on the Query Design toolbar, click the View button
26. Switch the query back to Design View
27. Scroll the lower section right to see CDPlayer
28. Click and hold your mouse on the bar on top of AMFM. Then drag right to CDPlayer to select the AMFM, the Cassette, and the CDPlayer columns. Then release the mouse 
29. Click and hold your mouse again on the bar on top of AMFM
30. Drag left until the vertical line is between the Model and the CarYear columns
31. Then release the mouse
32. To run the query, right-click its title bar and click Datasheet View 
33. To close the query, double-click its system icon
34. A message asks you whether you want to save the query, click No (this was a test; it was just a test…)

1. Open the Yugo National Bank database and open the Customers table in Design View. Insert a new field between CustomerID and AccountNumber. Name it DateCreated and close the table
2. Use the Table Wizard to create a new table based on the Accounts sample table of the Personal category and including only the AccountTypeID field. Rename AccountTypeID to TransactionTypeID. Save the table as TransactionTypes and open it in Design View. Under the existing field, add another field named TransactionType. Using the Field Builder, add a field based on the Notes sample. Rename it Description. Save the table and perform data entries as follows:
    

   TransactionTypeID	TransactionType	Description
   1	Deposit
   2	Withdrawal
   3	Fund Transfer
   4	Money Order
   5	Service Charge

   Close the table

1. Open the Watts A Loan database. In the Datasheet View of the Customers table, insert a new field between CustomerID and FirstName. Name it AccountNumber
2. Using the Design View of the Customers table, use the Field Builder dialog box to add a new field at the end of the table (under Notes) and base the field on the OrderDate field from the Orders sample table of the Business category. Rename the field as AccountDate then move it up to position it between CustomerID and AccountNumber. Save and close the table
3. Open the Customers table in Design View and change the names of the following fields:
    

   Field Name	New Name
   AccountName	CustomerName
   StateOrProvince	State
   PostlCode	ZIPCode
   WorkExtension	Extension
   DateUpdated	LastUpdate

   Save and close the table

4. Open the Employees table in Design View and change the names of the following fields:
    

   Field Name	New Name
   DepartmentName	Department
   EmailName	Username
   StateOrProvince	State
   PostalCode	ZIPCode
   Salary	HourlySalary

   Save and close the table