|
Exercise Database 3-2: Language Arts Grades
What you will do: |
Rename the database
Create relationships
Create new tables
Change field properties
View subdatasheets
Create a select query with criteria
Create a parameter query
Create calculated fields with text
Use IIF
Create an update query
Create a Totals query with parameters
Create a Crosstab query
Use calculated fields with numbers
Create a MakeTable query
Create an Append query
Delete a table
Delete a relationship
Repair a broken query
Use
the Round function
Compact and repair the database
Document the database |
Start with:
Access open
We have been using the LanguageArtsClasses database as an
example. The original of this database was not actually "designed"; it
just grew. So there are a number of things that could have been done
better. (Yes, I say this even though I wrote it myself!) In this exercise
you will start with some of the tables and create some of the queries
needed to calculate grades.
Some features of these tables are chosen to make it easier to work
directly with the tables. If we were going to work primarily with forms,
we might choose differently.
Renaming Database
Recall that from inside a database you cannot change the file's name.
There is no Save As command for the whole database, just for objects
inside the database. You must rename it outside
of Access, while it is closed.
You need a copy of one of the databases in your resource files. This
database contains some of the tables from the original LanguageArtsClasses database but none of the queries, forms, or
reports.
If you do not have a local copy of the resource files:
-
Download the file
languagearts-tablesonly.mdb from the
resource files and save it as
LanguageArts-ex2.mdb.
If you already have a local copy of the resource files:
-
Navigate in a My Computer window to where you saved the resource files
to your hard disk.
-
Select the file languagearts-tablesonly.mdb in a My Computer window and
copy it.
-
Navigate to your Class disk to the folder databases
project3 and Paste.
-
Rename the file as
LanguageArts-ex2.mdb
-
Double-click the new name to open the database in Access.
Design with Relationships in Mind
-
Open the Relationships window and add all of the
tables to the window. Drag the borders of the tables until all fields
and table names are showing. Drag tables by their title bars until they
are in approximately the arrangement in the illustration.
-
Create
relationships between the following tables:
Students -
Scores, matching StudentID fields
Students - Six
Weeks Grades, matching
StudentID fields.
Students -
Bonus Points, matching
StudentID fields
Scores - LetterGrades, matching
LetterGrade fields
Scores - Assignments/Tests, matching A/T
ID fields
Scores -
Classes/Subjects, matching C/S ID
fields
Try to create relationships:
Assignments/Tests
and Graded Work Types on
the Type field.
Classes/Subjects and
Students on the
Class field.
Look carefully at the Edit
Relationship window. It shows that the relationship for both of these is "Indeterminate".
Whoops. That means that you can create a relationship BUT the values are not unique. Access cannot match
them with any certainty. Not a good idea!
(If you already created these two relationships, delete them now.)
You can fix these glitches, first by changing a table's design
and then by creating some new tables.
-
Save
the layout by clicking the Save button on the toolbar.
Leave the Relationships window open.
Change Table Design
The field Type in the table Graded Work Types
cannot be matched with the field Type in the table
Assignments/Tests because the values are not required to
be unique. You can fix that.
-
Open the table Graded Work Types
in datasheet view. It lists the types of assignments that there are.
This will be important to know when you calculate grades. Since the
entries are unique, you do not actually need the TypeID field after all.
-
Switch to Table Design View.
-
Delete the TypeID field and make the
Type field the primary key.
Now the
values are required to be unique, which they are already.
-
Save the table.
-
Switch back to the Relationships window and create a relationship
between the Graded Work Types and
Assignments/Tests tables, matching
the Type field. The table list change
automatically when you saved the table. Hurrah! No problem this time!
One problem solved.
Create New Tables & Relationships
-
Open the table Classes/Subjects
in Table Datasheet View.
The datasheet shows the combinations of class (A, B, D) and subject (R for
reading, S for spelling, and E for English). Each of the three classes
studies each of the three subjects.
-
Create a new table Classes with just one
field, Class, with field size 1. Make it, of course, the
primary key. Enter the values A,
B, and D as the three records in the
field. (I have no idea why there was not a class C!)
-
Create a new table Subjects with just
two text fields, Subject and
FullSubject. Make Subject the primary
key with a Field Size of 1. FullSubject
should have a Field Size of 10. Enter the values R
and Reading, S and
Spelling, E and
English for the three records.
-
Switch
to the Relationships window and add the two new tables to the
display.
-
Create relationships as follows:
Classes/Subjects -
Subjects, matching the
Subject field.
Students -
Classes, matching the Class field
-
Save
the layout and leave the Relationships window open.
Exploring the Tables
& Relationships
Find the answers to the following questions by viewing the
relationships or the various tables in datasheet or design view. Record
your answers as your instructor directs.
-
Which relationships are One-to-Many? Which are One-to-One?
(Right click on a join line to open the Edit Relationships window)
-
Predict, based on the relationships, which tables will have subdatasheets
and what table that data will come from. Then look at
the subdatasheets and see what actually shows.
-
Which tables have Lookup fields? What are those fields? How can
you tell it is a Lookup field?
-
Which fields in which tables logically could be Lookup fields but aren't?
Edit Properties
After EACH change below, save the table. (If you make several changes
and then find out that there is a problem, you won't know which change
caused the problem!)
You will often see a warning that some data may not fit the
new rules. Access hasn't looked yet! Let Access check the data.
If Access warns you that some data DOES violate the new validation
rule or other settings, cancel the Save process and inspect the data.
You will know that the problem was caused by your latest change to the
table design.
Make corrections to the data or table design, if necessary. For example, if a field is null that is
now required to have a value, you should figure out what the
appropriate value should be and enter it. Then you will be able to make
your change.
You may need to temporarily delete a relationship in order to make
some changes. You may need to close and reopen the Relationships or
table design windows to make Access notice changes in relationships. Just be sure to re-create the relationship after you
have corrected the problem.
Set the following table and field properties.
Hint: Validation Rules must
be expressions, not sentences!
Table: Assignments/Tests
- Table Property:
Validation Rule - requires the date assigned to be
after the date due or on the same day
- Table Property:
Validation Text = Date Due should
be later than Date Assigned or on the same day
- Subject:
Field Size =
1
Format: Formats as upper case
Validation Rule - requires the values R, S, or E
Validation Text = Choose R for Reading, S for
Spelling, or E for English
Let Access test the validation rule. Right click on the title bar
in Table Design View and choose Test Validation Rules. You get a warning
that data in Subject does not fit, but no hints as to which record(s)
are the problem.
Assignment #65 does not have a value for Subject. Judging from the
description, the assignment was in English.
Do not correct this record now. You will make a correction
later.
- Type:
Field Size =
1
Format: Formats as upper case.
Validation Rule -requires using one of the values D, Q, T, B, or W
Validation Text =
Choose D for Daily grade, Q for Quiz, T for Test, B for book report, W
for writing assignment
- Date Assigned:
Input Mask = Short Date from the wizard
(You may need to install the wizards from your installation disk.)
- Date Due:
Input Mask = Short Date from the wizard
- Max Score:
Validation Rule requires a score less than or equal to 100.
Validation Text = MaxScore must be less than or equal
to 100
Default Value = 100
- Grading Period:
Validation Rule - requires the value to be 1, 2, 3, 4, 5, or 6
Validation Text = Choose the grading period, 1, 2, 3,
4, 5, or 6
Required = Yes
Table: Scores
- Score:
Validation Rule - requires a value less than or equal to 110 (100 plus
any bonus points) or else be NULL.
Validation Text should explain this rule.
- Ignore?
Validation Rule - should require the value to be a slash (/) or to be
NULL.
Validation Text - should explain that a slash means to ignore the value
when averaging grades. (This field could have been a Yes/No field.)
- LetterGrade:
FieldSize = 1
Validation Rule - should require a value of A, B, C, D, or F or else be
NULL.
Validation Text - should explain this rule.
(The values in this field are created by an series of update queries.)
Table: Students
- Class:
Format property should force upper case.
Lookup- Display Control is a List Box. The Row Source is the table
Classes. Bound Column is 1.
- BirthDate:
Input Mask uses Short Date, from wizard.
-
Save
all of the tables and close them
If you had to delete any relationships in order to make changes, go
back to the Relationships window and re-create them.
Create Queries
Now that the tables are organized, you can start to work on queries
that answer
questions
about the students and their grades, especially
that most important question- "What's my
grade?" That question is actually several questions since each student
has scores for quizzes, daily assignments, and tests in three subjects.
There are also averages for the six weeks grading period and for the year. Whew!
Do
not use the Save button on the toolbar to save your
changed queries. That will overwrite the original. Use File | Save As.
- Select query: Create a Select query based on the Students
table. Include the fields StudentID,
LastName, FirstName,
Class. Sort alphabetically on LastName
and then FirstName. Save the query.
Save
the query as
QSortNames,
Results: a datasheet listing all students in alphabetical order,
with last name first.
- Parameter query: Change the query QSortNames to a
parameter query which allows you to select a Class. Include in your
parameter entry the choices for values for a Class: A, B, or D.
Save the query as QSortNames-PickClass.
Results: records for only one class.
- Calculated text field: Add a calculated field called FullName
to the query QSortNames-PickClass.
The field combines fields to produce values like McCoy,
Molly. Show only the
StudentID, Class,
and FullName fields in the datasheet.
Save the revised query as
QFullNames-PickClass.
Results: records in alphabetical order for only one
class; showing the ID number, class, and full name of the student.
- Remove the parameter from the query. Change the query to sort
by Class and then by name.
Save the query as
QFullNames.
Results: records are grouped by class and alphabetically sorted by name
within each class.
- Conditional Expression: The Called field
contains what the student
wants to be called by if not by their official first name. Change the
calculated field FullName to use the IIF
function. You want to use the value in the
Called field if there is one instead of the value in
FirstName. Otherwise you will use
FirstName to calculate the
FullName.
Save the query as
QFullNames-withCalled.
Results:
Only a few FullName values have changed. You should see records for Buck,
Allen and Greenfield, BJ, for example,
instead of Buck, William and
Greenfield, Beatrice.
Figuring Up Grades
A 6-weeks grade is a weighted average of the average daily, quiz, and test
grades for that subject. The daily grade may have bonus points added. So
we have several calculations to do!
Queries with Criteria
You will start with a fairly simple query and then add columns or other
features as we go along. You will
need a crosstab query to actually average the grades.
- Create a new query based on the
Scores and Classes/Subjects
tables. Include the fields A/T ID,
Score, and Ignore? from
Scores. Include the fields
Class and Subject
from Classes/Subjects.
- Sort ascending the columns Class,
Subject, and A/T ID,
in that order.
- Criteria: In the Ignore? column, write in
the Criteria row an expression that matches null or empty cells.
(There are 144 scores with a slash in the Ignore?
field.)
Run
the query.
You should see 7301 records grouped by class and subject, with
A/T ID in numerical order. The Ignore? column should be
blank for all these records. This is important!
- Add the table Assignments/Tests
to the query and join it, if necessary, to
Scores using the field
A/T ID. Include the fields Type and
GradingPeriod on the grid.
In the Criteria row for the Type column,
type Q, which stands for quiz.
Run
the query.
You should see 1561 scores for quizzes in English and Reading. There are
no quizzes for Spelling.
- Add the query QFullName-withCalled
to the query. Match the StudentID field with
the one in the Scores table. Include the
field FullName on the grid.
Run
the query.
You should see a student's name in each row. There are 1552 records. We
lost 9 scores. Why?? Let's investigate.
The joins between tables are of the type that include records only when
there are matching records. In other words, if one table has a record
that has no matching records in the other table, it won't show in the
query results. So, a student with no score for a particular assignment
won't be listed for that assignment. Neither will scores that don't
match a student. (Having a score without a student is a bad data entry
error!)
Problem:
You had 143,612 records in the query results instead of 1552.
You did not create the join between the query and the Scores
table. Access showed the cross-product of the query and the other
results (all possible combinations!). This query takes noticeably longer
to run.
- In the Design view, change the join between
Scores and the query
QFullNames-withCalled to the third type,
"Include all records from Scores and only those records from
QFullNames-withCalled where the joined fields are equal".
Run
the query. Sort the datasheet ascending on the FullName column.
At the top of the sorted column are 9 rows with only a comma in the FullName
column. Strange! You have some scores that don't match with a student.
Not a good!
- Add the field StudentID from the
Scores table to the query and sort
ascending on the FullName field.
-
Run
the query again.
Ah ha! Those blank records have a StudentID of 1,
which is not a normal ID number. If you check the
Students table, you will find that
there is no student with that ID number.
This glitch in data entering was my fault. A new student started class
before I got her records, including her official ID number. I used 1
for her ID as a temporary number and changed the ID number later. I did
not change her scores to include
her actual ID number. Big whoops! Those scores would not be
counted when averaging her grades. (In real life I calculated her grades
correctly, of
course!)
Problem:
StudentID does not show 1
You have used the StudentID field from the query
QFullNames-withCalled instead of from
the table
Scores. That query uses the
StudentID field from the
Students table. The value "1" appears only in
the Scores table.
- In the query datasheet, change the
StudentID for the first 9 records from 1
to 40102. Once you enter the
number and leave the field, the student's name appears in the FullName
column.
Keyboard
shortcut to copy a value from the cell above in datasheet: CTRL + '
You can type in the value in the record at the top of the datasheet, use
the arrow key or Enter to move to the cell below. Hold CTRL and press
the apostrophe key. The value above is copied.
Problem:
Don't see a name in the FullName field after changing ID number
Make sure you put the correct number in and that you have exited the
cell.
- Remove the sorting from the StudentID column.
Save
this query as QScores-Quiz.
Update Query
Let's stop a minute and handle the problem of that student whose
StudentID number was 1. You just changed the 9
quiz scores but there may be other types of assignments and tests that
need to update the StudentID.
An update query will quickly find and replace
all of those values with the correct ID number in the
Scores table.
- Create a new Select query based on the
Scores table. Include the
StudentID field and the
ScoreID field.
- Type 1 in the Criteria row
for the StudentID column.
Run
the query.
You should see a datasheet of 33 records with this ID number.
- In Design view, change the query to an Update query.
- In the Update to: row, enter the value 40102.
Run
the Update query. You will update 33 rows. Hurrah! That was not too
painful.
Save
as QUpdate-StudentID.
This query is not one that you would normally bother to save. It is a
one-time use type of query. You are saving it here for the benefit of
your instructor (and your grade)!
Comment on design: I did not think ahead! If the join between the tables
Students and
Scores allowed cascading updates, we
would not have had this problem to start with. When I changed the student's ID
number in the Students table,
Access would have automatically updated all of the records in
Scores and any other other table that had
a join to
Students that allowed cascading updates.
Totals Query with Parameters
Now you can calculate the average of the quiz scores.
- In the query QScores-Quiz,
show the Total row.
Keep the sorting and criteria.
-
Save the query as QScores-Quiz-parameters.
- Set the values in the Total rows as follows:
Score - Avg
Ignore? - Where
The other columns can keep the default value of Group By.
Delete the column A/T ID.
Add Sort Ascending for the Grading Period and FullName columns.
Rearrange columns if necessary to have the sorting columns in the order
Class, Subject,
Grading Period, FullName. (They do not
have to be side-by-side.)

Run
the query.
The AvgOfScore column shows the average of the quiz scores for each student for
each of the two grading periods. These are ordered by Class and Subject
and Grading Period and then FullName. So the first 128 records are for
students in class A.
- Add parameters to Subject
and GradingPeriod columns:
[Which subject - R or E?]
[Which grading period - 5 or 6?]
(The tables only have records for the last two 6-weeks grading
periods.)

Run
the query
Make choices as you like.
The datasheet now has scores only for one subject in one
grading period. There are 91 records for the 5th grading period and 92
for the 6th.
-
Save the query. ( QScores-Quiz-parameters)
Clearly you can manipulate the data in many ways with queries. You can
add criteria and parameters to restrict the results. You can do sums and
averages and counts using the Total row.
Averages Query
The first step toward averaging grades is to get individual averages
for each subject and grade type for each student. The query above did
that just for quizzes.
Each student should have an average for 8 different SubjectType values, RD, RQ, RT, SD, ST, ED, EQ, ET
for the grading period.
- Create a new query based on the tables
Students,
QFullNames-withCalled, Scores,
Assignments/Tests.
- Include the fields:
Class and StudentID
from the Students table
FullName from the query QFullNames-withCalled
Score from
Scores
Grading Period from
Assignments/Tests
- Create a new field in the query
SubjectType:[Subject]&[Type] which will identify each of the
averages, like RD for average of the Reading daily scores.
-
Run
the query with criteria in the Grading Period column = 6 (for the
6th grading period) and then again with 5. The results should be
different.
Results: Each student does have an average for 8 different SubjectType values, RD, RQ, RT, SD, ST, ED, EQ, ET.
-
Leave the datasheet open, with the 5th term averages showing.
Why? Because we are going to construct query using this query.
-
Save the query as QAverageScores.
Repair Data Entry Problem
You found a data problem earlier but were not allowed to fix it. Now it
is time!
-
Sort Ascending the SubjectType column in the datasheet for
the 6th grading period.
There are
92 rows with a SubjectType of just D. What happened??
Data entry error strikes again! This time I did not enter a subject for
the assignment. How to fix this? First you must identify the bad
assignment record!
- Leave the query open in Datasheet View.
- Open the Assignments/Tests
table and sort the Subject column.
Aha! A/T ID 65 is the problem record. It's
Subject field is blank. You found this problem when checking the
validation rules earlier but were not allowed to fix the record. From the Details field it is clear that this was
an English assignment.
- Type an E in the Subject field and
exit the field.
Save the table and close it.
- Inspect the datasheet that you left open. It changed!
Problem:
Datasheet did not change
No changes? Not to worry! Switch back to Query Design View and run
the query again.
Results: All rows have the correct SubjectType combo. Hurrah!
Note: You may have seen the screen flicker after you typed in the E and
exited the field. Access was updating the Score records.
Crosstab Query
Crosstab queries are just what you need to do the averages wholesale
instead of one type at a time or one subject at a time. It will take two
queries to get what we really want. The first one is a crosstab query
that calculates the daily, quiz, and test averages for each subject. The
second query will take those values and calculate the 6-weeks grade using
formulas that gives different weights to the parts.
- Back in Query Design View, change the query QAverageScores to a Crosstab
query.
The query keeps the Criteria in the Grading Period column, so it will
look at the 6th grading period.
- Sort Ascending on Class and FullName
.
- In the Crosstab row, select the following:
StudentID, Class,
and FullName - Row Heading
Score - Value
SubjectType - Column Heading
- In the Total row, set Score to
Avg and leave the rest as Group By.
-
Run
the query.
Results: Each row is for one student and shows the averages for
each type and subject for the 6th grading period. Cool!
Save and close the query. (QAverageScores)

Perhaps it is just me, but I really don't like seeing all of those
decimal places. It's not worth fixing here. We can wait until we get the
6-weeks grade and then format the numbers.
Calculated Fields for 6-Weeks Grades
The query you just created provides the numbers you need to do the
6-weeks grades. At last! You will create formulas to use those values in
a new query.
Where does the 6-Weeks grade come from?
20% average of Daily scores, plus bonus points
30% average of Quiz scores
50% average of Tests
For Spelling there are no quizzes. The Daily and Test averages are each
50%.
Bonus points are recorded in a separate table. There is one value for
each of the 6-week terms.
Weighted formulas: You will need to replace the words below with
the appropriate field names.
Reading or English average:
((2*(daily average+bonus points))+(3*quiz average)+(5*test average))/10
Spelling average:
(daily average + test average)/2
- Create a new query using the tables
Students and
Bonus Points and the query
QAverageScores.
- Include the following fields:
Class and StudentID
from the Students table
All fields from the crosstab query
QAverageScores, dragging the * from the table to the grid or
double-click the * entry in the list.
- Create the following calculated fields in the query, where
B is for the bonus points:
RAvg: (2*([RD]+[B])+3*[RQ]+5*[RT])/10
SAvg: ([SD]+[ST])/2
EAvg: (2*([ED]+[B])+3*[EQ]+5*[ET])/10
B:5B which will use the bonus points for the 5th 6-weeks in the
formulas above
-
Run
the query.
Results: A row for each student shows the averages for each
subject-type (RD, RQ, RT, SD, ST, ED, EQ, ET), and for each
subject (RAvg, SAvg, EAvg) for the 5th 6-weeks term. Why just
the 5th term? Because the crosstab query
QAverageScores won't let us use parameters.
To reduce the clutter, you need to change which columns show.
- Uncheck the Show box for the * column for the crosstab
query and the B column.
- Drag the
FullName field to the grid.
- Sort ascending the Class and FullName. Be sure that they have
the Show box checked.
-
Run
the query again.
Results: Only 7 columns now - Class,
StudentID, FullName, B, RAvg, SAvg, EAvg.

Save the query as
Q6WeeksGrades-5th.
To change this query to do grades for the 6th grading period, you must
make two changes. The B field must be redefined as 6B and the
criterion in the query
QAverageScores must be changed from 5 to 6. Not a good plan.
MakeTable Query
Those bonus points B are a little bit of a problem. The points vary
with each 6-weeks term. By setting B to 5B, the query can only do the
calculation for the 5th 6-weeks.
The Bonus Points table has a separate column for each 6-weeks term for
each student. Bad design! Better design would have each record include a
student's ID, the number of the term, and the number of bonus points.
Let's move the data into such a table! Action queries to the rescue!
- Create a new query based on the
Bonus Points table.
- Drag the fields StudentID and
5B to the grid.
- Change the query to a MakeTable query. A dialog appears
asking for a name for the new table.
- Type Bonus as the new table's
name.
- Edit the column 5B to read Points:5B,
giving the field a new name in the table you are about to create.
-
Run
the query.
A message tells you that you are about to paste 92 records into a new
table. Just right!
- Click Yes.
Save the query as QMakeTable-Bonus.
(Normally you would probably not want to keep this kind of query)
- Open the new table Bonus from the Database Window. It has two
columns. So far, so good.
That StudentID of 1 is in this table, too. We will fix that later.
- Switch to Table Design view and make the following changes:|
- Make StudentID the primary key.
- Add a new field named Term which is a
number field with size Byte.
- Make the Points field also have a field size of Byte.
Save the table and view the datasheet. You will get a warning
about reducing field size. It's OK!
The new table looks good except that nothing is filled in for the Term.
An update query will take care of that!
- Close the table.
Update Query
You created a new field for the Bonus
table. All of the rows need to show Term value of 5. An update query can
do that lickety-split (meaning very fast)!
- Create a new query based on the
Bonus table.
- Drag the field Term to the grid.
(Just the field that you need to update. No criteria are needed for this
query.)
- Change the query to an Update query.
- In the Update row, type 5. All
records will be updated to this value.
-
Run
the query. You will get a message that you are about to update 92
rows. Exactly right!
- Click Yes.
-
Save the query as QUpdateBonus-5
and close it.
(Normally you would not want to keep this query but your instructor
will need to see that you have done what you were supposed to have done.)
-
Open the Bonus
table in datasheet view. All the rows now show 5 for the term. Hurrah!
Append Query
You need to get the bonus points for the 6th 6-weeks added to the new table
Bonus. An Append query is what you need this
time.
-
Create a new query based on the
Bonus Points table.
-
Drag the fields StudentID
and 6B to the grid.
-
Change the query to an Append query.
-
In the dialog that appears, select the table
Bonus as the one to
which you want to append records.
-
In the Append to: row that appears, select for the column 6B the
field Points.
-
Run
the query.
Whoops. You get an error message.
-
Choose No.
You don't want to append the records just yet. There is a primary key
violation. Double-whoops.
When we designed the
Bonus table, we set StudentID
as the primary key. But now you are appending another set of
points for each student. Primary keys must be unique! You must revise
the table design.
-
Open the
Bonus table in Design view.
-
Add a new field BonusID and make it
an AutoNumber field and the primary key.
-
Save and close the table.
-
Run
the append query again.
This time you get the message that you are
about to append 92 records. Yes!
-
Save the query are QAppendBonus-6.
Close the query.
-
Open the table
Bonus and scroll through the records. You
have another step to do. The values for Term for the rows you just
added are all 0.
Note about the data: All
of the 6th term rows have 0 points. That is correct! I do not recall
why there were no bonus points awarded in the last 6-weeks term.
-
Open the query QUpdateBonus-5
in Design View.
(Be careful to open action queries in Design View unless you really
mean to perform the action now!)
-
In the Update to: row, type 6.
-
In the Criteria row, type 0.
-
View the datasheet before you run the query! You should
see 92 rows only, all with Term = 0.
-
Run
the update query.
-
Close the query without saving changes.
-
Open the table
Bonus and inspect the records. The
Term column now shows either a 5 or a 6.
-
Correct Data Error: That StudentID of 1 is still hanging around! Fortunately that
student earned no bonus points. Change the 1 to 40102 in
this table now.
-
Close the table.
Deleting a Table
You don't need the table
Bonus Points anymore. But, before deleting an object in Access, you should be sure that you
won't be breaking something else! A query, form, or report might use
your object.
Access 2003
shows such dependencies in a task pane. For earlier versions of Access
you will have to have a good memory or do some inspecting.
-
Select the table
Bonus Points in the Database Window.
-
From the menu select |.
Note: You may see a message about updating dependency information.
Choose OK. It won't really take very long in this case.

The task pane opens and shows a tree of
dependencies. What depends on this table? Table
Students,
Q6WeeksGrades-5th, QAppendBonus-6,
and
QMakeTable-Bonus.
Thinking: What problems will it cause to delete the table
Bonus Points?
- The table Students has a
relationship with Bonus Points. You must delete the relationship
before you can delete the table. That is not really a problem. (Access
will make this easy!)
- The query,
Q6WeeksGrades-5th will have to change to use the table Bonus
instead of Bonus Points. This query is used to calculate the 6 weeks
grades. This is the only real problem.
- The other
two queries were used to create and populate the Bonus table to start with and are not
really useful now.
-
Delete the table
Bonus Points.
A message box opens. Access won't let you delete without warnings! There is a relationship between this table and another. You
must delete the relationship first.
-
Click on OK .
A second message box appears and offers to remove the relationship for
you. (You have to read it carefully to see what it actually say!)
-
Click on OK in the second message box, too.
The table is deleted.
-
Open the Relationships window.
-
Add the table
Bonus to the Relationships window and
join it to Students using the StudentID field.
Repair a Broken Query
Now that you have straightened out the bonus points and have deleted
the Bonus Points table, you must fix the query that used that table. It
should use fields from the Bonus table instead. You must make sure that
the expressions to calculate grades can find the bonus point values.
- Open the query
Q6WeeksGrades-5th in Design View.
You get a message that Access cannot represent the join expression. This
is because one of the tables in the join just got deleted! This was
expected.
- Click on OK.
The query's Design view appears, but the Bonus Points table is reduced
to just a title bar in the upper part of the view. It's not in the
database any more.
Problem:
The query failed to open.
If you try to run this query by double-clicking it or using the Open
button, you get an error message. The query won't run at all since the
Bonus Points table does not exist anymore.
- Click on the Bonus Points
table in the upper part of the Design View.
- Drag the bottom edge of the table list to see if there are
any fields listed.
Missing tables show as just a title bar. However, you can resize a
table's list that small. Just because all you see is a title bar is not
a guarantee that the table is gone.
- Inspect the columns in the grid. Which ones rely on fields from Bonus Points?
What change(s) do you need to make. In this case, if you fix the B
column, all the other columns will work fine.
- Delete the Bonus Points
table from the Design View.
- Add the Bonus table.
- Delete the column B: [Bonus Points].[5B]
- Drag the Points and
Term fields to the grid.
- Sort the Term field ascending.
- Arrange the columns so that Class,
FullName, and Term
are in this order. They don't have to be side-by-side.
- Edit the Points column to read B:Points
in the Field row. Now the Points values will be used where B is
in the formulas.
Save
As Q6WeeksGrades.
-
Run
the query.
Results: Sorted by Class, FullName, and Term. Each student has a term 5
and a term 6 row.

Round Function
Now you can attack those ugly decimals. The Round function helps you manage the number of decimal
places showing. The values are rounded, not just cut off.
General form:
Round(expression, number of decimal places to show)
If no number is entered, the Round function will produce
only whole numbers.
Examples:
Round([Average],2) would display 19.23649 as 19.24, rounding
up to show 2 decimal places.
Round ([Price]*1.0925) would display 19.23649 as 19,
rounding down to show 0 decimal places.
- Edit the formulas for RAvg, SAvg, and EAvg to use the Round
function and show 1 decimal place to the right of the decimal point.
RAvg: Round((2*([RD]+[B])+3*[RQ]+5*[RT])/10,1)
SAvg: Round(([SD]+[ST])/2,1)
EAvg: Round((2*([ED]+[B])+3*[EQ]+5*[ET])/10,1)
Be careful to have matching parentheses and to use a comma before
the 1.
(You can copy and paste these expressions if you are careful to copy the
whole expression!)
Save
As Q6WeeksGrades-Round.
-
Run
the query.
The datasheet now shows rounded values. Much easier to read.

If you omit the ,1, you will get values
rounded to whole numbers.
- Close the query.
Compact and Repair the Database
You have done a LOT of work. The file is almost certainly fragmented on
your Class disk.
- Open a My Computer window to your Class disk and the
databases project3 folder. What is the current file size for this
database? Mine was 1668 KB.
- Switch back to Access and from the menu select | |.
Access will close the database first. After a pause while Access is
repairing and rewriting the file, the database will re-open. The larger
the file, the longer the wait. Be patient!
- Switch to the My Computer window again. What is the new file
size? Mine is 1012 KB. That's 60% of what it was. Neat!
What's in the Database Now?

Document the Database
There are far too many tables and queries to document all of them! Some
were saved just so you could show your instructor that you did the work.
You will print just enough of what you've done to show that you know how
to print and use the documentation.
Write your name and "Ex. 3-2" at the top left of each print-out
below.
- Relationships:
Print the Relationships report from the Relationships window in
Landscape orientation.
(Access will not remember this orientation. You must change the defaults
every time. )
-
Table
Datasheet:
Open the Print Preview for the table
Students.
Whoops. A whole row does not quite fit in the width available. A second
page prints to get the rest of the Notes field, even though no text was
actually cut off. Overkill!
Some columns are reprinted on the second page. These columns were
frozen. Frozen columns stay in view even when
you have to scroll the datasheet to see other columns. They are also
printed on all the pages that it takes to print the complete record. It
keeps you from having to tape sheets of paper together to see what
records the additional data goes with.
Change the margins in the Page Setup to half an inch left and
right.
Print
the datasheet of the table Students. (2
pages)
- Documenter report: Table
Print the Documenter report for the
Students table. (6 pages)
Highlight or circle the answers to the following questions
on the report:
- RecordCount for the table
- Data type and Field Size for Class
field
- Related tables
- Type of relationship with Six Weeks
Grades table
- Documenter report: Query
Select the Documenter report for the query
Q6WeeksGrades and change the Options to not print any of the
permission or index information.
Access will remember
your Options choices for the next time your print a Documenter report.
Print
the report. (2 pages)
Highlight or circle the answers to the following questions
on the report:
- Formula that calculates the English average
- Source field and table for the field B
- Field type and size for field EAvg
-
Object
Dependencies:
Show the Object Dependencies task pane for the query Q6WeeksGrades -
Objects that I depend on.
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
to your Class disk in the folder databases project3
as ex3-2-dependencies.gif in GIF format.
(Your print-out will look better if you print before your save in
GIF format.)
|