MICROSOFT ACCESS 97 LAB SESSION 1 - SIUE



Dr. Bordoloi

MICROSOFT ACCESS LAB 1

Introduction

A database is a collection of data that is stored electronically. The person who “owns” the database needs to manage this data, i.e., retrieve data upon request, delete data that is not needed, add new data, and modify existing data. He/she will also want to answer questions from the database and develop and print reports based on the database. These tasks are enormously complex when the volume of data gets large. A database management system (DBMS) is a program (software) that helps the user manage large volumes of data with relative ease.

Terminology

Databases are usually designed and implemented according to a fairly standard architecture that is well suited for computer processing.

Tables (also called Files):

A collection of related data (a file folder). A database consists of one or more related tables.

Records:

A collection of individual items; many such collections may exist in a file or table (a piece of paper in a file folder).

Fields:

A Single item in a record.

An Example

Consider your address book. Typically, the book contains several addresses and each address has a name, street address, city, state, zip code, and telephone number, If you were to store this data in a database, the entire address book would make up the table or file, each address would be a record, and each item in a address ( e.g., name) would be a field.

Microsoft Access

The particular DBMS that we will use is Microsoft Access. This is a Windows 95/Windows NT-based product that uses a graphical user interface (GUI). It has the capability to embed graphic images within the database. Microsoft Access, like all other commercial DBMS’s requires that each record within a table have the same structure as every record in the same table.

Bring Up Microsoft Access

Task1: Create and Name a Database (name it Bookstore Inventory)

1. From Microsoft Access startup dialog box, choose Blank Database and click on OK

2. In the File name text box, type the new database file name- let’s make it Bookstore Inventory.

3. Click on the Create button.

Task2: Create the AUTHOR table

1. Click on Tables (probably not necessary).

2. Click on New.

3. Double-click on Design View. (Note: We are building this table from scratch. In a later lab we will use Wizards to create a database and tables.)

4. Type Author Number in Field Name of the first row in table, then press Tab or Enter.

5. Click on drop-down arrow to see the available data types.

6. Choose Number, then Tab or Enter, then Tab or Enter again.

7. Type Author Name in Field Name of the second row in table, then Tab or Enter.

8. Accept text as the data type by pressing Tab or Enter.

9. Let’s set Author Number as the Primary Key for this table. Move the cursor to anywhere in the first row, then click on the primary key toolbar button (a picture of a key near the middle of the toolbar).

10. Now save this table structure by clicking on the Close button (the X) in the upper right-hand corner of the window. Select Yes to save the changes. Finally, name this table AUTHOR and click on OK.

Task3: Enter Data in This Table (AUTHOR table)

1. The data to be entered in the Author table is found in the attachment to this handout

2. In the database window, click on Tables tab (may not be necessary). Make sure the AUTHOR table is highlighted then click on Open. This opens the AUTHOR table in the database view, ready to enter data.

3. Now simply enter the AUTHOR table data, using Tab, Enter, or the right arrow key to advance from one field to the next.

4. To adjust column sizes to handle the full titles and entries, click on Format at the top of the screen, then Column Width, then Best Fit. Do this multiple times if later entries are larger than earlier ones.

5. When you have completed entering the data, simply click on the Close button (the X) in the upper right-hand corner of the window. Access automatically saves the data you have entered. Say Yes to save changes to the layout of the AUTHOR table.

Task4: Create Another Table (WROTE table)

Following the same general approach outlined in Task 2 above, create the WROTE table. Call the first field Book Code and make it data type Text (note the Xs in this field!). Call the second field Author Number, and call the third field Sequence Number- both these fields are obviously Numbers. Denote both the Book Code and Author Number as the primary key of this table. To do this, select both rows by holding down the Control (Ctrl) Key while clicking on the row selector button to the left of the field name in both rows. Then click on the Primary Key toolbar button.

Task5: Enter Data in This Table (WROTE table)

Following the same general approach outlined in Task 3 above, populate the WROTE table by keying in the data found in the attachment to this handout.

Task6: Consider the Form View for Working With a Table (WROTE table)

Don’t close the WROTE table yet. Instead, change to the Form View using the AutoForm. To do this, click on the drop-down arrow to the right of the New Object toolbar button (second icon from the right edge). Choose AutoForm. Access builds a simple form (one record per screen) and displays it on your screen. This is an alternative way of viewing a table, and it may be easier for data entry, especially for tables with a large number of data fields.

Task7: Define the Relationship Between the AUTHOR and WROTE tables

1. Close any open tables so that only the database window is visible.

2. Click on the Relationships toolbar button (the icon showing tables connected by lines-near the right end of the toolbar). This will open up a Show Table dialog box.

3. Click on the AUTHOR table name to highlight it, then click on Add. Click on the WROTE table name to highlight it, then click Add. Click on close to take you to the relationships window.

4. To define the relationship between these two tables, move the mouse pointer to the Primary Key field in the primary table (the table on the “one side” of a one-to-many relationship). That Primary Key field is boldfaced in this list. In our example, this is the Author Number of the AUTHOR table.

5. Drag that field name to the corresponding field in the related table (that is, drag it to the appropriate foreign key). In our case, drag it to the Author Number field in the WROTE table.

6. This causes a dialog box to open. Click on the Enforce Referential Integrity box. This will ensure that every record in the WROTE table has a corresponding record in the AUTHOR table. It will also prevent you from deleting an Author record if that author has books in the WROTE table. Click on create to complete the definition of the relationship.

Task8: Use One-Table Queries

Query A: List all the author names with a last name beginning with one of the letters A through H.

1. Make sure you save your Relationships changes. Get back to the database window. Click on Queries, then New.

2. Click on Design View, then OK.

3. Select AUTHOR table, then Add, then Close.

4. We will use the default query type in these initial queries, which is a select query. To change to another query type, click on the Query Type icon in the middle of the toolbar (two overlapping tables) and choose the desired query type.

5. Click on the drop-down arrow in the first column and choose Author Name. This puts Author Name in the first column and puts a checkmark in the Show row so that Author Name will “show up” in the answer to the query.

6. In the criteria row, type box. This moves all field names to the selected Fields box. Click on Next.

4. We don’t want any grouping levels, so, if necessary, remove any preset grouping levels by clicking on < box, then click on Next.

5. Let’s sort on Book Code. Click on Next.

6. Accept the default values for Tabular Layout and Portrait Orientation by clicking on Next.

7. Let’s try a Corporate title. Click On Corporate, then Next.

8. Preview the report to see if you like it by clicking on Finish.

9. Print the report by clicking on the Printer icon at the left of the toolbar.

Task12: Closing Access

This one is easy: Close all open windows by clicking on all the close buttons (Xs) in the upper right-hand corners of the windows. All files are saved automatically.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download