Top of
the Page| Discovering Computers | Windows XP | Word 2002 | PowerPoint 2002 | Excel 2002 | Access 2002 | Site Map |
| Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 |
MS Access 2002: Maintaining a Database Using the Design and Update Features of Access
You can add records to a table by using the datasheet view of the table or by inputting them from a form view. Before you can add records to the table, you need to open it. To open the table, select the table name in the Database Window and then right-click on it to display the shortcut menu. From the shortcut menu, click on the Open option as shown in Figure 3-03 in your book.
The table should open in Form View. In this view, you can add a new record by clicking on the New Record button as shown in Figure 3-04 in your book.
When you are in the datasheet view, you just need to go to the end of the table and click in any of the fields to begin the record.
There will be times that you need to find records based on specific values in a field. One of the quickest ways to do this is to use the Find feature that is built into Access. When you are in form view, the Find button will be on the Form toolbar as shown in Figure 3-06 in your book. Make sure that your cursor is in the field that you want to search on and then click the Find button.
The Find and Replace dialog box will appear as shown in Figure 3-07 in your book and you will be able to type in the value that you want to locate.
To process the request, click on the Find Next button. If the value exists in the table, Access will move to that record. At this point you can close the Find and Replace dialog box or click on the Find Next button again to find the next record in the table that contains the same search value.
Another feature of the Find and Replace dialog box is the ability to replace the located or found values with an alternate value. To perform this, click on the Replace Tab in the dialog box and put the Find and Replace values in the appropriate boxes. Now you can use the Find Next button to move to the next record that contains that value and if you want, you can click on the Replace button to replace the value with the new one. You also have the option of replacing everything at once by clicking on the Replace All button.
When you locate records using the Find button, all of the records in your table will still be displayed. There will be times that you will only want to see the records that match the criteria that you are searching for. In this case, you can use the Filter option instead of the Find option.
The Filter option allows you to narrow the result set by displaying only those records that match the search value. The easiest way to do this is to select the data that you want to filter on and then click the Filter By Selection button as shown in Figure 3-12 in your book.
As you can see, the text Lenger Mason's has been selected. When the Filter By Selection button is clicked, only those records that match Lenger Mason's in the Name field will be displayed in the datasheet. If you look at Figure 3-13in your book, you can see that only one record is displayed because only one record in the table matched the criteria.
If you decide that you would like to remove the filter that you placed on your data, you can do this by clicking on the Remove Filter button as shown in Figure 3-14 in your book. Once you remove the filter, all of your records should again be displayed in the datasheet.
Some times you will have records in your table that you will no longer need. It is possible to delete records from your table using a variety of techniques. The easiest way is to open the table in datasheet view and then click on the record selector of the record that you want to delete as shown in Figure 3-15 in your book.
Once the record is selected, press the Delete key on your keyboard to remove the record from the table. It is important to remember that once you do this, there is no going back. This is a permanent action and Access cannot recover the record once it is deleted. Figure 3-16 in your book shows you how Access warns you that you are about to delete various records.
If you click the Yes button, you won't be able to get these records back unless you have made a backup of your database.
Access allows you to delete multiple records at one time. This is done by selecting multiple records and then pressing the Delete key on your keyboard. There may be cases when Access does not allow you to delete records and this usually happens when referential integrity is involved. We will discuss this later in the chapter.
Access allows you to change the structure of a table after it has been created. When you change the structure, you change things like:
To change these things you need to make sure the table is open in Design View by using the method shown in Figure 3-17 in your book.
Once it is open, you can make any change that you want. If you would like to change the name of a field, click in the Field Name column and type in a new name. Access will allow this as long as no other fields in the table have the same name you are trying to use. To change the size of a field, select the field and then click in the Field Size property box as shown in Figure 3-19 in your book.
Type in the new size of the field. One important thing to remember here is that you risk losing data when changing the size of the field. This is particularly true for any type of text field. Access will not stop you from making the field smaller. If you have data in the field that is more characters than the size you've allotted and you save the changes, you will lose the data that goes past the new size.
Field types are another thing that Access allows you to change. If you've made a field that is a text type and you determine that only numeric data will be used, you can change it to a numeric type. This operation will perform correctly as long as only numeric data is in the field. If alpha characters happen to be in the field, Access will not allow this action to complete.
Access allows you to add fields to existing tables with minimum impact to the existing table structure. There are many reasons why you might want to add a field, including changes in a users needs and policy issues.
To add a new field, you can either put it at the end of an existing table or include it in a specific location between existing fields. To add a field between existing fields, first open the table in Design View. Once the table is open, select the row where you would like the new field inserted. Press the Insert key and a blank row will open up as shown in Figure 3-22 in your book.
In the new row, enter the Field Name, Data Type and a Description if you are using them. At this point you can also switch between the row and the Field Properties at the bottom of the window by using the F6 key.
Once you are done updating the structure of your table, you need to save the changes to make them permanent. You can do this by clicking on the File menu and selecting the Save option or by closing the table and letting Access present the Save Dialog Box.
When this box appears, as shown in Figure 3-24 in your book, you can chose to save or cancel your changes. If you save them, Access may display additional information about possible problems that could occur with your data by changing the structure. If you are confident that your structure changes will not harm your data, continue with the save process. If for any reason you are not sure what might happen to your data, cancel the change and reevaluate the decisions you made and verify that your data will not be harmed.
If you need to make changes to a single field and it will only affect a few records, using the datasheet view of the table is probably the easiest way to accomplish this. The first thing that you need to do is make sure that the table is open and is being viewed in Datasheet View. Remember that you can do this by right-clicking on the table name in the Database Window and then selecting the Open option from the shortcut menu.
Once the table is open, you can move to the field that you need to change by using the mouse and clicking in any of the records. Once you are in the field, you can use the arrow keys or the navigation keys to move to the other records that need to be changed. Figure 3-25 in your book shows you an example of how to make a change in a text field.
Once you make the change, make sure that you move out of the field to guarantee that the new information will be saved. When you are finished making changes, make sure that you close the table so that you don't accidently change something that shouldn't have been changed.
There are times when you will need to make mass changes to a large groups of records and you will not want to change each record individually become it could be very time-consuming. In this case, you would want to use an Update Query to make this task easier.
To create an Update Query, start a query as you normally would and then right-click anywhere in the upper window to view the shortcut menu as shown in Figure 3-33 in your book. From the shortcut menu, click on Query Type and then select the Update Query option. The lower window will change and you will see a Update To row appear.
Make sure that you enter the Field Name in the Field row. In the Update To box, you will want to enter the new value that should be used in this field. At this point, Access has enough information to run the query. If you want to change every record, run the query by clicking on the Run button in the Query toolbar. Access will then display a message telling you how many rows you are about to update. If you are sure that you want to proceed with this operation, click the Yes button. This is another one of those all or nothing operations. Once you click on the Yes button, you cannot retrieve your original data. If you are not sure about performing the update, click on the No button to cancel the operation.
You also have the option of performing the update using the Criteria box. This will enable you to update records with the value in the Update To box where the fields match the criterion in the Criteria box. This is a very clean way of updating multiple records at one time.
There are times when you will need to delete large groups of records and you will not want to delete each record individually become it could be very time-consuming. In this case, you would want to use a Delete Query to make this task easier. To create a Delete Query, start a query as you normally would and then right-click anywhere in the upper window to view the shortcut menu as shown in Figure 3-36 in your book.
From the shortcut menu, click on Query Type and then select the Delete Query option. The lower window will change and you will see a Delete row appear. This will enable you to delete records where the field in a specific table matches the criteria.
Make sure that you enter the field name in the Field row. The Delete row gives you two options to choose from. If you are using a specific field name, you will use the WHERE clause to perform your query. If you use the asterisk (*) to signify all of the fields and want to use the criteria, you will need to use the FROM clause and then put the WHERE criteria in the next column using the specific field name that the criteria should run against. It's important to understand that if you use the asterisk (*) and don't use the WHERE clause to narrow down the delete, Access will assume that you want to delete all of the records in the table. You will receive one warning about this and if you accidentally click the Yes button, you will not be able to recover your records unless you have backup of your database.
Validation rules are used to verify that the information that is being stored in your table is in the proper form. You can include rules on a field that limit the values that users can input. Examples of these rules are:
Let's look at some of these so that validation makes more sense to you.
Figure 3-39 in your book shows what you need to do if you want to make sure that the field always has data supplied by the user.
To guarantee this, it is necessary to make the field required. As shown, the way to do this is by selecting the field in the upper part of the window and selecting Yes from the Required box in the Field Properties section. Once you do this, Access will not save any new records that don't contain data in this field.
Another example we can look at is specifying a range of data that the user must stay within. Figure 3-41 in your book shows that a range has been specified for the Amount Paid field.
If you look at the Validation Rule, it states that the user must enter a value between 0 and 90000. Failure to do this will cause the Validation Text message to appear in a dialog box to warn the user of the error that they've made. The user also will not be able to move out of the field until they enter a valid value or they use the Esc key to cancel the changes they have made to the field.
From the above list, you can see that it is possible to create many different types of validation rules. These are very good helpers to ensure that your data is as "clean" as possible.
Once you have validation rules in place, you will need to remember what they are and exercise caution when entering or updating data. While validation rules exist to help us, they can also be annoying if a user tries to enter data and Access returns an error because the data is invalid. You should make sure that you include error messages in the validation text that make sense to the user and won't cause them to become upset. As long as the validation rules are followed, you can update a table using the same techniques described previously in the chapter.
To update a single field and few records, open the table and then move to the field that you need to change by using the mouse and clicking in any of the records. Once you are in the field, you can use the arrow keys or the navigation keys to move to the other records that need to be changed. Figure 3-25 in your book shows you an example of how to make a change in a text field.
To update fields in many records, create an Update Query. Start a query as you normally would and then right-click anywhere in the upper window to view the shortcut menu as shown in Figure 3-33 in your book.
From the shortcut menu, click on Query Type and then select the Update Query option. The lower window will change and you will see a Update To row appear, where you will enter the new value to be used for the field.
Remember that you MUST follow the validation rules for the updates to work. If the validation rules are violated, Access will not allow the update to occur and your user will probably receive an error message.
Referential Integrity is the process we use to force a table to be dependant upon the values of another table. For example, your textbook talks about drivers and customers. In the Driver table there is one record per driver and in the Customer table there are many customer records per driver. Within the Customer table, we wouldn't want to have a record that contains an invalid driver number because we wouldn't be able to refer back to the Driver table to access the driver information. The way that we guarantee that this doesn't happen is by enforcing referential integrity. The way we do this in Access is by setting up a relationship between the tables that need to have referential integrity enforced.
To enforce referential integrity in Access, select the Relationships button as shown in Figure 3-54 in your book.
The Show Table dialog box will appear and you will need to select the tables that should be included in the relationship. Do this by clicking on the table name and then clicking on the Add button. When you are done selecting tables, click the Close button.
In the Relationships Window, expand the field list for all the tables so that you can see as many of the fields as possible. There should be a corresponding field in each table. To establish the relationship, click on the field from the primary table and drag it to the corresponding field in the secondary table. This will force the Edit Relationships dialog box to appear as shown in Figure 3-57 in your book.
When the Edit Relationships dialog box appears, make sure that the corresponding field names are correct and then click on the check box next to the Enforce Referential Integrity label. To create the relationship, click on the Create button. The dialog box will close and you should see a one-to-many relationship between the two tables that will be similar to the one shown in Figure 3-59 in your book.
Once referential integrity is enforced, some of your typical actions will be limited. For example, if you try to add a record to the Customer table with a driver number that is not in the Driver table, Access will not let you enter the new record. Also, if you try to delete a record in the Driver table and there are records in the Customer table that are associated with the one record in the Driver table, Access will not let you delete the existing record.
Subdatasheets are one way of viewing related information. After you have setup referential integrity between two tables, you can use the primary table to view the related records in the secondary table. You need to make sure that you have the primary table open in Datasheet View, as shown in Figure 3-62 in your book.
As you can see, if there are related records available for viewing, a new column with plus signs will be visible to the far left in the table. You can click on this plus sign and the records from the related table will be visible in a subdatasheet as shown in Figure 3-63 in your book.
The plus sign will also change to a minus sign. To close the subdatasheet, click on the minus sign. This will collapse the subdatasheet and the plus sign will again be visible.
One last thing you should know about subdatasheets is that you can add, update and delete records using the same techniques you have learned about for a table in Datasheet View.
You can order or sort the records in a table by sorting on one or more fields. If a table has a primary key the sort will default to the primary key field. You can change the sort by using the Sort Ascending and Sort Descending buttons located on the Table Datasheet toolbar as shown in Figure 3-64 in your book.
Make sure that your cursor is in the field that you would like to sort by and then click on either of the buttons. It's important to remember that ascending means from A-Z and descending means from Z-A.
When you sort on multiple fields, they need to be in order from left to right in the table. The major sort key should be to the left of the minor sort key. Figure 3-66 in your book shows two fields that have been selected for a sort.
When the Sort Ascending button is clicked, the records will be sorted first by Customer Type and then by Amount Paid within Customer Type. This works correctly because the Customer Type field is to the left of the Amount Paid field.
If you decide that you don't like the results of your sort, you can cancel the changes. All that you have to do is close the table and make sure that you DO NOT save the changes by clicking on the No button when the Save dialog box appears. You can then reopen the table and the original sorting order should be back.
An index makes it much easier for Access to perform searches and sorting because the index keeps track of which records contain particular values. For the purposes of this book, it's not important to understand everything about indexes, but you do need to understand the general idea. As you can see from the table on the left, an index has been created that keeps track of Names and which records contain that specific name. The whole idea of the index is to make searching and sorting much quicker since Access will know which records contain which names. Because of this, Access does not need to look at each record and search for the name and the sort/search will move a lot faster. This may not be evident when you only have a few records, but you will start to notice speed problems when you get a few hundred or thousand records.
The easiest type of index to setup is the single-field index. If you have a primary key in your table, it already has a index built into it. If you don't have a primary key, you can setup an index by selecting the field in the Table Design View as shown in Figure 3-70 in your book.
Once the field is selected, click on the drop-down arrow in the Indexed box in the Field Properties section of the design area. If you want to use an index that contains duplicates, make sure you select Yes (Duplicates OK). Otherwise, select Yes (No Duplicates) to guarantee that only unique values are stored in the field.
The other type of index that Access allows is the multiple-field index. You have to use the Indexes button to setup this type of index. From the Table Design View, click on the Indexes button to display the Indexes dialog box as shown in Figures 3-71 and 3-72 in your book.
Once the dialog box is open, click in the Index Name column and type in the name of your index. Use the Tab key to move to the Field Name column to select the first field in the index. Move to the Sort Order column to change the sort order. Once the first field is selected for the index, use the Tab key to move to the next row and into the Field Name column. You do not want to type in a new Index Name because Access will think that you are creating another index. Leave the Index Name blank if you want Access to include the field within the index that was created above. After you have finished adding all the fields, click on the Indexes Dialog Box Close button to close the box and to save the new index.
| Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 |
| Discovering Computers | Windows XP | Word 2002 | PowerPoint 2002 | Excel 2002 | Access 2002 | Site Map |