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.
- Select Query: extract specified data from tables
- 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.
- Crosstab Query: summarizes data and presents it in easy-to-read format
- 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.
- From the Tables/Queries drop-down list, choose the table/query that contains the fields you want to include in the query
- Select the fields you want to include and move them into the Selected Fields column
- 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.
- 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
- Open an existing select query in Design view
- Enter the parameter statement in brackets in the Criteria row of the desired fields
- 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.
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.
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
If you get the table in the grid, and get the field in the column, the
WHERE will be created.
PARAMETER Like [begins with] & "*" (try "cr" "first") PARAMETER Like "*" & [ contains ] & "*" (try "orig") PARAMETER Like "*" & [ ends with ] (try "42" "ity" "america") SELECT title FROM tblBook WHERE title LIKE *war* AND LIKE *pacific* SELECT title FROM tblBook WHERE title LIKE *war* OR LIKE *pacific* 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 * (gets ALL records) SELECT title FROM tblBook WHERE title LIKE *asia (how many?) SELECT title FROM tblBook WHERE title LIKE *asia* (how many?) SELECT title FROM tblBook WHERE title LIKE asia (why none?) SELECT title, copies FROM tlbBook WHERE copies > 6 SELECT title, topic FROM tblBook WHERE topic LIKE literature SELECT title, topic FROM tblBook WHERE topic LIKE ethics OR LIKE physics
When you do a boolean
SELECT, you can put it on the
OR: line, but after you save
the query, Access will reformat it to display entirely on the
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: