A Special Query Mechanism

0) Introduction:
1) The Special Query Mechanism:
2) Copying Selected Records:
3) Database Calculations:
4) Summary:


0) Introduction:

In this third lecture on databases, we will learn about a special mechanism 
that we can use to specify general criteria with Excel formulas (as in the last
lecture), but stating these criteria/formulas using a special form that is
simpler than the one-big-and-complicated-boolean-formula method, and is more
like what we did the autofiltering lecture. So, we have the same power as in
the second database lecture, but a simple way to write our queries as in the
first database lecture.

We will also apply this same special mechansim to calculate other kinds of
questions about databases: e.g., counting the the number of records, or finding
mins/maxes/sums/averages/etc. for certain columns/fields, but only in the
selected records.


1) The Special Query Mechanism:

First, let's start with a familiar example, and then continue with a more
general analysis of this mechanism. Recall from the previous lecture that the
following formula

  =OR(  AND(B2="Knuth",F2<30),  AND(G2="Theory", F2>50, E2>=2000)  )

specified either books written by Knuth that cost <$30 OR Theory books
that cost more than $50 and were published in the year 2000 or later.

To use the special database mechansim, first insert 4 blank lines above the
column labels (above Author(s), Title, Publisher, etc).

Our query involves the columns labeled by Author(s), Category, Cost, and Year
so we will enter these text values into cells A1, B1, C1, and D1 respectively.
Actually, it doesn't make a difference in which order we enter these values,
but let's assume the above order for simplicity. What labels appear here is
critical: they must exactly match the labels in what is now row 5. For this
reason, I suggest copying/pasting these values, not retyping them, lest we 
make a typographical error.

We put the text value Knuth in cell A2 (under Author(s)) and the text value
<30 in cell C2 (under Cost). We put the text value Theory in cell B3, the text
value >50 in cell C3, and the text value >=2000 in cell D3.

-->Open the the workbook lecture14.xlsx distributed with this lecture.
-->Examine the layout on page DB1, which is as we described above

For this special database mechanism, the top part of the worksheet is the
query criteria, and the bottom part is the database that we are querying. We
should leave at least one blank row between the last criteria and the columns
that top the database.

Next, select cell A5 (the upper-left hand corner of the database) which
contains the first column label ( Author(s) )and press ctrl/a, which selects
the entire range of rows and columns that is the database.

Then, click the (Filter) Advanced icon in the Sort & Filter group on the Data
tab. Its pop-up window, named Advanced Filter, should have "List range:"
already filled in with $A$5:$H$1279 (the database that we selected right before
starting this command). We should fill inthe "Criteria range:" with the range
A1:D3 (if it is not already there). The easiest way to do this is select the
"Criteria range:" textbox and then select A1 and drag/drop down to D3.

Finally, click OK and the database will be filted according to the criteria
above. Note that these criteria are still easily visible at the top of the
worksheet with the filtered records below (not stuck in some BOOLEAN formula in
column A, as with the previous method). We might have to scroll upward to see
these criteria.

-->Follow the directions above and verify that 19 records are displayed

Generally, each line in the criteria specifies any number of conditions
(on the data in the columns specified by the labels in row 1) that are ANDed
together. There can be multiple lines, and a record is displayed if the
criteria on any line is TRUE. This is really just the same as the standard
OR of AND form that we discussed in the previous lecture.

In the example on worksheet DB1, row 2 specifies Knuth in the Author(s) column
and <30 in the Cost column; row 3 specifies Theory in the Category column and
 >50 in the Cost column and >= 2000 in the Year column. So, by the
interpretation described above, a record will be displayed if EITHER the author
is Knuth AND the cost is <30, OR if the category is Theory AND the cost is >50
AND the year is >= 2000.

If we changed one of these criteria (say change >50 to > 55) we would have to
click the (Filter) Advanced icon again to redisplay the records that satisfy
the new criteria (just changing the criteria does not automatically update what
records are displayed). For this reason alone, it is very useful to have the
(Filter) Advanced icon on the Quick Access Toolbar.

With this special mechanism the criteria is text, but we can use general Excel
formulas to create the text criteria. So, for example, in cell C3 we could
specify the text formula (using the & -catenation- operator)

  =">" & AVERAGE($E:$E)

which would result in the text

  >26.38...

appearing in cell C3, specifying to compare the Cost in each record to this
value using the > operator to determine whether the record should be selected
to appear.

So, when we put text in these cells, Excel knows how to interpret the text as
a criteria to apply to records.

If we clicked (Filter) Advanced and OK again, Excel would  redisplay the
records that satisfied this new criteria. Typically the Advanced Filter pop-up
window keeps the same values for the List range and Criteria range that we used
last time we clicked this command. But, you should always double-check these
ranges for correctness, especially if you get an unexpected result for your
query.

We can click the (Filter) Clear icon (in the Sort & Filter group on the Data
Tab) to show all the records again. If we again click the (Filter) Advanced
icon, that command shoould keep the same defaults for the data and criteria
that we used the last time we selected this command.

-->Examine the layout on page DB2, which is as describe above
-->Click the (Filter) Advanced icon to display the filtered records

Of course, we can define names for these ranges too and use those names in the
pop-up window that needs these ranges. In fact, whenever we do this kind of
database query on a page, Excel stores the name Criteria scoped to that page
with the range of cells that contains the criteria.

So, generally if a databse column is needed to specify some criterion, then
the label on the top of that column will appear at the top of the criteria.
There are two other interesting rules involving these labels.

First, we can DUPLICATE a label and put a second criterion under it on any
criteria line. For example, if we wanted to display all records whose cost was
between $25 and $30, we could enter Cost in both cells A1 and A2; then we could
enter >=25 in cell A2 and <=30 in cell B2. For this line, this is like
specifying a cost >=25 AND a cost <=30. Sometimes duplicating column labels
makes queries easier to write. In fact, we repeat labels any number of times
that we find useful.

Second, we can also write arbitary formulas in cells that have no column label
or a column label that is different from the labels of each record field
(something descriptive of the calculation done by the cell). In the above case
we could remove the Cost labels from A1 and B1 (or put the label "Cost Between"
in cell A1), and remove the criteria from cells A2 and B2, and instead enter
into A2 (it must be below an empty or otherly-named cell) either the formula

  =AND(Cost>=25, Cost<=30)

or the formula

  =AND(E5>=25, E5<=30)

The first formula uses the label of the column with the relational operators;
the latter uses the name of the cell (using relative addressing) starting at
the first row in the database

-->On the DB3 worksheet, try all three ways of displaying books in the [25,30]
-->  price range: using two Cost labels, using an unlabeled cell and the
-->  formula above with Cost, and the formula above with E5.

So, we can do everything we did with the "BOOLEAN formula in column A" query,
but using this special query mechanism and Advanced filters.

Don't worry about the fact that the first formula will appear as #NAME? (since
Cost has no meaning by itself, Excel shows the formula this way, but when we
use it in a query, Excel knows what to do with Cost in the formula). Please
note you need to type the word Cost, NOT the text "Cost" here.

We can mix these forms. Put Category in cell B1 and Theory in cell B2 (along
with the formula =AND(Cost>=25, Cost<=30) in cell A2 and nothing in A1) and
use both in an Advanced filter (remember to specify the Criteria range as
A1:B2.

Suppose there are some number of queries that we commonly use to filter a
database, or we need to use them to filter multiple databases, each on their
own worksheet. We can keep these queries (their column labels and criteria)
together at the top of the worksheet containing the data, or even on their own
worksheet. Then, when we want to filter the database according to  one of the
standard queries, we specify the database query, either both on the same
worksheet or on different worksheets.

The simplest way to do this is to first select the database (using the ctrl/a
method specified above). Then click the (Filter) Advanced icon. Then, select
the "Criteria range:" box, then click the tab of the worksheet containing the
query, then select the first cell in the query and drag and drop to the last
cell in the query, then click OK. The cells must include the column/field names
and the values for the query.

-->Filter the data on the DB3 worksheet using the two queries on the DB3A
-->  worksheet.

One caution, if you go back to worksheet DB3 and try (Filter) Advanced again,
Excel will lose the reference to the 2nd worksheet with the criteria, so you
will have to specify it all over again. It might be useful to define a name
like DB to be equal to the rectangular range that includes the entire database.

-->Put 10 blank lines on DB3 and copy the queries from DB3A at the top of
-->  this worksheet
-->Perform those queries, one after another, on that worksheet

Finally, carefully spell the column labels from the database; it might even be
a better idea to copy/paste them from the columns in the database to ensure
they are spelled correctly. If your query selects no records (when you expect
it to select some), double-check your column/field names and the values
underneath them.

In summary, this special mechanism is almost as general as using boolean
formulas to filter, but easier to use because we can specify the criteria in
many cells on top of the database (or even on another page). It is much eaiser
to change a criterion with this special mechansim than editing and copying (or
filling) boolean formulas in a special column. Also, we can write and save
queries that we commonly use, and query using them easily.


2) Copying Selected Records:

Once we filter the records (by autofilter or the (Filter) Advanced mechanism
descrribed here, we can copy just the selected records and paste them anywhere
else.

In fact the Advanced filter pop-up window allows us to choose to not filter
the the records but instead copy the chosen records to some other cell. If we
click the "Copy to another location" radio button, then we can fill in the
"Copy to" range (easiest to click the left-most cell in the first row that we
want the selected data copied to). Unfortunately, we are restricted to copy
them somewhere on the worksheet that the database is on. 

Finally, we can also have Excel elide any records that are complete copies of
one another (all fields have the same values). Typically records in a database
are unique, so this feature is not useful for simple database queuries.


3) Database Calculations:

Excel includes the following functions which operate using the special
mechanism discussed above: rows of criteria followed by rows of a database

  DCOUNT, DCOUNTA, DMAX, DMIN, DSUM, DPRODUCT, DAVERAGE

Each of these functions has the same three arguments

  =DCOUNT(Label+Database Range, Label to Count, Label+Criteria Range)

For example (note the "Cost" here, not Cost as in a previous formula)

  =DCOUNT(A4:H1278, "Cost", A1:B2)

The DCOUNT command selects the records from the first operand (label row and
the database record rows underneath it), specified by the last operatnd (label
row and the criteria rows underneath it) all the records satisfying the
criteria. The value it calculates is the number of selected cells in the "Cost"
column that contain numbers (the DCOUNTA function counts the number of
non-blank cells in that). 

The DMAX, DMIN, DSUM, DPRODUCT, and DAVERAGE functions work similarly; for
example

  =DSUM(Label+Database Range, Label to Sum, Label+Criteria Range)

selects the from the database specified by the first argument all the records
satisfying the criteria in the last argument. Then its value is the sum of
the selected cells in the column Label to Sum). Note that the column label is
TEXT and either must be calculated or must appear in quotes.

We can use such a database function to compute quantities like the average
price of Theory books bought before 2005.

-->On the DB4 worksheet, I have written the query  (Theory books bought before
-->  2005) in cells A1:B2.
-->Write a DAVERAGE command in cell C1 to compute the average price of books
-->  selected by this query (should be about $40.28)
-->You don't need to filter the database, just compute this value.
-->Change the query to select books bought >= 2005 and see the DAVERAGE cell
-->  update immediately (to about $39.69)
-->Change the query again to specify Theory books regardless of the year,
-->  using A1:B1 for the criteria range, (the result should be about $40.10)
-->Change the query again to specify AI books regardless of the year
-->  (the result should be about $25.01)


4) Summary:

Here is a quick summary of skills to acquire from working on this lecture.
You should be able to discuss each of these topics a bit, but more importantly
know HOW TO DO something in Excel,

Know how to set up queries using the special mechanism: column/field names and
text of formulas in cells.

Know how to count the number of records whose BOOLEAN formula is TRUE, or
find their min/max value, or their average, etc.