CSCI A114 / INFO I111
Lab Notes Five

First semester 2000-2001

Project Three: Maintaing a database using the design and update features of Access. Opening the database. Adding, changing, and deleting records in a table. Adding records in a table. Searching for a record. Changing the contents of a record. Switching between views. Filtering records. Deleting records. Changing the structure of a database. Changing the size of a field. Adding a new field. Deleting a field from a table. Updating the restructured database. Resizing columns. Using an update query. Using a delete query to delete a group of records. Creating validation rules. Specifying a required field. Specifying a range. Specifying a default value. Specifying a collection of legal values. Using a format. Saving rules, values, and formats. Updating a table that contains validation rules. Making individual changes to a field. Specifying referential integrity. A3 and THQ3 posted.
In this lab we start Project Three.

Here's the CASE PERSPECTIVE:

Bavant Marine Services now has created and loaded their database. The management and staff have received many benefits from the database, including the ability to ask a variety of questions concerning the data in the database. They now face the task of keeping the database up to date. They must add new records as they take on new marinas and technicians. They must make changes to existing records to reflect additional billings, payments, changes of address, and so on.

Bavant Marine Services also found that it needed to change the structure of the database in two specific ways:

In addition, they wish to establish rules for the data entered in the database to ensure that users only can enter valid data.

Finally they determined they want to improve the efficiency of certain types of processing, specifically sorting and retrieving data. Your task is to help Bavant Marine Services in all these activities.

Open your Bavant Marine Services database. The following steps use the Marina form you created in Project 1.
Once your database is open, click Forms, then right-click Marina. The shortcut menu displays.
Click Open on the shortcut menu. The form for the Marina table displays.
Click the New Record navigation button at the bottom of the Form view window. The contents of the form are erased in preparation of a new record.
Type the data for the new record. Use the data below. 
Marina Number: PM34
Name:          Peter's Marina 
Address:       453 Wilson 
City:          Torino 
State:         MI
Zip Code:      48268
Warranty:      $0.00
Non-warranty:  $0.00
Tech Number:   49
Press the TAB key after typing the data in each field, except after typing the final field (Tech Number). The record displays.
After typing the value for the final field (Tech Number) press the TAB key. The record now is added to the Marina table and the contents erased.
You have added a record to the Marina table using a form. Let's now search for a record in a table.
Make sure that the form for the Marina table displays. Click the First Record navigation button at the lower-left corner of the Form view to display the first record.
The first record displays in the form. If the Marina Number field currently is not selected, select it by clicking the field name. Point to the Find button on the Form view toolbar.
Click the Find button on the toolbar. The Find and Replace dialog box displays.
Type FM22 in the Find What text box in the Find and Replace dialog box. The Find What text box contains the entry, FM22. Click the Find Next button and then click the Close button.
Access locates the record for marina FM22. After locating the record to be changed, select the field to be changed by clicking the field.
You also can repeatedly press the TAB key. Then make the appropriate changes. Clicking a field automatically produces an insertion point. If you use the TAB key, you will need to press F2 to produce an insertion point.
Perform the following steps to use Form view to change the name of marina FM22 to Fedder's Marina by inserting an apostrophe (') and the letter, s, after Fedder. Sufficient room exists in the field to make this change. Position the mouse pointer in the Name field text box for marina FM22 after the word, Fedder.
The mouse pointer shape is an I-beam. Click to produce an insertion point and then type 's to correct the name.
The name is now Fedder's marina. Point to the View button arrow on the Form View toolbar.
Click the View button arrow on the toolbar. The View list displays.
Point to the Datasheet View in the list. Click Datasheet View, and then maximize the window containing the datasheet. The datasheet displays. The position in the table is maintained. The current record selector points to marina FM22, which is the marina that displayed on the screen in Form view.
The Name field, the field in which the insertion point is displayed, is selected. The new record for marina PM34 is the last record in the table. When you close the table and open it later, marina PM34 will be in its appropriate location.
If you wanted to return to Form view, you would use the same process. You can use the Find button to locate a record quickly that satisfies a criterion.
All records display, however, not just the records or record that satisfy the criterion. To have only the record or records that satisfy the criterion display, use a filter.
The following steps use filter by selection to display only the record for Fedder's Marina. Make sure the name for marina FM22 (Fedder's Marina) is selected and then point to the Filter By Selection button on the Form View toolbar.
Click the Filter By Selection button on the toolbar. Because there only is one marina in the database with the name Fedder's Marina, only one marina displays.
If you had instead selected the City field on the same record (Burton) before clicking the Filter By Selection button, ... three marinas would display, because there are currently three marinas located in Burton (marinas AD57, BL72, and FM22).
In order to have all records once again display, remove the filter by clicking the Remove Filter button on the Form View toolbar, as in the following steps. First, point to the Remove Filter button on the Form View toolbar.
The Remove Filter button is recessed because there currently is a filter applied to the table. Click the Remove Filter button on the toolbar amd all records once again will display.
When records are no longer needed we should delete them. For example, if marina JB92 is no longer in business and already has settled its final bill, that marina's record should be deleted.
To delete a record, first locate it and then press the DELETE key. Complete the following steps to delete marina JB92.
Click the record selector to select the record, and then press the DELETE key to delete the record. The Microsoft Access dialog box displays. The message indicates that one record will be deleted.
Click the Yes button to complete the deletion. Close the window containing the table by clicking its Close button on the title bar.
The record is deleted and the Marina table window closes. This completes the "adding, changing, and deleting records in a table" section.
We're now ready for the "changing the structure of a database" section. We first need some motivation.
When you initially create a table you define its structure. A variety of reasons exist why the structure of a table might need to change.
To start making any changes just click on the Design View on the shortcut menu. Let's change the size of the Name field from 20 to 25 to accommodate the change of name from Alan's Docks to Alan's Docks Boat Works.
With the Database window open, click Tables, on the Objects bar, and then right-click Marina. The shortcut menu displays. Click Design View on the shortcut menu.
The Marina: Table window displays. Point to the row selector for the Name field, then click on it: the Name field is selected.
Press F6 to select the field size, and press F6 again. The size is changed.
We're ready for the second structural change we wanted to make to the table. First, some motivation: the management of BMS decided they needed to categorize the marinas by the type of storage they offer.
Some marinas only support storing boats in the water. Others offer only rack storage where each boat is stored in a rack. Still others offer both in-water and rack storage. To be able to store the marina type, the following steps add a new field, called Marina Type, to the table.
The possible entries in this field are BIR (both in-water and rack storage), IWO (in-water only), and RSO (rack storage only). The new field will follow the zip code in the list of fields; that is, it will be the seventh field in the restructured table, with the fields starting with Warranty being shifted one position to the right.
Complete the following steps to add the field. Point to the row selector for the Warranty field.
Click the row selector for the Warranty field and then press the INSERT key to insert a blank row. A blank row displays in the position for the new field.
Click the Field Name column for the new field. Type Marina Type as the field name and then press the TAB key. Select the Text data type by pressing the TAB key.
Type Marina Type (BIR, IWO, or RSO) as the description. Press F6 to move to the Field Size text box, type 3 (the size of the Marina Type field), and then press F6 again.
The entries for the new field are complete. Close the Marina: Table window by clicking its Close button on the title bar.
The Microsoft Access dialog box displays asking whether or not to save changes to the table's design. Click the Yes button to save the changes.
How do you delete a field from a table? You first open the table in Design view, and then click the row selector for the field to select it.
You then would press the DELETE key to delete the field. Access would request confirmation that you do indeed wish to delete the field.
If you click the Yes button and then save your changes, the field will be removed from the table. Changes to the structure are available immediately.
The Name field is longer, although it does not display that way on the screen, and the new Marina Type field is included. To make a change to a single field perform the following steps.
With the Bavant Marine Services: Database window open, right-click Marina. Click Open on the shortcut menu. The datasheet displays.
Position the I-beam mouse pointer to the right of the second "s" in "Alan's Docks". Click immediately to the right of the second "s" in "Alan's Docks", press the spacebar, and then type Boat Works to change the name.
The name is changed from "Alan's Docks" to "Alan's Docks Boat Works". The following steps illustrate the process for resizing the Name column to the size that best fits the data.
Point to the right boundary of the field selector for the Name field. Double-click the right boundary of the field selector for the Name field: the Name column will be resized.
Use the same technique to resize the remaining columns. Close the Marina: Table window by clicking its Close button on the title bar.
The Microsoft Access dialog box displays asking whether or not to save changes to the table's layout. Click the Yes button. The changes are saved. The next time the datasheet displays, the columns will have the new widths.
The Marina Type field is blank on every record. At Bavant most marinas are type BIR.
Initially, you can set all the values to BIR. To accomplish this quickly and easily, you use a special type of query called an update query.
The process for creating an update query begins the same as the process for creating the queries in Project 2. After selecting the table for the query, right-click any open area of the upper pane, click Query Type on the shortcut menu,
... and then click Update Query on the menu of available query types. An extra row, Update To:, displays in the design grid.
Use this additional row to indicate the way the data will be updated. If a criterion is entered, then only those records that satisfy the criterion will be updated.
Perform the following steps to change the value in the Marina Type field to BIR for all the records. Because all records are to be updated, no criterion will be entered.
With the Marina table selected, click the New Object: AutoForm button arrow on the Database window toolbar. The New Object: AutoForm list displays. Click Query in the list. The New Query dialog box displays. Design View is selected.
Click the OK button, and then be sure the Query1: Select Query window is maximized. Resize the upper and lower panes of the window as well as the Marina field list box so all fields in the Marina table field list display. Right-click the upper pane and point to Query Type on the shortcut menu. The shortcut menu displays. The Query Type submenu displays, showing available query types.
Click Update Query on the submenu, double-click the Marina Type field in the Marina table field list to select the field, ... click the Update To text box in the first column of the design grid, and then type BIR as the new value.
The Marina Type field is selected. In an Update Query, the Update To row displays in the design grid. The value to which the field is to be changed is is entered as BIR. Because no criteria are entered, the Marina type value on every row will be changed to BIR. Click the Run button on the Query Design toolbar.
The Microsoft Access dialog box displays. The message indicates the number of rows that will be updated by the query. Click the Yes button.
In some cases you may need to delete several records at a time. You can do that with a delete query, which is a query that will delete all the records satisfying the criteria entered in the query.
Perform the following steps to use a delete query to delete all marinas whose zip code is 48121 (soon to be serviced by another firm). Click Edit on the menu bar and then click Clear Grid to clear the grid.
Right click the upper pane, and then point to Query Type on the shortcut menu. The Query Type submenu displays the available query types.
Click Delete Query on the submenu, double-click the Zip Code field in the Marina table field list to select the field, ... and then click the Criteria entry. Type 48121 as the criterion. The criterion is entered in the Zip Code field.
In a Delete Query, the Delete row displays in the design grid. Click the Run button on the Query Design toolbar to run the query.
The Microsoft Access dialog box displays. The message indicates the number of rows (records) to be deleted. Click the Yes button.
Close the Query window by clicking its Close button on the title bar. Do not save the query. The two marinas with zip code 48121 have been removed from the table.
You now have created, loaded queried, and updated a database. Nothing you have done so far, however, ensures that users enter only valid data.
To do so, you create validation rules. Validation rules can indicate:
  • a required field
  • a range of values
  • a default value
  • a collection of acceptable values
To specify that a field is to be required, change the value in the Required text box from No to Yes. The following steps specify that the Name field is to be a required field.
With the Database window open and the Tables object selected, right-click Marina. Click Design View on the shortcut menu, and then select the Name field by clicking its row selector.
Point to the Required text box. The Marina: Table window displays. The Name field is selected.
Click the Required text box in the Field Properties pane, and then click the down arrow that displays. Click Yes in the list. The value in the Required text box changes to Yes. It now is required that the user enters data into the Name field when adding a record.
The following step specifies that entries in the Warranty field must be between $0.00 and $10,000.00. Select the Warranty field by clicking its row selector. Click the Validation rule text box in the Field Properties pane to produce an insertion point,
... and then type 
>=0 and <=10000
as the rule. 		Click the Validation text text box in the Field Properties pane to produce an insertion point.
Then type as the text. 
Must be between $0.00 and $10,000.00
You must type all the text, including the dollar signs in this text box. The validation rule and text are entered now.
In the Validation Rule text box, Access automatically changed the lowercase letter, a, to uppercase in the word, and. Users now will be prohibited from entering a warranty amount that either is less than $0.00 or greater than $10,000.00
... when they add records or change the value in the Warranty field. To specify a default value, enter the value in the Default Value text box in the Field Properties pane.
The following step specifies BIR as the default value for the Marina Type field. This simply means that if users do not enter a marina type, the type will be BIR.
Select the Marina Type field by clicking its row selector. Click the Default Value text box in the Field Properties pane and then type =BIR as the value. The Marina Type field is selected. The default value is entered in the Default Value text box.
From this point on, if users do not make an entry in the Marina Type field when adding records, Access will set the value to BIR. Perform the step below to specify that the legal values for the Marina Type field are BIR, IWO, and RSO, only.
Make sure that the Marina Type field is selected. Click the Validation Rule text box in the Field Properties pane, and then type
=BIR or =IWO or =RSO
as the validation rule. 		Click the Validation Text text box in the Field Properties.
The type: as the validation text. 
Must be BIR, IWO, or RSO
The validation rule and text have been entered. In the Validation Rule text box, Access automatically inserted ... quotation marks around the BIR, IWO, and RSO values and changed the lowercase letter, o, to uppercase in the word, or.
Users now will be allowed to enter only BIR, IWO, or RSO in the Marina Type field when they add records or make changes to this field. To affect the way data is displayed in a field, you can use a format. To use a format, you enter a special symbol, called a format symbol, in the field's Format text box in theField Properties pane.
Here's an example: the format symbol used in the example is >, which causes Access to display lowercase letters automatically as uppercase. The format symbol < would cause Access to display uppercase letters automatically as lowercase.
Select the Marina Number fierld by clicking its row selector. Click the Format text box in the Field Properties pane and then type >.
From this point on, any lowercase letters will be displayed automatically as uppercase ... when users add records or change the value in the Marina Number field.
To save the validation rules, default values, and formats, perform the following steps. Click the Close button on the Marina: Table window title bar to close the window.
The Microsoft Access dialog box displays, asking if you want to save your changes. Click the Yes button to save the changes.
The Microsoft Access dialog box displays. This message asks if you want the new rules applied to current records.
If this were a database used to run a business or to solve some other critical need, you would click Yes. You would want to make sure that the data already in the database does not violate the rules.
Click the No button. The changes are incorporated.
When updating a table that contains validation rules, Access provides assistance in making sure the data entered is valid and formatted correctly. Take care when creating validation rules, as you may come to an impasser where you neither can leave the field nor close the table because you have entered data into a field that violates the validation rule.
It may be that you cannot remember the validation rule you created, ... or it was created incorrectly.
First try to type an acceptable entry. If this doesn't work, repeatedly press the BACKSPACE key to erase the contents of the field, and then try to leave the field.
If you are unsuccessful using this procedure, press the ESC key until the record is removed from the screen. The record will not be added to the database.
Earlier, you changed all the entries in the Marina Type field to BIR. You now have created a rule that will ensure that only legitimate values (BIR, IWO, or RSO) can be entered in the field.
To make a change, click the field to be changed to produce an insertion point, use BACKSPACE or DELETE key to delete the current entry, and then type the new entry. Complete the following steps to change the Marina Type value on the second and eighth records to IWO and on the fifth record to RSO. First, make sure the Marina table displays in Datasheet view.
Click to the right of the BIR entry in the Marina Type field on the second record to produce an insertion point. Press the BACKSPACE key three times to delete BIR and then type IWO as the new value.
In a similar fashion, change the BIR entry on the fifth record to RSO and on the eighth record to IWO. Close the Marina: Table window by clicking its Close button on the title bar. The Marina Type field changes are now complete.
The property that ensures that the value in a foreign key must match that of another table's primary key is called referential integrity. A foreign key is a field in one table whose values are required to match the primary key of another table.
In the Marina table the Tech Number field is a foreign key that must match the primary key of the Technician table. A Marina whose technician number is 02, for example, should not be stored because technician 02 does not exist.

In Access, to specify referential integrity, you must define a relationship between the tables by using the Relationships command. Access then prohibits any updates to the database that would violate the referential integrity:

The type of relationship between two tables specified by the Relationships command is referred to as a one-to-many relationship. This means that one record in the first table is related to (matches) many records in the second table, but each record in the second table is related to only one record in the first.

In the BMS database, for example, a one-to-many relationship exists between the Technician table and the Marina table: one technician is associated with many marinas, but each marina is associated with only one technician.

The following steps use the Relationships command to specify referential integrity by specifying a relationship between the Technician and Marina tables.

Close any open datasheet by clicking its Close button on the title bar. Then point to the Relationships button on the Database window toolbar.

Click the Relationships button on the toolbar. The Show Table dialog box displays.
Click the Technician table, click the Add button, click the Marina table, click the Add button again, and then click the Close button. Field list text boxes for the Technician and Marina tables display. Resize the field list boxes that display so all fields are visible.
Point to the Tech Number field in the Technician table field list. Drag the Tech Number field inthe Technician table field list to the Tech Number field in the Marina table field list.
The Edit Relationships dialog box displays. The correct fields (the Tech Number fields) have been identified as the matching fields.
Click Enforce Referential Integrity. With Enforce Referential Integrity selected Access will reject any update that would violate referential integrity.
Click the Create button. Access creates the relationship and displays it visually with the relationship line joining the two Tech Number fields.
The number 1 at the top of the relationship line close to the Tech Number field in the Technician table indicates that the Technician table is the one part of the relationship. The infinity symbol at the other end of the relationship line indicates that the Marina table is the many part of the relationship.
Close the Relationships window by clicking its Close button on the title bar. Click the Yes button to save your work.

Last updated: September 12, 2000 by Adrian German for A114/I111