Introduction to Access

 Objectives

  1. Define Terms: Field, record, table, database
  2. Identify objects in the Database window
  3. Add, edit, find, and delete records within a table
  4. Define relational database with one-to -any or many-to-many relationship
 Colleges must maintain records of students, courses, staff, and accounts. This information was created, stored, and updated manually. Now we have programs for micros such as Paradox, Access, and Approach to handle this task much more efficiently.

For example, a university bookstore keeps information (Title, Author, Publisher) about each book in stock. It also keeps information( name, address, phone number ) of each publisher. In addition, each time a book order is placed, information (order number, date, and quantity) about each order is kept track of. In the past, this information was stored on sheets of paper in folders which were kept in a file cabinet. In a computerized database, each of these three types of information may be kept in tables called Books, PUBLISHERS, ORDERS, and Detail Orders Each of these tables contains numerous records each containing the required information. In the tables below, the field names are found in the first row. Each row thereafter represents a record.

 A small portion of the Books TABLE:
 
ISBN NUMBER TITLE INVENTORY AUTHOR
PUBLISHERID
0-13-794827-1 The Right Phit  123
R. Grauer
P4
0-201-68814-x Javascript  98 Jeremy Schneider
P6

A small portion of the PUBLISHERS TABLE
 
NAME Publisher ID ADDRESS Telephone
Prentice Hall P4 Upper Saddle River, NJ 619-254-7854
Ventana  P6 Chapel Hill, NC 800-743-5369

A small portion of the ORDERS TABLE:
 
Purchase Order Number Date PublisherID
PO87654 1/12/96 P4
PO87674 3/15/96 P6
A small portion of the Order Details Table
 
PURCHASE ORDER NUMBER ISBN NUMBER PRICE QUANTITY
PO87654 0-13-138686-7 52.95 500
PO87655 0-13-794827-1 34.95 500
PO87655 0-201-68814-x 23.95 200

The Access screen contains 6 database objects: TABLES, QUERIES, FORMS, REPORTS, MACROS, and Modules:
 

Download the Access97 student data files from Ron MacKinnon's local access files on the Info 130.12 page at

http://www.stfx.ca/people/rmackinn/Grauer/Grauer%20IM%20Access%2097/Student%20Data%20Disk/

 Open Access and open the Bookstore.mdb (Access uses mdb as the file extension)

  1. Notice the 6 database objects and click on the tabs to see the names of the various objects already designed in this database.
  2. Click on Tables and then open the books table. What you see now is the Datasheet View of the Books Table.
  3. The fields for this table have been defined in the top gray row. Each succeeding row is the record. There are 22 records in this table. You can use the scroll bar, the record selector in the left bottom corner, and arrows to go from record to record..
  4. Each table must have a unique field on which the records are identified. In this case the ISBN Number is the a unique field and is called the Primary Key.
  5. Saving in Access is done automatically as you enter each record.
  6. Add a 23rd record as follows:Click new record button ® 0-13-271693-3 ® Exploring the Internet/2nd edition ® Grauer/Marx ® 1997 ® 28.95 ® Prentice Hall
  7. Edit a record
  8. Validation in some field warns when incorrect data is being entered: Try entering qqqq in the year or price fields
  9. Delete a record - There is a warning before deleting records

    10.  


Find and Replace Command – useful in correcting misspelled names or mass updating

Go anywhere in title field ® Edit ® Replace ® Find What: "Exploring Windows 95" ® Replace with: "Prentice Hall" ® Find -->Replace
        ---> or Replace All (if doing a mass update)
 Undo Forms view of the same table - repeat some of above activities in forms view. Which is easier to edit and view?

When in datasheet view ® View ® Form View ( The form can be made automatically or custom designed as this one is.)

Reports – click on reports tab and see samples of reports already designed

Reports can be automatically generated such as the Books report or custom designed  Queries – are designed to extract certain records fulfilling set criteria. Example: Open Publisher query ® enter Prentice Hall as the Parameter value ® a list of all books published by Prentice Hall are returned in the answer set  Relational Databases
  1. In the above query, we extracted information contained only in the Books table. By relating or connecting tables with a common field name, information across several tables can be extracted.
  2. For example: We could find the answer to our question:

    3. What are the address and telephone number for the publisher of the book The Right Phit?
    This requires
  3. Another question: Which books were published by Ventana press?

    4. Look in the Publisher table -->get the ID --> go to the Books table and match up all books having that ID. Again this is a one-to-many relationship
    Answer: Javascript
  4. Question: What is the publisher and address associated with Purchase order number PO87654?

    5. Look in Orders table --> get Publisher ID that matches PO87654 --> go to Publisher's table and get the address
    Answer: Prentice Hall, Upper Saddle River, NJ
  5. Many-to-many relationships can occur between tables. For example you can have many orders having many of the same books in these orders.

    6. Which books were included in order Number PO87655?
    Search Orders details table for all occurences of PO87655 --> get the ISBN numbers --> go to the BOOKs table to find the matching titles
    Answer: The Right Phit and Javascript

Assignment:

Do multiple choice questions
Do Practice with Access # 1-4 on Pp. 32-34
Hint on #4 go to http://www.superbowlhistory.com/yearbyyear.html