This Assignment Continues Where Lab 16 Left Off, with a Couple Examples of Advanced Queries

This Assignment Continues Where Lab 16 Left Off, with a Couple Examples of Advanced Queries

Homework 16

CS131 – Summer ‘08

This assignment continues where Lab 16 left off, with a couple examples of advanced queries

  1. Open up your access database from Lab 16
  2. First, we’ll populate the tables a bit more.
  3. Enroll yourself and two students into different classes (not cs131) for the Fall 2008 term. Save the database
  4. If you go to the Fall 2008 query, you will notice that the query we created earlier did not update. Lets fix that
  5. Go to design view for the query, then under “Query Tools->Design”, hit Run in the Results group. This will update the query
  6. Save a screenshot of the updated “Fall 2008 Classes” query in datasheet view

  1. Now we want to count the number of students taking each class. We will use the COUNT function (refer to Chapter 13, Objective 14)
  2. Toggle “Totals” under Query Tools -> Design. At the bottom a “Total” row now shows.
  3. For a third field, we want to show a total. We will use “Count” in the total field, and count up the number of student ID’s in the enrollment table by setting the Table to “enrollment” and the field to “Student ID”.
  1. Once you’ve added this new field to your query, run the query again. Save a screenshot of your new query

  1. The other “advanced” query we want to make is a list of classes a particular student is taking during a term.
  2. Enroll yourself in two more classes for the Fall 2008 term (you should already be enrolled in one). If you want, add some more classes to enroll yourself in.
  3. Create a new Design Query, name it “Name’s Fall 2008 classes”, where Name is your name
  4. First add drag “term” down from enrollment, and set the criteria to “Fall 2008”. Uncheck the box since we don’t need to see Fall 2008 3 times.
  5. Now Drag “ID” down from the Students table, and set the criteria to be the same as your ID in the table. We don’t need to see this field either so uncheck it.
  6. Drag down the Class Name and Class ID, both of which we do want to see.
  7. Now the database will automatically search through the merged Enrollment, Class, and Student tables and find entries which match your name and the Fall 2008 term. It will then display the Class Name and Class ID. Run the Query.
  1. Now that you have a class list, lets create a printable version. Go to Create and create a report for this query. It might ask you to save the query—so select “yes”
  2. You should now get a nice, printable class schedule for the Fall 2008 term. Save a Screenshot.Note that I added an extra class, you only need 3 classes (although more is fine).


  1. Zip up all screenshots or paste them into a word document and email me to them under
    “[cs131] Name – Homework 16”
Top View