-

Access Tables & Queries:
Exercise 3-2

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016


You need to use what you just learned and what you learned in the previous project, and maybe learn a little more. 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-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
Icon: New SkillUse 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:

  1. 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:

  1. Navigate in a My Computer window to where you saved the resource files to your hard disk.
     
  2. Select the file languagearts-tablesonly.mdb  in a My Computer window and copy it.
     
  3. Navigate to your Class disk to the folder databases project3 and Paste.
     
  4. Rename the file as LanguageArts-ex2.mdb
     
  5. Double-click the new name to open the database in Access.

Design with Relationships in Mind

  1. 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.
     
  2. Relationships window - initial tables and relationshipsCreate 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.
     
  3. Icon: Save 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.

  1. 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.
     
  2. Switch to Table Design View.
     
  3. Delete the TypeID field and make the Type field the primary key.
    Now the values are required to be unique, which they are already.
     
  4. Icon: Save Save the table.
     
  5. 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

  1. 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.
     
  2. 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!)
     
  3. 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.
     
  4. Relationships window - with new tables and relationshipsSwitch to the Relationships window and add the two new tables to the display.
     
  5. Create relationships as follows:
       Classes/Subjects  -  Subjects, matching the Subject field.
       Students - Classes, matching the Class field
     
  6. Icon: Save Save the layout and leave the Relationships window open.
     

Icon: QuestionExploring 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.

  1. Which relationships are One-to-Many? Which are One-to-One?
    (Right click on a join line to open the Edit Relationships window)
     
  2. 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. 
     
  3. Which tables have Lookup fields? What are those fields? How can you tell it is a Lookup field? 
     
  4. 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

  1. Table Property:
      Validation Rule - requires the date assigned to be after the date due or on the same day
  2. Table Property:
      Validation Text = Date Due should be later than Date Assigned or on the same day
  3. 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.
  4. 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
  5. Date Assigned:
      Input Mask = Short Date from the wizard
    (You may need to install the wizards from your installation disk.)
  6. Date Due:
      Input Mask = Short Date from the wizard
  7. 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
  8. 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

  1. 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.
  2. 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.)
  3. 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

  1. 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.
  2. BirthDate:
      Input Mask uses Short Date, from wizard.
     
     
  3. Icon: Save 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!

WarningDo not use the Save button on the toolbar to save your changed queries. That will overwrite the original. Use File | Save As.

  1. 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.
    Icon: Save Save the query as QSortNames,
    Results: a datasheet listing all students in alphabetical order, with last name first.
     
  2. 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.
    Icon: Save Save the query as QSortNames-PickClass.
    Results: records for only one class.
     
  3. 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.
    Icon: Save 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.
     
  4. Remove the parameter from the query. Change the query to sort by Class and then by name.
     Icon: Save Save the query as QFullNames.
    Results: records are grouped by class and alphabetically sorted by name within each class.
     
  5. 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.
    Icon: Save 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.

  1. 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.
     
  2. Sort ascending the columns Class, Subject, and A/T ID, in that order.
     
  3. 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.)
     
  4. Icon: Run 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!
     
  5. 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.
     
  6. Icon: Run Run the query.
    You should see 1561 scores for quizzes in English and Reading. There are no quizzes for Spelling.
     
  7. 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.
     
  8. Icon: Run 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!)

    Icon: TroubleProblem: 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.
     
  9. 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".
     
  10. Icon: Run 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!
     
  11. Add the field StudentID from the Scores table to the query and sort ascending on the FullName field.
     
  12. Icon: Run 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!)
     
    Icon: TroubleProblem: 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.
     

  13. 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.
     
    TipKeyboard 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.
     
    Icon: TroubleProblem: 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.
     
  14. Remove the sorting from the StudentID column.
     
  15. Icon: Save 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.

  1. Create a new Select query based on the Scores table. Include the StudentID field and the ScoreID field.
     
  2. Type  1 in the Criteria row for the StudentID column.
     
  3. Icon: Run Run the query.
    You should see a datasheet of 33 records with this ID number.
     
  4. In Design view, change the query to an Update query.
     
  5. In the Update to: row, enter the value 40102.
     
  6. Icon: Run Run the Update query. You will update 33 rows. Hurrah! That was not too painful.
     
  7. Icon: Save 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.

  1. In the query QScores-Quiz, show the Total row.
    Keep the sorting and criteria.
     
  2.  Icon: Save Save the query as QScores-Quiz-parameters.
     
  3. 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.)
    Query Design View: QScores-Quiz
     
  4. Icon: Run Run the query.
    Average Quiz scores for 5th and 6th grading periods
    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.
     
  5. 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.)

    Query Design View: QScores-Quiz-parameters
     
  6. Icon: Run 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.
     
  7.  Icon: Save 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.

  1. Create a new query based on the tables Students, QFullNames-withCalled, Scores, Assignments/Tests.
     
  2. Include the fields:
     Class and StudentID from the Students table
     FullName from the query  QFullNames-withCalled
     Score from Scores
     Grading Period from Assignments/Tests
     
  3. 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.
     
  4. Icon: Run 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.
     

    Query Datasheet View: QAverageScores for 6th grading period Query Datasheet View: QAverageScores for 5th grading period
     

  5. Leave the datasheet open, with the 5th term averages showing.
    Why? Because we are going to construct query using this query.
     
  6. Icon: Save 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!

  1. 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!
     
  2. Leave the query open in Datasheet View.
     
  3. 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.
     
  4. Type an E in the Subject field and exit the field.
     
  5. Icon: Save Save the table and close it.
     
  6. Inspect the datasheet that you left open. It changed!
    Icon: TroubleProblem: 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.

  1. 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.
     
  2. Sort Ascending on Class and FullName .
     
  3. In the Crosstab row, select the following:
      StudentID, Class, and FullName - Row Heading
      Score - Value
      SubjectType - Column Heading
     
  4. In the Total row, set Score to Avg and leave the rest as Group By.
     
  5. Icon: Run 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!
     
  6. Icon: Save Save and close the query. (QAverageScores)

Query Datasheet: QAverageScores - a crosstab query

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

  1. Create a new query using the tables Students and Bonus Points and the query QAverageScores.
     
  2. 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.
     
  3. 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
     
  4. Icon: Run 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.
     
  5. Uncheck the Show box for the * column for the crosstab query and the B column.
     
  6. Drag the FullName field to the grid.
     
  7. Sort ascending the Class and FullName. Be sure that they have the Show box checked.
     
  8. Icon: Run Run the query again.
    Results: Only 7 columns now - Class, StudentID, FullName, B, RAvg, SAvg, EAvg.
    Query Datasheet View: Q6WeeksGrades-5th
  9. Icon: Save 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!

  1. Create a new query based on the Bonus Points table.
     
  2. Drag the fields StudentID and 5B to the grid.
     
  3. Change the query to a MakeTable query. A dialog appears asking for a name for the new table.
     
  4. Type Bonus as the new table's name.
     
  5. Edit the column 5B to read  Points:5B, giving the field a new name in the table you are about to create.
     
  6. Icon: Run Run the query.
    A message tells you that you are about to paste 92 records into a new table. Just right!
     
  7. Click Yes.
     
  8. Icon: Save Save the query as QMakeTable-Bonus. (Normally you would probably not want to keep this kind of query)
     
  9. 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.
     
  10. 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.
       
  11. Icon: Save 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!
     
  12. 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)!

  1. Create a new query based on the Bonus table.
     
  2. Drag the field Term to the grid. (Just the field that you need to update. No criteria are needed for this query.)
     
  3. Change the query to an Update query.
     
  4. In the Update row, type 5. All records will be updated to this value.
     
  5. Icon: Run Run the query. You will get a message that you are about to update 92 rows. Exactly right!
     
  6. Click Yes.
     
  7. Icon: Save 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.)
     

  8. 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.

  1. Create a new query based on the Bonus Points table.
     

  2. Drag the fields StudentID and 6B to the grid.
     

  3. Change the query to an Append query.
     

  4. In the dialog that appears, select the table Bonus as the one to which you want to append records.
     

  5. In the Append to: row that appears, select for the column 6B the field Points.
     

  6. Icon: Run Run the query.
    Whoops. You get an error message.
     

  7. 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.
     

  8. Open the Bonus table in Design view.
     

  9. Add a new field BonusID and make it an AutoNumber field and the primary key.
     

  10. Icon: Save Save and close the table.
     

  11. Icon: Run Run the append query again.
    This time you get the message that you are about to append 92 records. Yes!
     

  12. Icon: Save Save the query are QAppendBonus-6. Close the query.
     

  13. 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.
     

  14. 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!)
     

  15. In the Update to: row, type 6.
     

  16. In the Criteria row, type 0.
     

  17. View the datasheet before you run the query! You should see 92 rows only, all with Term = 0.
     

  18. Icon: Run Run the update query.
     

  19. Close the query without saving changes.
     

  20. Open the table Bonus and inspect the records. The Term column now shows either a 5 or a 6.
     

  21. 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.
     

  22. 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. Icon: Access 2003 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.

  1. Select the table Bonus Points in the Database Window.
     

  2. From the menu select  View | Object Dependencies .
    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.
       
  3. 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.
     

  4. 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!)
     

  5. Click on OK in the second message box, too.
    The table is deleted.
     

  6. Open the Relationships window.
     

  7. 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.

  1. 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.
     
  2. 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.
    Icon: TroubleProblem: 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.
     
  3. Click on the Bonus Points table in the upper part of the Design View.
     
  4. 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.
     
  5. 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.
     
  6. Delete the Bonus Points table from the Design View.
     
  7. Add the Bonus table.
     
  8. Delete the column B: [Bonus Points].[5B]
     
  9. Drag the Points and Term fields to the grid.
     
  10. Sort the Term field ascending.
     
  11. Arrange the columns so that Class, FullName, and Term are in this order. They don't have to be side-by-side.
     
  12. 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.
     
  13. Icon: Save Save As Q6WeeksGrades.
     
  14. Icon: Run Run the query.
    Results: Sorted by Class, FullName, and Term. Each student has a term 5 and a term 6 row.
    Query Datasheet - Q6WeeksGrades

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.

  1. 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!)
     
  2. Icon: Save Save As Q6WeeksGrades-Round.
     
  3. Icon: Run Run the query.
    The datasheet now shows rounded values. Much easier to read.
    Query Datasheet: Q6WeeksGrades - with Round
    If you omit the ,1, you will get values rounded to whole numbers.
     
  4. 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.

  1. 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.
     
  2. Switch back to Access and from the menu select  Tools  | Database Utilities | Compact and Repair database .
    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!
     
  3. 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?

Database Window: Tables Database Window: Queries Relationships window


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.

  1. Relationships:
    Icon: Print Print the Relationships report from the Relationships window in Landscape orientation.
    Print Preview: Relationships
    (Access will not remember this orientation. You must change the defaults every time.Sad smiley face)
     
  2. Print Preview: table datasheetTable 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.
    Icon: Print Print the datasheet of the table Students. (2 pages)
     
  3. Documenter report: Table
    Icon: Print 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
       
  4. 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.
    WarningAccess will remember your Options choices for the next time your print a Documenter report.
    Icon: Print 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
       
  5. Print Preview: object dependenciesObject 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.)
    Icon: Print Print the screen shot.
    Icon: Save 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.)