Access 2002

What We Have Learned

Chapter 2

Review

In this chapter we have learned that the data in a database may be manipulated in a variety of ways without affecting the way that the data is stored in the tables. This ability means that data can be examined and maintained in variety of ways making the process easier than if the user had to work with the data as it was originally stored. We have seen some of the power of Access to do data manipulation, but there is even more capability to be seen in future chapters.

Some of the techniques we have seen include:

  • Sorting
  • Arranging table columns
  • Find and Replace using specific values and wildcards
  • Adding and Deleting records
  • Cutting and Pasting data using the Office Clipboard
  • Hiding and Unhiding columns
  • Freezing and Unfreezing Columns

In addition to looking at how existing data may be manipulated, we have examined how databases can be developed and designed. A poorly designed database can cause more problems than it solves. Therefore, the design process is critical to database development. The critical process in developing a database is to assess the information needs. From that assessment, table subjects and relationships can be determined through a process called normalization.

At times database designers build a prototype. A prototype is a trial version of the database. By building a small version of a database the designer and user can find and correct errors before committing to the final version of the database.

Questions:

  1. To what does the term Maintenance refer?
    Discussion
  2. How does sorting records help with the maintenance process?
    Discussion
  3. Under what condition would a search be more useful than sorting?
    Discussion
  4. Once a table has been defined, can the columns be changed? Why or why not?
    Discussion
  5. What is the purpose of wildcards and what are two of the most commonly used?
    Discussion
  6. Why do database designers use prototypes?
    Discussion
  7. If prototypes are so useful, why does a designer need to be concerned about the early design of a database?
    Discussion

Discussion for Question 1

Maintenance is process of keeping data current and correct in a database. Typical maintenance activities include the creation of new records, deletion of unneeded records, and changes in values to fields within existing records.

Return to Questions.

Discussion for Question 2

As records are added to the database they are stored in the order they were entered. This sequence of records is generally useful to the user. In most cases, the user would like to have the data in a sequence appropriate for a specific task. Some times the user might want to make changes based on a person’s name; in other cases, ordering the records by location would be more appropriate. Sorting helps the user to perform maintenance by allowing maintenance to be done directly in a logical manner. The user does not have to search through the database to find the next logical record.

Return to Questions.

Discussion for Question 3

When a large number of records need to be processed, sorting may be the best solution. However, when just a few records need to be maintained and the user knows the specific criteria to find the records, searching may be more efficient.

Return to Questions.

Discussion for Question 4

Yes, it is quite easy to rearrange the columns of a table. The underlying data is not actually moved on the hard disk. The database management system reads the record from the disk but rearranges the columns before they are viewed on the screen. This capability allows the user to see data in a variety of formats without requiring multiple copies of data on the hard disk.

Return to Questions.

Discussion for Question 5

Wildcards are a way to facilitate searches through a database. In many cases the user does not have complete information for the desired search. In other cases, typing in a full name or text value would cause a lot of unnecessary keying. The wildcard combined with a few specific characters can create a pattern match that will allow Access to retrieve a small set of records. The user can then examine them to find the desired record.

The asterisk (*)can stand for any number of characters in a field and allow any character value. The pattern “th*” would match many words such as The, They, Those, Thine, Thomas, Theodore. The more specific the user can make the pattern, the fewer the number of erroneous records.

The question mark is also a wild card and allows a match to any character for a specific position in a field. Sometimes, fields have a well defined format where each position in the field as a specific meaning. Automotive VIN numbers are an example of such a field. For example, the second character in the VIN specifies the manufacturer of the vehicle. If searching for all General Motors cars, one could create a search pattern like “?G*”. This pattern would accept any value in the first position, but the second position would have to be a “G”. It would not matter what came in the third to 14th positions.

Return to Questions.

Discussion for Question 6

Prototypes are trial versions of the database. Database designers generally build a prototype before committing to a final design because the user often has unanticipated needs. As users review the prototype they can find other uses that they had not thought of until they see a tangible product.

Prototypes allow the designer the chance to build a small database and to experiment with it. Any necessary changes can be applied before large amounts of data are stored. Major changes in design once the data has been gathered can cause high maintenance costs. Proto types are expendable. If the prototype uncovers the need for major design changes, the prototype can be discarded and an entirely new prototype developed.

Return to Questions.

Discussion for Question 7

It is not a contradiction to say that a database should be well designed from the start and that prototypes should also be developed. There are principles of database design that always be followed. Prototypes are not an excuse for sloppy design. Prototypes assist in the design process but they cannot substitute for laying a good database foundation. One of the points of the work that we are doing is showing how a bad initial design leads to many problems. In database design, we want to be refining and improving the initial design and not trying to overcome it.

Return to Questions.

Practice Exercise

To this point we have not actually incorporated any keys into Joe’s database. In this exercise we are going to add primary keys to each of the tables that we built in the Getting Started section of this chapter. These primary keys will make the database more efficient by establishing indexesfor the tables. Indexes are special files used by Access to improve database processing. We are going to establish the primary keys as AutoNumber fields. By using an AutoNumber field, Access automatically assigns a number to the field in any new record that is created. Assigned numbers are only used once. Thus if a record is deleted, the number will not be used again. We will be using numbers for the primary keys rather than the fruit or vendor name to minimize the amount of keying done by the user and for faster processing by Access.

We will then create a permanent relationship between the two tables by using the LookUp Wizard. The LookUp Wizard establishes the foreign key to primary key connection.

Our plan of action is:

  • Add an AutoNumber field to each of the new tables—Fruit and Vendor
  • Make the new fields primary keys
  • Use the LookUp wizard to create a foreign key in the Fruit table which links to the Vendor table
  • Set the proper value in the foreign key field for each record in the Fruit table.
Adding Primary Keys to the Tables.
  1. Open the Fruit-1-M.mdb database and make sure that you are view the Tables object.
  2. Open the Fruit table in Design view. Highlight FruitName and click the Insert Row icon.
  3. Name the new field FruitID and set the datatype to AutoNumber.
  4. Select the Fruit field and then click on the Primary Key icon. The result should be similar to Figure ACWL02.1.

Figure ACWL02. 1 Reformated Fruit Table

  1. Close the table and save the changes.
  2. Follow the same procedure for the Vendor table. The results for this change should look like Figure ACWL02.2.

Figure ACWL02. 2 Revised Vendor Table

  1. Close and save the changes to the Vendor table.
Relating the Fruit and Vendor Tables

In order to use multiple tables together, relationships must be created. Most relationships are based on the use of a common field within both tables. Access provides a nice way to build relationships between records called a Lookup field. This topic is addressed in more detail in Chapter 4 but we are going to use the technique because it is relatively simple to do and provides many benefits.

  1. Make sure that the Fruit-1-M.mdb database is open and the Table objects are in view.
  2. If the Inventory table is present, as shown in Figure ACWL02.3, you can delete it using the Delete icon.

Figure ACWL02. 3 Fruit-1-M.mdb Tables

  1. Open the Fruit table in Design view and select the VendorName field. Select Lookup wizard in the Data Type column. This is shown in Figure ACWL02.4.

Figure ACWL02. 4 Lookup Wizard Selected for Vendor Name

  1. The wizard then proceeds to build the relationship. Select “I want the lookup colum to look up the values in a table or query” as in Figure ACWL02.5.

Figure ACWL02. 5 Look up Values from a Table

  1. Click Next. Make sure that the lookup will be in the Vendor table. Click Next.
  2. From the Vendor table select the VendorID and VendorName fields. Figure ACWL02.6 shows this process.

Figure ACWL02. 6 Selecting the Lookup Fields

  1. Click Next. As shown in Figure ACWL02.7, Access shows you how the Lookup will appear. Keep the key field hidden and click Next to accept the view.

Figure ACWL02. 7 Format of the Lookup Field

  1. Accept VendorName as the label for the lookup column and click Finish to complete the process.
  2. In this case, the Vendorname column was originally text and contained the vendor’s names. Access will change the data type to number and will delete all of the text names. Figure ACWL02.8 shows the warning message. Click Yes to accept this change.

Figure ACWL02. 8 Warning that Data will be Changed

  1. You will now need to open the Fruit table in Datasheet mode. Click on the down arrow in the VendorName column. The potential vendor names appear in the dropdown listing. Figure ?? show the process. You will have to go through each record of the file to select the correct vendor. You can see that this process could become quite tedious if there were many records in the table.

Figure ACWL02. 9 Process of Linking Fruit Table to Vendor Table

  1. When all of the records are linked, close the table to save the changes.
Building a Two Table Query

In this exercise, we build a query that replicates the original Inventory table. We will use the Query Wizard to generate the query.

  1. Open the Queries Object window.
  2. Highlight “Create query by using wizard” and Click New.
  3. Select Simple Query Wizard and OK
  4. Select all of the fields from Fruit table except for FruitID.
  5. Select all fields from the Vendor table except VendorID and VendorName and then click Next twice.
  6. Title the query InventoryQuery and click Finish.

Arthur R. BussACWL 2.1<Au 1st draft>

Access 200210/15/2018