Project 3: Tables & Queries
Designing Queries

The power of a database comes from its queries.

A query can sort records, select certain records, and display only certain fields or certain records and can even perform actions on those records.

Types of Queries

• Select - Most commonly used type. Can sort or filter and can calculate values using several fields.
• Parameter - Any kind of query that first asks the user for input that is used in creating the final query.
• Action - Selects records and then performs an action on them
  • Make-Table creates a new table from selected records
  • Append selected records to an existing table
  • Delete selected records
  • Update values in selected records
• Crosstab - Selects records and calculates totals based on one set of values down the left and another across the top of the datasheet.

Queries are often used as the source for forms and reports and for Lookup fields.

Examples below are from LanguageArtsClasses.mdb.

Query with Multiple Tables or Other Queries

In your previous work you used the Query Wizard to create a simple query based on just one table. In the next lessons you will create more complex queries using several related tables.

Happily, you have a lot of control over what the query does.

Example Query - Students Allowed on Computer
         (from the Language Arts database):

Purpose of query: To produce a list of students who have qualified for free-play time on the classroom computers.

The table No Computer lists students who have incomplete assignments and therefore do not qualify. I wanted a list from the other point of view - those who DID qualify. (It was a shorter list!)

Tables used: Students and No Computer, which are joined by the field StudentID.

Fields used: Class field from the Students table and the StudentID field from the table No Computer.

Calculated field: The query uses the three fields for a student's name in the Students table to create a single text string for a new field called Name.
Name: IIf([Called] Is Null,[LastName] & ", " & [FirstName],[LastName] & ", " & [Called])
Yes, you will learn how to create such a calculation! It's not as hard as it looks. Really!!

Sorting: Sort Ascending on Class and Name fields.

Criteria: For StudentID field, Is Null

Resulting Datasheet:

The datasheet for this query shows that 24 of the 92 students would be allowed to use the classroom computer. The StudentID field does not show in the datasheet because the Show box in the design grid was not checked.

Query with Calculated Fields

A powerful feature of queries is the ability to calculate new fields, like the Name field in the example above. In that case, text from three fields was combined into a single text string, along with some punctuation and spaces.

You can also combine number fields like a Score with text fields for a name and with static text into a single text string, like "The score for Julie French was 95".

You can do all kinds of mathematical calculations with number fields, like averaging six weeks grades for a semester grade or calculating profits and losses. The options are endless!

Such new fields are often used in forms and reports to control the display so that there is no wasted space.

In this project, you will create a number of calculated fields. Later you will create calculated controls for forms and reports, which are quite similar but do have some important differences.

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012