MIS 70-451

 

Access Lab 1

Getting Acquainted with Access

 

[This lab exercise was created by Evelyn Barry.

If you have any questions or comments, contact her at eb48@andrew.cmu.edu]

 

 

Objective:

This exercise will serve as an introduction to Microsoft Access '97. In this exercise you will open an existing Access database, become familiar with key building blocks of relational databases and practice finding and viewing data stored in the tables of a relational database. Approximate time: 30 minutes

 

To Start:

  1. From the Windows desktop, click on Applications.
  2. Click on Microsoft office
  3. Click on Microsoft Access
  4. Click on Open existing database
  5. With 'more files' highlighted, click on OK.
  6. Request the Northwind database by clicking on file folders until the Northwind access data base appears. Highlight it, and click on Open. In most systems, the Northwind database is loaded at the same time the Access software is loaded. It is usually found in a path for ... ..../Microsoft Office/Samples ...

 

After you have opened Access and the Northwind database, your screen should resemble the picture below.

 

 

 

Tables:

 

Data Sheet View -

After opening an existing database, Access will display several object tabs. Each tab represents a grouping of objects for the data stored in the database. The objects are of different types; tables, queries, forms, reports, etc. Each object type is a different way to look at the same set of data elements stored in the database. We will start by looking at the Customer table in the Northwind database.

 

Click on the Tables tab, and then click on the Customers table. Customers should be highlighted. Now click on Open. The system should display the contents of the Customers table. You will notice that the name of each field is at the top of the columns in the table. Values for each field appear in the columns. All values in one row (tuple) of the table are considered a record. Each of the fields in one record relate to the same customer and can be identified by the key field, 'Customer ID'.

 

 

Design View

 

 

Design View -

Now let's look to see how each field has been defined in the Customers table.

 

In the far left corner of the design view tool bar (the second tool bar across the top of the screen) you will see an icon resembling an engineer's triangular drawing tool. Click on this icon. The display should change from the data sheet view to a design view. The design view will list each field in the table, with parameters describing the field name, data type and description. The field names correspond to the table's column headings.

 

Data type defines the type of data allowed in that field. There are a number of different data types you can use. Access makes it easy to choose by listing them for you. To see what I mean, click on the data type field for Customer ID. A down arrow will appear in the far right end of the data type slot. Click on that arrow and Access will display the different data types available.

 

 

 

Keys: You will notice a small key symbol in the far left column on the design view of the customer table. This indicates that the Customer ID has been designated as the primary key for this file. In addition, the field properties for Customer ID show that this field is indexed and will not allow duplicates. By not allowing duplicates in a key field, Access insures that the use of a key will uniquely identify each instance on the Customer table.

 

Move the cursor down to another field in the design view of the customer table. You will notice that the field properties in the lower half of the screen have changed. The field properties are the detailed information describing the new field you just moved to. We will discuss the meaning of each of the properties in the next exercise. For now, just try to understand the relationship between fields and tables, and the use of keys.

 

Finding Records:

How can we locate a particular customer in the customer table? We don't want to look through all 91 records to locate one individual. First, we need to go back to the data sheet view of the customer table. To do this, locate the small icon in the upper left corner of the second toolbar. It will resemble a small table. Click on that, and you should see the table of data we first saw.

 

We want to locate the record for Paris specialties, with Customer ID = PARIS. Move the cursor to the name of the field you want to search. In this case, we want to search the Customer ID field, so move the cursor to the Customer ID label. The cursor should change to a vertical arrow. Click on the field label. The entire column of data should now be highlighted.

 

Now we are ready to enter the Find command. Choose Edit from the top toolbar, and then click on Find.

 

In the Find What box, enter the value you want to match against Customer ID. In this case, we want to match PARIS. We are searching all records, and expecting a match on the whole field.

 

 

Click on Find first and Access should locate the record for PARIS. Check to see if there are any other records with the same customer id. There shouldn't be. Why not? (Hint: Customer ID is the database key)

 

Try locating a record(s) for the following:

A record for a company in London

A record for contact name with last name = Trujillo

A company with postal code = 59000

 

Are there multiple records to find for any or these searches?

 

In later exercises we will learn other ways to retrieve, update and store data in a database. At this point you should be acquainted with the navigation techniques in accessing data in tables, and the basic concept of the design view for the table fields.

Return to home page