Class Session: Wednesday Apr 10, 2019

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

Queries Cheat Sheet

Basic Queries Powerpoint

One Table Queries

The following queries are very simple. They use only the tblBook table. These will all work with the absence of all other tables. No relationship are required. No forms or reports are needed.

Access does not require you to know SQL. So you do not need to be concerned with the SELECT, FROM, WHERE statements; they will be automatically generated, but you do need to know the field you want, and you need to know what table to use. You will drag those into the QBE grid or you may also use the query wizard.

Spacing in the examples below is for comprehension; do not use spacing in queries.

We wil start with the SELECT Queries and come back to the PARAMETER Queries.

Parameter queries are "user" queries. Your user does not need need to write SQL or need to know how to use the QBE grid. I will go over the QBE grid in a Powerpoint before we start working on these queries.

You will focus on writing the parts of the queries starting with the LIKE statement on the right side in the examples show below. You will need to understand the difference between And and Or.

If you get the table in the grid, and get the field in the column, the SELECT , FROM and WHERE will be created.

Video covers examples below (First part: Exact, Wildcards, Math Symbols,and Two Records in Context)

EXACT
---------------------------------------------------------------------------------
SELECT title         FROM tblBook WHERE title  =      war law  (only one exactly)
SELECT title         FROM tblBook WHERE title  LIKE   war law  (only one exactly)
SELECT title         FROM tblBook WHERE title  LIKE   asia     (why none?)

WILDCARDS
---------------------------------------------------------------------------------
SELECT title         FROM tblBook WHERE title  LIKE  *         (gets ALL records)
SELECT title         FROM tblBook WHERE title  LIKE  asia*     (begins with asia)
SELECT title         FROM tblBook WHERE title  LIKE  *asia     (ends with asia)
SELECT title         FROM tblBook WHERE title  LIKE  *asia*    (contains asia)

MATH SYMBOLS
---------------------------------------------------------------------------------
SELECT title, copies FROM tlbBook WHERE copies >      6
SELECT title, copies FROM tlbBook WHERE copies =      3
SELECT title, copies FROM tlbBook WHERE copies <      1

TWO RECORDS SO YOU CAN SEE THE CONTEXT
---------------------------------------------------------------------------------
SELECT title, topic  FROM tblBook WHERE topic  LIKE   literature

BOOLEAN "AND" and "OR"
---------------------------------------------------------------------------------
SELECT title         FROM tblBook WHERE title  LIKE  *war* AND LIKE *pacific*
SELECT title         FROM tblBook WHERE title  LIKE  *war* OR  LIKE *pacific*
SELECT title, topic  FROM tblBook WHERE topic  LIKE   ethics OR LIKE physics

PARAMETER QUERIES
---------------------------------------------------------------------------------
 Like          [begins with]  & "*"     (try "cr" "first")
 Like   "*" &  [ contains  ]  & "*"     (try "orig")
 Like   "*" &  [ ends with ]            (try "42" "ity" "america")

MULTIPART PARAMETER QUERIES
---------------------------------------------------------------------------------
Like       [begins with]  & "*" Or Like       [begins with different] & "*" (prompts can't be exact)
Like "*" & [ ends with ]        Or Like "*" & [ ends  with different]       (parameter prompts must not be exact)
Like "*" & [ contains  ]  & "*" Or Like "*" & [ contains different  ] & "*" (parameter prompts must not be exact)

When you do a boolean OR SELECT, you can put it on the OR: line, but after you save the query, Access will reformat it to display entirely on the Criteria: line.

For example, if you type this:

Critera: Like *war*
     or: Like *pacific*

After saving, it will be changed to:

Criteria: Like "*war*" Or Like "*pacific*"
      or:

Go to task 5.05

Quick links

opal login: ssh ONYEN@opal.ils.unc.edu
opal password: ONYEN password

Instructor

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.

Office hours by appointment.