Last updated: 11/8/03
Link back to course Welcome

Using MS Access 2000

  1. Like Excel, Access presents a screen composed of cells located in rows and columns. Don't be fooled by this similarity -- they are very different.
    Description Excel (spreadsheet) Access (database)
    What is in the row labels 1, 2, 3, 4 Nothing
    What is in the column headings A, B, C Field names that you set (or accept Access' default)
    What is in the rows Whatever you want - layout is freeform A record -- a full set of information for a single case, such as all of WSU's personnel information for David Bowen in one record, for Irvin Reid in another
    What is in the columns Whatever you want - layout is freeform Field values - must be the same item (e.g. FirstName) and data type for each record
    Combining tables One spreadsheet can get information from another Can combine several tables to make one big one (relation)
    Size 256 columns by 65,000 rows 255 fields, no limit on records (must be in linked files each less than 1 GB)
Not only will a database handle an unlimited number of records, but it can find records very quickly, even in a large file. Using a  word processor or spreadsheet file, to find a given record, we would have to read through all of the records before it. Because all of the records in a database are the same length, we only need to know which record we want, and we can go directly to that record. A database keeps a condensed short table of the values in each record for selected fields, called keys. For keys, going to a record with a given value is very fast, no matter how large the whole database is.
  1. Getting the demonstration file
    1. For use during this demonstration, download WebLog.mdb by going to the GST 2710 web site (www.is.wayne.edu/gst2710). Remember to right-click on the link to the file to download it. Save it to C:\My Documents or the desktop.
    2. Start Microsoft Access. Access will go directly to opening a file. Keep the choices at Open an Existing Database and More Files... and click OK. Navigate to where you downloaded WebLog.mdb to, and open it.
      Access1.gif (8930 bytes)
  2. When you open a database in Access, you see the "Database View", as below.
    1. The name of the database file (WebLog) and what it is (Database) are shown on the title bar.
    2. Notice the different types of objects listed down the left-hand side: we will be working with Tables, Queries, Forms and Reports.
      1. Table - holds raw information. Rows (records) Vs Fields (columns). Fields have names. A database must have at least one table, but it can have many.
        1. Datasheet View - view and enter data, rename fields, change order of fields, add and delete fields, sort and filter records - shown above for CLL web server
        2. Design View - rename fields, change order of fields, add and delete fields, set type of data (text, various types of numbers, etc.)
      2. Query - like a table, except can a query can combine tables and calculate new information (Excel-type equations cannot be put in tables but only in Queries)
      3. Form - for structured data entry and checking
      4. Reports - summaries of data
      5. Notice that there are two tables. Open them up and look at them.
        1. AccessLog - the web server log for the CLL web server for a certain period in 1998. What are the fields? Notice that every file request is entered in the log, including requests for GIF and JPEG files
        2. Codes - the standard codes for web servers, with descriptions of their meanings. You may have seen 404 - not found - and maybe one or two of the others, but mostly they are exchanged invisibly by the web server and the web browser
    3. Tables. Double-click on the "AccessLog" Table to open it

Datasheet view - this shows a table as an array (rows or records, and columns or fields) of cells, with the field names at the top, running from left to right.

  1. To change a field name in Datasheet view, double-click on the name (highlights the field name), type the new name, and tap <Enter>
    Access3.gif (5670 bytes)
    Practice this by changing the "Date" field name to "Day."
  2. To enter data into a cell
    1. Click in the cell
    2. Type the data
    3. Tap the <Enter> key or go to another cell
      Access5.gif (4150 bytes)
      Practice this by scrolling to the bottom of the AccessLog table and entering some data at the bottom.
  3. "Industrial Strength" database programs do not have a Save or Save As... for data entry. The data is so important that it is saved automatically, as soon as you tap <Enter> or go to another cell.. Other changes must be saved, such as formatting, but not data entry.
  4. Sort the AccessLog table to find the largest file sent out, by
    1. Open the AccessLog Table and scroll right to display the Bytes field.
    2. Click on the Bytes field (column). If necessary, click right on the word Bytes. The whole column should be highlighted (selected).
    3. Click on the "Sort Descending" button.
      AccessSrt.gif (3918 bytes)
    4. What is the name of the largest file? (Look in the Path field.) How many times was it sent out?
    5. Undo the Sort by choosing the menu item Records / Remove Filter/Sort
  5. A Filter will block out a class of records in a table. A web "hit" is a request for a web page, including any graphics. That is one hit is one request for a page, not counting the graphics files separately. We will filter out graphics requests, leaving legitimate hits. "Hits per month" is a common measurement of how popular and busy a server is. (If the boss doesn't know enough, some WebMasters will inflate the number of hits by including graphics requests, but that is asking for trouble later, if you plan to stay.)
    1. Open the AccessLog table. Notice the number of records listed down near the bottom left corner.
    2. Find a record where the path is a GIF and select the .gif (including the "dot") by dragging over it and highlighting it. (This is case-insensitive - GIF and gif are treated the same)
    3. Leave the highlight on. Choose the menu item Records / Filter / Filter Exlcuding Selection. All of the requests for GIF files disappear from the screen (they are not deleted, just not displayed). They have been filtered out.
    4. Repeat 2 and 3 for JPG. Now there are no records with graphics requests at all, leaving only "hits". How many records are left? See the record count down near the bottom of the screen. This is the count of hits for the period covered by the file.
    5. You COULD remove the filter and see all of the records again by choosing the menu item Records / Remove Filter/Sort. If you do this, apply the filter again.
    6. A Filter can be saved and applied again (we will not do that here) but the effects of a filter are not permanent. That is, if the table is closed and reopened, it is not filtered.
  6. Now convert the Filter to a Query. A Query is also a table, but altered by "asking a question" (a query). (A Query can also be filtered, so it acts a lot like a table.) The question is stated in terms of the fields in Tables, or in other Queries. Queries can (a) filter out records, (b) compute new fields based on existing fields, with an identical calculation for each field, and (c) combine information for different tables, or even for different databases (a Join, or Relationship). To convert the filter to a Query,
    1. Choose the menu item Records / Filter / Filter by Form. The Form table appears again.
    2. Save the Filter as a Query by choosing the menu item File / Save As Query. You will be asked to name the Query; choose "Hits" (omit the quotation marks). (If you choose another name, you will have trouble following the directions later on.)
    3. Close the table, click on the Queries tab and open Hits. Note that the number of records is the same as the number in AccessLog with the Filter applied. There are no graphics file requests in the Query. The effect of the filter on this Query is permanent and intrinsic. The missing records are still in the database file, but they can never be displayed in this Query.
  7. Now we are going to create a Relationship by doing a Join. Both the AccessLog Table and the Codes Table have a field named Code. We can combine the information from both Tables into one virtual Table by creating a Relationship between the fields with the same name and Join the two tables together. This is the most powerful aspect of databases. Information from different sources can be combined together using Relationships and Joins. The two databases can be Joined over a network, or over the Internet. To create the Relationship,
    1. Close the Hits Query and click on the Tables tab. Unselect any selected Table by clicking elsewhere (a dotted box is OK, but not a highlight)
    2. Choose the menu item Insert / Query. On the New Query dialog, make sure that Design View is selected and then click OK
    3. On the Show Table dialog, the Table "AccessLog" should be selected; if not, click on it to select it.
    4. Click the Add button. An AccessLog dialog should appear in the grey top area of the Query1 dialog. This dialog displays the field names for the AccessLog table.
    5. Also select and Add the Table "Codes." A Codes dialog should appear in the grey top area of the Query1 dialog, displaying the field names for the Codes table. Close the Show Table dialog
    6. On the Query1 dialog, scroll down the AccessLog dialog until the field name Code show.
    7. Click on  "Code" in one of the dialogs and drag to the other.  Don't let the "Circle and Bar" mouse icon bother you when the mouse is in between the two dialogs. When you are done, a thin black line with bumps on the end should be displayed, joining the two Code field names. That creates the Join or Relationship.
    8. Now add some fields to the Query. Click at the right of the "Field" box at the bottom left of the Query1 dialog. Click on AccessLog.* to add all of the fields (*) from that Table to the Query.
      AccesQry.gif (2599 bytes)
    9. Similarly, in the second column, add only the "Description" field from the Codes table.
    10. Close the Query1 dialog. Choose to save the changes, and name the Query anything you want.
    11. Click on the Queries tab and open the new Query. Notice that the Description field from the Codes Table is present, along with all of the fields from the AccessLog table. To get ready for the next section, close the Query.
  8. Design view - this shows the list of field names only, running from top to bottom
    1. To open a table in Design view, click on the table you want, to select it, and then click on the "Design" button


      Access6.gif (8035 bytes)
    2. To insert a new field in Design view, click in the next empty space and type a name for the field. To accept this, leave the space by clicking or arrow-keying to another space, or by tapping <Enter>
    3. To change a field name in Datasheet view, click in the space and edit the name, using the normal editing keys
    4. To change the data type for a field, click near the right edge of the "Data Type column for that field. A drop-down arrow pops up - click on it. Select the data type from the drop-down list. The following data types are available:
      1. Text
      2. Number
      3. Date/Time
      4. Currency
      5. Auto Number
      6. Yes/No
  9. Now we will create a Form. Forms are often called "Screens" by people who work with databases a lot, without necessarily being aware of what is going on behind the scenes. Forms can be used to display and enter information into a Table. To create a simple form, using the form Wizard,
    1. Click on the Forms tab and then click on the New button.
    2. On the New Form dialog, select Form Wizard. Where you are asked to "Choose the Table or Query", select the Table AccessLog. Then click Next.
    3. Add all of the Fields to the Form by clicking on the Right-pointing double-headed arrow. Click Next
    4. On the next step, leave the selection at Columnar and click Next
    5. For the style step, leave the selection at Clouds and click Next
    6. On the step showing the Finish flag, leave the selections as they are and click the Finish button.
    7. Your new Form should appear. All of the information for one record is displayed on one screen, without scrolling. You can change the information in a field. You can cycle through the fields by using the arrow button at the bottom left of the Form. You can go to the end and enter a new record. Forms can also be used to make sure that information entered is valid (Data Validation) by performing a variety of checks. (Access is not reading your mind here - you have to tell it what you mean by "Valid", e.g. "five numbers no letters" for a zip code.)
    8. Close your Form to prepare for the next step.
  10. We will look at a Report. A database Report lists the records in a given Table or Query, with Filters and Sorts applied to the Table or Query. Charts can be added. Calculations can be performed. For example, for a sales database, the sales for various regions can be added (summed) or counted (if the regions and sales information is in fields, of course.) Here we will look at a Report that lists the Hits for each Path (file or web page) and see how it is constructed, without holding you responsible for constructing a report on the Final Exam.