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!
You 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!
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!
Warning:
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
- From your
resource files, open
gamescores.mdb. This database has 3 tables,
Level Scores,
Levels, and TotalScores with data
already entered.
- Open each table and inspect the fields, field properties, and
values.
- View the relationships.
A relationship already exists between
Level Scores and
Levels.
- Backup database: From the menu select | 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??)
- Change the Time field's Format property to
Short Time.
- 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.
- 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?
- 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.
- 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.
- 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!
- 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.
- Save the table. You get a 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!
- 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.
- Change the name of the table to
LevelScores.
It is easier to manage expressions when the object names do not have
spaces.
- Open the Relationships window.
Whoops. The LevelScores table is
missing! When you changed the name, Access dropped it from the
Relationships diagram.
- 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!
- 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.
- 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
- 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.
Save the changes to the
Relationships window.
- 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.
Problem: 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.
- From the menu select |, and click on the General tab to see the
current file size.
- From the menu select ||
Access will close the database, make a new copy with the same name, and
reopen the database. All the wasted space should be gone.
- 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?
- Create a new query that includes the tables LevelScores
and Levels.
- 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.
- Add criteria to find null values for
FinishTime.
Uncheck the Show box for the
FinishTime field.
Run
the query. Which levels do not have a FinishTime
value? (There are 3.)
- Change the query to find records that do not have a Score
value.
- 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!
- Save
your query as QNullScores.
Question: What was the highest score for any level?
- Create a new query based on the table LevelScores.
You can use the Max function to find the largest score.
- Drag the * down to the grid and also drag the field
Scores separately.
- Click the Totals button.
Whoops. Access won't let you use the * to include all fields and then
use the Totals row for anything.
- Delete the * column, leaving only the
Scores column, and click on Totals again.
The Totals row appears.
- Select the Max function.
- Run
the query. The datasheet has one value, MaxOfScore = 26620. That's the
best score for any single level in the database.
- Save
your query as QMaxOfScore.
Question: What are the highest scores for each level?
- Add the table Levels to the
query QMaxOfScore.
- Drag the field LevelName from the
Levels table to the grid.
- Leave the Totals row value as Group By for the added field.
- 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?
- 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?
-
From the menu select | and save
your query as QMaxOfScore-eachLevel.
If
you click the Save button on the toolbar, Access will overwrite the
original query!
- 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!
- In Design view, choose Sort Ascending for the column
Score.
- Run
the query.
With the values sorted, it is easy to see which is the lowest and which
is the highest value.
- Save
the query with the same name. (QMaxOfScore-eachLevel)
Question: What is the
fastest time for a level?
- Create a query based on LevelScores.
Include only the field FinishTime.
- Run
the query.
- Sort the FinishTime column in
ascending order.
The first three records are blank. The shortest time that was recorded
is 14 seconds. (0:14)
- Save
the query as QFinishTimes-min.
- In Design view, show the Totals row by clicking the Totals
button.
- 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.
- Save
the query and close it. (QFinishTimes-min)
Question: Which times are faster than the AceTime?
- Create a new query based on the LevelScores
and Levels tables.
Include the fields TotalsID,
Stage, and
Level, in that order.
- Sort all three fields ascending.
- 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")
- Run
the query.
- 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?
- 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.
- Add a calculated field to the query grid named
Diff, which subtracts
AceTime from FinishTime.
Diff: [FinishTime]-[AceTime]
- 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!
-
In Design view, show the Totals row and change the
Diff column to Count. Leave the other three
columns at Group By.
- 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.
- On the Criteria row in the Diff column, type
>0. The Diff value
will be positive when the FinishTimeis
greater than the AceTime.
- 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.
- 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.
-
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.
-
Save the query as QSlowFinishTime-levels.
- Close the query and reopen it.
Surprise! Access has modified your design.
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.
- View the current file size from Database Properties.
- Compact and repair the database.
- 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
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
-
Open
the Relationships window.
Print the Relationships report from the Relationships window.
Datasheets
-
Open the datasheet for the table
TotalScores. Check the Print Preview. The table does not fit on 1
page with the default settings.
- Make changes to the orientation, margins, or column widths until the
table will fit on one sheet of paper.
-
Print the datasheet for the table
TotalScores.
-
Open the query QSlowFinishTime-levels.
-
Print the datasheet for the query
QSlowFinishTime-levels. No changes are needed since this
datasheet fits nicely on a page.
Documenter Reports
- Open a Documenter report for the table
LevelScores.
-
Print the 4-page report.
- 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
- Open a Documenter report for the query
QSlowFinishTime-levels.
-
Print page 1 of the 3-page report.
- Highlight or circle in the report the following information:
- Page number
- Definition of the calculated field Diff
- Criteria used
Object Dependencies
-
Select 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.)
Print
the screen shot.
Save
in GIF format as ex3-1-dependencies.gif to the
folder databases project3 on your Class disk.
How to handle a full disk
