Class Schedule
22 AUG | intro
27 AUG | clients
29 AUG | servers
05 Sep | networks
10 Sep | basics lab
12 Sep | structural layer
17 Sep | presentational layer
19 Sep | working with layers
24 Sep | behavior layer
26 Sep | images & design
01 Oct | website lab
03 Oct | object layers
08 Oct | graphics
10 Oct | document markup lab
15 Oct | spreadsheets
17 Oct | formulas & functions
22 Oct | thoughts about data display
18 Oct | Fall Break
24 Oct | database tools
29 Oct | spreadsheets lab
31 Oct | relational databases
05 Nov | tables
07 Nov | relationships
12 Nov | input & output
14 Nov | SQL |
05.05 |
next session
19 Nov | complex queries
26 Nov | databases lab
21 Nov | Thanksgiving
28 Nov | presentation design
03 Dec | presentation delivery
05 Dec | presentation lab
12 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