Class Schedule
21 Aug | intro
23 Aug | clients
28 Aug | servers
30 Aug | networks
04 Sep | basics lab
06 Sep | structural layer
11 Sep | presentational layer
18 Sep | working with layers
20 Sep | behavior layer
25 Sep | images & design
27 Sep | website lab
02 Oct | object layers
02 Oct | graphics
09 Oct | document markup lab
11 Oct | spreadsheets, formulas & functions
16 Oct | thoughts about data display
18 Oct | Fall Break
23 Oct | database tools
25 Oct | spreadsheets lab
30 Oct | relational databases
01 Nov | tables
06 Nov | relationships
08 Nov | input & output
13 Nov | SQL |
05.05 |
next session
15 Nov | complex queries
20 Nov | databases lab
22 Nov | Thanksgiving
27 Nov | presentation design
29 Nov | presentation delivery
04 Dec | presentation lab
13 Dec | 0800-1100 | final in class presentation
This work
is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
home & schedule | class blog | syllabus | contact | grades
Queries ask questions of the tables and respond with dynamic new data in new tables.
Access uses the Structured Query Language to create queries,
though you won't necessarily have to directly write a query in SQL syntax.
Whenever you run a query ...
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.
read the question out loud and pull the elements of the question into the query design.
The Wizard can do simple queries by asking you questions and creating a query based on your answers.
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
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.
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 ...
Other select queries allow you to add Boolean "and" and "or" criteria to the query and to sort the response.
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.
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.
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 | 05.05 | next session