meets Tuesday and Thursday from 0800-0915
office hours in Manning 112
Class Schedule
10 Jan | intro
15 Jan | clients
17 Jan | servers
22 Jan | networks
24 Jan | basics lab
29 Jan | structural layer
31 Jan | presentational layer
05 Feb | working with layers
07 Feb | behavior layer |
12 Feb | images & design
14 Feb | website lab
19 Feb | document markup
21 Feb | graphics
26 Feb | document markup lab
28 Feb | spreadsheets
05 Mar | formulas & functions
07 Mar | data display
19 Mar | database tools
21 Mar | spreadsheets lab
26 Mar | relational databases
28 Mar | tables
02 Apr | relationships
04 Apr | input & output
09 Apr |
SQL |
05.05 |
next session
11 Apr | complex queries
16 Apr | databases lab
18 Apr | presentation design
23 Apr | presentation delivery
25 Apr | presentation lab
30 Apr | 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.
You may also need to use one or more wildcard characters to pull data that is almost, but not exactly, in the database.
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