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
11 Apr |
complex queries |
05.06 |
next session
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
You can build queries that merge data from several tables
and that mathematically manipulate the data in the resultant dynasets.
You can apply a function on one of your queried fields by grouping by that field
Having revealed the totals row, you can apply a function on the field you wish.
This allows you to, in this case, to sum the number of times a state name appears in the Grouped By list.
Or will it? Can you actually SUM text values?
No, you cannot and you will be told so with an error message. What do you really want to do to see how many times a state name appears?
Does this work? Will it show me the names of the states and the number of publishers associated with each state that are in the database?
What does the criteria on the Group By field mean?
What, indeed, is a "Null value"?
it is a field that has nothing in it
therefore, you wish to have a query return a field that is empty, you need to
add the criteria that the field is empty, or "Is Null"
when you ask for Null, you get fields that have nothing in them
Likewise,
when you ask for Is Not Null,
you get fields that do not have nothing in them,
or better said, fields that are not empty.
So if you want to see records that have empty fields of some kind, create a null value criteria in your query.
You can add calculations by using the Expression Builder and you can add an expression to an existing query or to a new one you are building. Place cursor in a vacant field in the QBE box and click on the Builder button to open the expression builder.
Build an expression by deciding what you want to do and where you can find the values you need.
Once you have built the expression, click OK and the expression will be added as a new field in the query. You may need to rename the expression in the QBE grid and you may need to alter the properties of the newly created field so that it will display as you need it to
in this example, we might want to ensure that our newly named expression will display as money and not as numbers
and then to test it, to see if it works
... noticing how the new name we used for the expression ends up as a caption in the query.
When you sort on multiple fields, Access bases the sort on the leftmost field and uses fields to its right as tie-breakers when values in the leftmost field are the same
you may need to change the order of fields in the design grid if you are sorting on multiple fields, because the sort order will match the field order
in an ascending sort,
a descending sort is useful for date fields so you can place the most recent records at the top of a list