INLS161-002 | Spring 2017

Session Date: Wednesday Nov 15, 2017

Access: SQL Queries


Preparation for this Session

Look over these for simple queries

  1. In Access 2007: The Missing Manual, 1st Edition,
    read 6. Queries That Select Records.
  2. Introduction to queries
  3. You may find the Wikipedia entry on Structured Query Language (or SQL) useful for background, but don't get too deeply wrapped up in it right now.

You might also want to look at Microsoft's tutorial on queries.

[top/reload prep panel]

Microsoft's "Get answers from your data"

A query is a request of the database, the result of which is a new table.

  • a query selects records from one or more tables based on user-specified criteria
  • so a query is a way to ask questions of the database and your query design tells Access exactly what data to retrieve

Whenever you run a query ...

  • it checks for the latest data in your database and
  • the data returned by a query is called a recordset or a dynaset

You can browse through the recordset, select from it, sort it, and print it. Typically, the recordset you produce with a query isn't saved, but the query structure and criteria you used to get the results are saved.

Queries make it easy to:

  • combine data from multiple tables into a single view
  • summarize data
  • select and sort data
  • add, update, or delete large groups of records

The key to creating queries

read the question out loud and pull the elements of the question into the query design.

Query Types

  1. Select Query: extract specified data from tables
  2. Parameter Query: a select query that allows the user to specify selected parameters at the time of each query.
    All the Queries we will do in the Access assignment will be Select and/or Parameter Queries.
  3. Crosstab Query: summarizes data and presents it in easy-to-read format
  4. Action Query: makes changes to many records in a query at once
    • Make-Table Query: creates a new table with data from existing tables
    • Delete Query: deletes rows from a given table or tables
    • Append Query: adds additional rows to the end of a given table
    • Update Query: makes changes to one or more rows in a table

Creating Queries Using the Query Wizard

The Wizard can do simple queries by asking you questions and creating a query based on your answers.

query wizard.new query query wizard in operation

Creating Queries Using the Query Design Window

query design view

add tables and queries to be used in the new query to the window so you can see the tables, keys, and relationships in the tables

  • You can either drag fields from the tables into the Query By Example (QBE) grid
  • or highlight the field in the QBE grid and doubleclick on the field in the table to move in to the QBE grid
  • or use the drop down box on the QBE grid to select the desired field

Creating a Simple Select Query using the Wizard

A select query retrieves data from one or more tables and displays the recordset in a datasheet.

You can also use a select query to group data, and to calculate sums, counts, averages, and other types of totals, which we will cover in a subsequent session.

  1. From the Tables/Queries drop-down list, choose the table/query that contains the fields you want to include in the query query wizard.select source
  2. Select the fields you want to include and move them into the Selected Fields column query wizard.select fields
  3. If the fields selected include a number field, you have a decision to make.
    • If you want to see each record, choose Detail.
    • If you want to see totals, averages, or other summaries, choose Summary and set the summation options.
    query wizard.summary options
  4. Name your query and save it name the query

Creating queries with criteria

A select query can be built to return data that meets certain criteria. This is easily seen in the design view. Say, for example, we wish to see a list of all authors whose names begin with B as well as the titles of their books ...

  • we can add a criteria to the query
  • we can also add a sort requirement for the resultant data
sort options

Other select queries allow you to add Boolean "and" and "or" criteria to the query and to sort the response.

query design Boolean results of testing the criteria

Creating queries with criteria and with parameters

A parameter query displays a dialog box when it runs, prompting the user to enter information to use as criteria for the query. You can design a parameter query to prompt for more than one piece of information. For example, you can design it to prompt for something from one table as well as from another.

  • here we ask for names from Author that begin with some letter and dates from Book that fall within a range
  • Access will then retrieve all data with values that meet the criteria we specify in the dialog boxes
  1. Open an existing select query in Design view
  2. Enter the parameter statement in brackets in the Criteria row of the desired fields
parameter statement in design view
  1. Test it out

When the Enter Parameter Value dialog box opens, enter an appropriate value and click OK
[note: the parameter entered into the dialog box must be an exact match for the data in the fields you are searching. In this case, you must enter the exact value that the database holds in order to return anything.]

parameter dialog box, first value parameter dialog box, second value parameter dialog box, third value testing the results of the three part parameter

A parameter query allows you to design a query to return data in accordance with specific criteria you enter as you run the query.

Sorting

sorting from left to right

When sorting by multiple criteria, you need to arrange the sorted elements in the desired sort order, from left to right in the Query By Example grid. Here we are sorting first by publisher country, then where the country name repeats, by publisher name, then where the publisher name repeats, by state, and so forth.

Structured Query Language view

All queries are built using the Structured Query Language, or SQL, and you can review how the Wizard-built or the Design-built query is expressed in SQL. It is one of the views under the View icon in the Query Tools Design ribbon.

SQL view

Go to task 5.05



last page update: Friday May 19, 2017

Instructor

Lawrence Jones

Office hours by appointment.