Access Tables & Queries:
Exercise 3-1

Title: Jan's Illustrated Computer Literacy 101


You need to use what you just learned and what you learned in the previous project, and maybe learn a little more. The exercises with databases will require more steps than in the other lessons!

Complete all parts of the following exercises. Don't forget to backup your Class disk when you have completed the exercises or whenever you stop for the day and saved a document along the way.

This exercise uses a file from the databases resource files. The default location for these files is c:\My Documents\complit101\databases\ You cannot make changes to these files and save them in the same place. Save the changed documents to your Class disk. This keeps the original resource files intact in case you need to start over or another student will be using this same computer.

Full floppy disk How to handle a full disk


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries
    Designing TablesTo subtopics
    Designing QueriesTo subtopics
    Summary
    Quiz
    Exercises Arrow: subtopic open
    Icon: Exercise Ex. 3-1 Game Scores
    Icon: Exercise Ex. 3-2 Language Arts
    Icon: Exercise Ex. 3-3 On Your Own

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



Exercise Database 3-1: Game Scores


What you will do:

Backup a database
Change properties of fields
Create relationships
Compact and repair database
Create select and totals queries with criteria
Create a calculated field in a query
Print datasheets and Documenter reports

Start with:   Access open, gamescores.mdb

People who play games like to keep score. Lots of scores! Some computer games track lots of things during the game but do not save all of that lovely information to compare with your next attempt. You can do that in your own Access database!

Screen shot of Zuma in actionYou will be working with a database that records scores for each of the 76 levels in a popular computer game, Zuma®, and some totals for successfully completed games. The resource file gamescores.mdb provides you with actual scores from actual games, played by me.

(Yes, it was a lot of 'work'. But it was all for YOU! Smiley face  I confess that this was not the first time I played Zuma. It took a lot of practice to get good enough to actually finish all of the levels!!)

If you have never played it, I have written a short explanation: How Zuma works
Disclaimer: I do not have an investment with or any relationship with the creators of Zuma. I just enjoyed the game enough to buy it!

WarningWarning: Playing computer or console games for a long time can damage your wrists and back. Take breaks. Gradually increase your playing time, just like for any other physical skill. Too much practice can hurt! Tendonitis is not fun!

Backup Database with New Name

  1. From your resource files, open gamescores.mdb. This database has 3 tables, Level Scores, Levels, and TotalScores with data already entered.
     
  2. Open each table and inspect the fields, field properties, and values.
     
  3. View the relationships.
    A relationship already exists between Level Scores and Levels.
     
  4. Backup database: From the menu select  File | Back Up Database... Choose to save the backup as gamescores-revised.mdb in your databases project3 folder on your Class disk. The database will close first, create the new file, and then it will try to reopen the original file. Cancel when it gets to that part. Open the file you just created on your Class disk instead.
     
    Alternate method: Copy the file from where you saved the resource files in a My Computer window and paste it to your Class disk folder and then rename it. The file cannot be open in Access when you use this method.

Change Field Properties

Some of the field properties are not particularly well-chosen. You will change some of them. When data has already been entered, some changes are more dangerous than others!

TotalScores table:

About the table: The values in the Time field appears to be clock times, like 2:21:15 AM. They are actually elapsed times, like 2 hours, 21 minutes, 15 seconds. You have two choices - use one of the date/time formats or change to Text type and create your own format. The easy way is to use an existing format! That also allows you to do arithmetic with the times which you cannot do with text data. (Do you see something coming later in this exercise??)

  1. Change the Time field's Format property to Short Time.
     
  2. Save the table. When you change field properties, Access may give you a general warning or two. It will proceed to do whatever you told it to do once you click OK. Access does not actually look at your data before giving the warning. You have to look yourself to see if any of those bad consequences will happen. Sad smiley face
     
  3. Switch to datasheet view and inspect the results.
    You've got hours and minutes but where are the seconds?? Two of the times now look like they are equal. Did you really lose those seconds?
     
  4. Click in a Time cell. The value displayed changes! The Input Mask property applies while you are editing or entering new data. The seconds are there!

    Remember: What Access actually stores is not always what you see!! Times and dates are really just numbers. A time value is the decimal part of a date/time value, while the whole number part of the value is the number of days since the starting date, 12/30/1899. Times without a date attached have zero as the whole number part, like 0.058574.

    Did you see?
    In TotalScores, one game finished much faster than the others with a much lower score. What happened? I thought I had paused the game when the doorbell rang, but the game kept running while I was gone until all my lovely stack of lives was used up. Sad smiley face

  5. Close the TotalScores table.

Levels table:

About the table: The field AceTime shows what time the game designers want you to beat.

You don't have to guess at how big the field sizes should be. You've got actual values to look at! You will now change those field sizes to reduce the size of the database. The larger the database, the more effect field sizes have on the total size of the database and therefore on the speed of actions like sorting and filtering.

  1. In the Levels table, open the datasheet view and look at some of the subdatasheets. A subdatasheet shows all of the records in Level Scores for that particular level. Cool!
     
  2. Back in Design view, change  the Field Size for LevelName to be just large enough to hold the longest level name.
    How many characters is that?? Count the letters in the longest name.
     
  3. Save the table. You get a warning.
    Warning Be sure that the field size is large enough! Access does not look first to see if a problem actually exists, and then warn you. It just gives a general warning and then does whatever you told it to do. If you chose a size of 30 and an entry has 35 characters, the last 5 characters are thrown out!
     
  4. Change the Field Size for the fields Stage and Level from Long Integer to Byte. The size Byte will hold only whole numbers from 0 to 255. Long Integer numbers use 4 bytes. Since our numbers must be between 1 and 13, this will work out great!
     
    Another choice would have been to change these two fields to Text fields with a size of 2 characters. We are not going to do any arithmetic with the stage and level values!

Level Scores table:

About the table: In the Level Scores table, the field LevelID is a foreign key. It is the primary key for the table, Levels. It must be a Long Integer size in Level Scores, even though we know that the numbers only go up to 76 for this game, because the field is that type in the Levels table. That is the nature of keys.

There are some missing values for level scores. Somehow I simply failed to record those levels! I know it is hard to believe, but I cannot deny that those blanks are there. Data entry can be hard. It's not that it is hard to do. It's just hard to remember to do!

You might think that I could reconstruct a missing score by adding up the other levels and subtracting the sum from the TotalScore for the whole game. Sounds good, but... there are bonus points added at the end of a game, so the difference will not be just from the missing level score. <sigh>

We don't need to change any of the field properties but the table name needs a change. Also, to join this table to the TotalScores table, we need to add a field.

  1. Change the name of the table to LevelScores.
    It is easier to manage expressions when the object names do not have spaces.
     
  2. Open the Relationships window.
    Whoops. The LevelScores table is missing! When you changed the name, Access dropped it from the Relationships diagram.
     
  3. Add the LevelScores table back to the Relationships diagram.
    The join you created before comes back. Whew! That's a relief. There was no warning about this effect.

    Inspecting: Do you see a field that you could use to join LevelScores and TotalScores? There is not one! We need to add a field to LevelScores that tells us which TotalScores record the level belongs to. This is a bit backwards! While entering the level scores as you play, there is no record yet in TotalScores for the game since it's not finished! You could look at the TotalScores table to see what the next ID number will be. Awkward! OR you could start a new record in TotalScores without any data. OR you could skip that field in LevelScores and enter the matching number for each level after you finish the game. Perhaps that is the better plan!
     
  4. In Table Design View, add a new field to LevelScores named TotalsID. This field should be a Long Integer type of Number field. Description = Foreign key, from TotalScores table.
     
  5. Enter the following values:
    (Hint: Use the down arrow key to move down the column in the datasheet)

      for records 1 through 76 -       TotalScoresID = 1
      for records 77 through 152 -   TotalScoresID = 2
      for records 153 through 228 - TotalScoresID = 3
      for records 229 through 271 - TotalScoresID = 4
      for records 272 through 347 - TotalScoresID = 5
     
  6. Create a relationship between LevelScores and TotalScores using the field TotalsID. (You will have to drag the bottom edge of the table in the Relationships window to see the field you just added.)
    Rearrange the tables in the Relationships window so that the join lines are short and direct.
     
  7. Icon: Save Save the changes to the Relationships window.
     
  8. View the datasheet for the table TotalScores. Open the subdatasheets which show the levels that are part of this completed game. These subdatasheets are long. Click in one of the cells and a vertical scroll bar appears for the subdatasheet.
     
    Icon: TroubleProblem: No subdatasheets in the TotalScores table
    Access needs to refresh its understanding of the table after you create a new relationship.
          Solution:
    Close the table and open it again.

Compact and Repair Database

Since you reduced some field sizes, it is likely that the database file is taking up more room on the disk than necessary.

  1. From the menu select  File | Database Properties , and click on the General tab to see the current file size.
     
  2. From the menu select  Tools | Database Utilities | Compact and Repair Database...
    Access will close the database, make a new copy with the same name, and reopen the database. All the wasted space should be gone.
     
  3. View the Database Properties, General tab to see the new file size. It should be noticeably smaller.

Queries

Now that your tables are neatly related to each other, you can create some queries to answer some questions!

Question: Are there missing scores or finish times?

  1. Create a new query that includes the tables LevelScores and Levels.
     
  2. Drag the * from the table field list to the design grid for each table. Also drag the field FinishTime from the LevelScores table to the grid.
     
    When you use the * to include all fields, to use criteria you have to add fields separately.
     
  3. Add criteria to find null values for FinishTime.
    Uncheck the Show box for the FinishTime field.
     
  4. Icon: Run Run the query. Which levels do not have a FinishTime value? (There are 3.)
     
  5. Change the query to find records that do not have a Score value.
     
  6. Icon: Run Run the query. The datasheet shows only 1 record!
    Why? Two records had score of zero, which is not the same as Null. A Null values means that no value has been entered. Zero means "0" was entered. An important difference!
     
  7. Icon: Save Save your query as QNullScores.

Question: What was the highest score for any level?

  1. Create a new query based on the table LevelScores.
    You can use the Max function to find the largest score.
     
  2. Drag the * down to the grid and also drag the field Scores separately.
     
  3. Click the Totals button.
    Whoops. Access won't let you use the * to include all fields and then use the Totals row for anything.
     
  4. Delete the * column, leaving only the Scores column, and click on Totals again.
    The Totals row appears.
     
  5. Select the Max function.
     
  6. Icon: Run Run the query. The datasheet has one value, MaxOfScore = 26620. That's the best score for any single level in the database.
     
  7. Icon: Save Save your query as QMaxOfScore.
     

Question: What are the highest scores for each level?

  1. Add the table Levels to the query QMaxOfScore.
     
  2. Drag the field LevelName from the Levels table to the grid.
     
  3. Leave the Totals row value as Group By for the added field.
     
  4. Icon: Run Run the query.
    The datasheet shows the maximum value for each of the named levels. The levels are in the same order as they were entered in the Levels table.
    Can you tell quickly which of these "highest" values is the smallest?
    An easier way exists so don't save yet!

Question: Which level has the smallest of the high scores?

  1. In the datasheet for the query QMaxOfScore, click in the the MaxOfScore column and then on the Sort Ascending button on the toolbar. Which level has the smallest score?
     
  2. Icon: Save From the menu select  File | Save As  and save your query as QMaxOfScore-eachLevel.
    WarningIf you click the Save button on the toolbar, Access will overwrite the original query!
     
  3. Switch to Design view and back to Datasheet view. The sorting you applied with the button is gone. To have sorting automatically applied each time you run the query you can change the query just a little!
     
  4. In Design view, choose Sort Ascending for the column Score.
     
  5. Icon: Run Run the query.
    With the values sorted, it is easy to see which is the lowest and which is the highest value.
     
  6. Icon: Save Save the query with the same name. (QMaxOfScore-eachLevel)

Question: What is the fastest time for a level?

  1. Create a query based on LevelScores. Include only the field FinishTime.
     
  2. Icon: Run Run the query.
     
  3. Sort the FinishTime column in ascending order.
    The first three records are blank. The shortest time that was recorded is 14 seconds. (0:14)
     
  4. Icon: Save Save the query as QFinishTimes-min.
     
  5. In Design view, show the Totals row by clicking the Totals button.
     
  6. Use the Min function to find the shortest time.
    Whoops! The value shown is 12:14:00 AM! Where did THAT come from?
     
    About Time values: (Working with dates and times is tricky!!)

    Access records time values as the decimal part of a number where the whole number represents the date. It does its arithmetic with those decimal numbers and then re-interprets the result according to the format that you chose. Access thinks that your 0:14 is a Date/Time value for 14 minutes after midnight!
     
    Access does not have special formats or functions for handling elapsed time! You could write a Visual Basic function to do this, but that is beyond the level of these lessons!! It's amazing how difficult some ordinary things can be to do on a computer.
     

  7. Icon: Save Save the query  and close it. (QFinishTimes-min)

Question: Which times are faster than the AceTime?

  1. Create a new query based on the LevelScores and Levels tables.
    Include the fields TotalsID, Stage, and Level, in that order.
     
  2. Sort all three fields ascending.
     
  3. Create a new field in the query named Faster, using a conditional expression to display the phrase "Faster!" if the FinishTime is faster than the AceTime and the phrase "Too long" otherwise.
    Faster: IIf([FinishTime]<[AceTime],"Faster!","Too long")
     
  4. Icon: Run Run the query.
     
  5. Icon: Save Save the query as QFasterThanAceTime.
     

Question: How many of the FinishTime values are slower than the AceTime for each level? For which levels is it the hardest to beat the AceTime?

  1. Create a new query based on the LevelScores and Levels tables.
    Include the fields Stage, Level, and LevelName (in that order) and sort ascending all three fields.
     
  2. Add a calculated field to the query grid named Diff, which subtracts AceTime from FinishTime.
     Diff: [FinishTime]-[AceTime]
     
  3. Icon: Run Run the query.
    You should see 347 records with both positive and negative Diff values, all of which are decimals. Those messy decimals are ugly!
     
  4. Query Design View: Calculated field named Diff and Count as TotalIn Design view, show the Totals row and change the Diff column to Count. Leave the other three columns at Group By.
     
  5. Icon: Run Run the query.
    The Diff column now shows a count of the number of Diff values for each Stage/Level combination. Not too helpful. It does show that some values are missing. Now you can add criteria to pick out the times that did not beat the AceTime.
     
  6. On the Criteria row in the Diff column, type >0. The Diff value will be positive when the FinishTimeis greater than the AceTime.
     
  7. Icon: Run Run the query.
    There is no change! What happened?? The query cannot filter with the criteria and use the Count function on the same column. You need to repeat the column so Count can be in one and the criteria in the other.
     
  8. Create another column with the same definition for the field as the Diff column and the same expression in the Criteria row. In the Total row choose Where instead of Count.
    You can delete the expression >0  from the column with Count.
    Query Design View: Diff column with Count and 2nd Diff column with criteria
     
  9. Query Datasheet View: Diff column counts the number of instances where FinishTime is larger than AceTimeIcon: Run Run the query.
    The results datasheet has 29 rows that count the number of times Diff is positive, which means that the FinishTime for that level was larger than the AceTime.
     
  10. Icon: Save Save the query as QSlowFinishTime-levels.
     
  11. Close the query and reopen it.
    Surprise! Access has modified your design.
     
    Query Design View: Access changed the two Diff columns

Compact and Repair Database Again

Since you have added a number of objects (queries) to the database, it is likely that the database file is once again taking up more space than it really needs.

  1. View the current file size from Database Properties.
     
  2. Compact and repair the database.
     
  3. View the Database Properties again to see the new file size. Is it any smaller?

Your Own Queries

Now, create your own queries to find the answers to the following questions:

Question: What is the average of the scores for all the levels?
  
Create QAvgOfScore, which produces just 1 number.

Question: What is the average score for the level that has a name containing "mix"?
  
Create QAvgOfScore-mix (Use the Like operator in the Criteria row. This is a very useful operator when you can't quite remember the text value you are looking for!)

What other questions can you ask and answer with this database?


What's in the Database Now

Database Window: Tables Database Window: Queries
Table Datasheet View: Levels table Table Datasheet View: LevelScores
Table Datasheet View: TotalScores Relationships


Documenting Your Database

There are LOTS of Documenter reports now available to you - one for each table and each query. You can also print datasheets for each of those tables and queries. You can print the Relationships diagram. We won't do all of that! You do need to do a little printing, just to see what the limitations are.

Write your name and "Ex. 3-1" at the top left of each print-out below.
(Unfortunately you cannot configure the page's header to include such information automatically. Wait for Reports!)

Relationships

  1. Open the Relationships window.
  2. Icon: Print Print the Relationships report from the Relationships window.
     

Datasheets

  1. Print Preview: datasheet for table TotalScoresOpen the datasheet for the table TotalScores. Check the Print Preview. The table does not fit on 1 page with the default settings.
  2. Make changes to the orientation, margins, or column widths until the table will fit on one sheet of paper.
  3. Icon: Print Print the datasheet for the table TotalScores.
     
  4. Print Preview: datasheet for QSlowFinishTime-levelsOpen the query QSlowFinishTime-levels.
  5. Icon: Print Print the datasheet for the query QSlowFinishTime-levels. No changes are needed since this datasheet fits nicely on a page.
     

Documenter Reports

  1. Open a Documenter report for the table LevelScores.
  2. Icon: Print Print the 4-page report.
  3. Highlight or circle in the report the following information:
    • Relationship type
    • RowSource for LevelID field
    • DistinctCount for LevelID (look at the indexes)
    • Data type and Field Size for Score field
       
  4. Open a Documenter report for the query QSlowFinishTime-levels.
  5. Icon: Print Print page 1 of the 3-page report.
  6. Highlight or circle in the report the following information:
    • Page number
    • Definition of the calculated field Diff
    • Criteria used

Object Dependencies

  1. Print Preview: object dependenciesSelect the table LevelScores and open the Object Dependencies task pane - Object that depend on me.
    Capture a screen shot of the window and paste it into Microsoft Paint, or similar graphics program.
    Crop the image to show just the task pane.
    (Select just the part you want, copy, open a new blank image and paste.)
    Icon: Print Print the screen shot.
    Icon: Save Save in GIF format as ex3-1-dependencies.gif to the folder databases project3 on your Class disk.
     
 

Zuma® - ©2003-2006 PopCap Games, Inc. Zuma is a registered trademark of PopCap Games, Inc. All rights reserved.