Information Technology in a Global Society

Databases: Part 5 Queries and Reports

So far you have seen ways of designing your database and getting information into the database.  Today we will look at how you extract that information once it has been entered.

Creating a query

A query is a form of a question for the database.  Suppose that you wanted to ask your database, "Please give me the grades for each student in a nice format like a transcript".  To get that information you form a query first.  The results of the query will look like a table that may include information from one or more tables in the database.

  1. You have create a database with student and grade tables.  Open that database now.  If you don't have a copy you can download this version from the web site.
  2. You are going to create a query so click on the queries tab and select Create query in design view. 
  3. Now select and add the student and grade tables.
  4. Click on the following fields in order:  student.IDNumber, student.address, student.gpa, grade.course, grade.year, grade.grade (note that the table name precedes the name of the field with a '.' in between).
  5. Save this query and call it transcript. 
  6. Open the query to see what you get.  If you don't have enough grades go back to your student form and enter more grades.  You should have a few grades for a few students to get a real feel for how it will work.  Also, for at least one student, include grades from a number of years.

Creating a Report

A report is a lot like a query.  In fact, we will base our report on the query that you just created.  The difference is that the report is intended to be printed by the user.  The query is not normally directly seen by the user.  So the query is analogous to a table while the report is similar to a form, but for output instead of input.

  1. Click on the report table and choose Create Report using wizard.
  2. On the first tab select the query called transcript that you just created.
  3. Select all fields. 
  4. The next window shows you how the fields will be grouped.  This is the preferred grouping already.
  5. The next tab shows you how you want to group again.  This time select the year field for grouping (so that you see your grades by year).
  6. The next tab asks you about sorting, well sort by course of course.
  7. On the layout tab pick Outline 1.  That should look good.
  8. And then the finish tab.  Now you see your transcripts.  Do you like this format?  What is wrong?
  9. Go back into design view to fix your report to look nice.  Right-click in the IDNumber header.  You want a new page for each student's transcript so look for the property ForceNewPage and set it to Before Selection.
  10. Change the report header to make a better cover page for the printout of student transcripts.