Access: SQL Queries

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.

Creating Queries Using the Query Design Window

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
2. Select the fields you want to include and move them into the Selected Fields column
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.
   
4. Name your query and save it

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

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

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

3. 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.]

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

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.

Go to task 5.05