Last updated: 12/6/00
Link back to course Welcome

Using MS Access

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)

2. Access opening screen
   
   Accept the default and click OK to get the File / Open dialog, which is also available using the folder icon or the File menu
3. A database must have at least one table, but it can have many. The tables for the conferencing system database are shown below. This view of a database is called the Database Window
   
4. To open a table in the rows and columns view (datasheet view), click on it to select it and click the Open button (or double-click the table
   
5. Tables
   1. 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 open a table in Datasheet view, click on the table you want, to select it, and then click on the "Open" button
      2. To insert a new field in Datasheet view, chose the menu item Insert / Column
      3. To change a field name in Datasheet view, double-click on the name (highlights the field name), type the new name, and tap <Enter>
         
      4. 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
            
      5. Most 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, but not data entry.
   2. 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
         
      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
6. Open the database file with the changed name, and add two records to it.
7. Working with a large file. Databases are not really important until the files get large.
8. Go to the course web site, find the agenda for this class (Agenda 7) and under that, the link for AccesLog.mdb. Click on the link to download the file and save it in a place you remember on the C: drive. Once you have saved it, open it in Access using method A or method B
   1. Start Access, and open the file
      OR
   2. Locate the file in Windows Explorer and double-click on it to start Access and open the file
   3. 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 then end of November 2000. 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.
   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.
         
      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 Excluding Selection. All of the requests for GIF files disappear from the screen (they are not deleted, just not displayed). They have been filtered out. (NOTE: Using "Filter by Selection" we could also select only the gif files.)
      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. A table with only one row should appear, containing Not Like "*GIF" And Not Like "*jpg" in the "RequestedFile" field.
      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.
   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. 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 (if a dotted box remains, that 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 you see the field name Code.
      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.
         
      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. 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.
   9. 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.)