Project 3: Tables & Queries
Designing Queries

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

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

  • Button: Select Query 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
    • Button: Make-Table Query Make-Table creates a new table from selected records
    • Button: Append Query Append selected records to an existing table
    • Button: Delete Query Delete selected records
    • Button: Update Update values in selected records
  • Button: Crosstab Query 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.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    ExercisesTo subtopics

Project 4: Forms & Reports


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

Query Design View: multiple tables

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:

Query Datasheet View: Multiple tables

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.