SILS iSchool

19 Nov 2018

Value Added | daily

Class Schedule

Basics | sessions 01-05

22 AUG | intro
27 AUG | clients
29 AUG | servers
05 Sep | networks
10 Sep | basics lab

Web Development | sessions 06-11

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

Document Markup | sessions 12-14

03 Oct | object layers
08 Oct | graphics
10 Oct | document markup lab

Spreadsheets | sessions 15-19

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

Relational Database | sessions 20-26

31 Oct | relational databases
05 Nov | tables
07 Nov | relationships
12 Nov | input & output
14 Nov | SQL

19 Nov | complex queries | 05.06 | next session

26 Nov | databases lab
 21 Nov | Thanksgiving 

Presentation | sessions 27-30

28 Nov | presentation design
03 Dec | presentation delivery
05 Dec | presentation lab
12 Dec | 0800-1100 | final in class presentation

You can build queries that merge data from several tables
and that mathematically manipulate the data in the resultant dynasets.

More Complex Queries

back to top


You can apply a function on one of your queried fields by grouping by that field

  • normally, the Query By Example (QBE) grid assumes you are simply grouping by fields
  • to apply a function on a grouped field, you must click on the sigma () to reveal the Totals row
totals row

Having revealed the totals row, you can apply a function on the field you wish.

totals function

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?

data mismatch

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?

total in query design

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?

back to top

Null values

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

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.

back to top

Adding Calculations to Queries

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.

total function: count

Build an expression by deciding what you want to do and where you can find the values you need.

expression builder

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

using the expression builder

in this example, we might want to ensure that our newly named expression will display as money and not as numbers

format the expression

and then to test it, to see if it works

running the query

... noticing how the new name we used for the expression ends up as a caption in the query.

back to top


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,

  • text fields are sorted in alphabetical order and the sort does not take capitalization into account
  • number and currency are sorted from the smallest to the largest number
  • date fields are sorted from the earliest to the latest date

a descending sort is useful for date fields so you can place the most recent records at the top of a list

back to top