Last updated: 4/9/03
Link back to course Welcome

Using MS Access 2000 #1

  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)
  1. Getting the demonstration file
    1. For use during this demonstration, download WebLog.mdb, either through Blackboard or by going to the GST 2710 web site directly (www.cll.wayne.edu/isp/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 by Selection. All of the requests that are not for GIF files disappear from the screen (they are not deleted, just not displayed). They have been filtered out.
    4. Choose the menu item Records / Filter / Filter by Form. A new table appears, with 'Like ".gif"' in the Path field. Click or use the right arrow then left arrow and insert "Not " (including the space) at the beginning of this field. (Should read 'Not Like ".gif"'.) Now choose the menu item Filter / Apply Filter/Sort. The AccessLog table appears, but with all of the "gif" paths filtered out.
    5. Repeat 2, 3 and 4 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.
    6. 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.
    7. 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.