Supplementary Access Review:

Open the database DAI pre-course Access work.mdb. First, make a copy of this database so that if you make too many changes you can go back to the original if needed. With the copy open, answer the following questions:

(1) How many records are in each of these tables?

N Tahlequah:_____

N Ft.Smith:______

(2) Open the “MarbleCity” table. Use the sort buttons (A>Z and Z>A) to sort on date and PM2.5 value to find the range of dates (start and end) and values (max and min) :

MarbleCity
First date
Last date
Lowest value
Highest value

(3) Run the query “qry_Ft Smith Summary” by first, finding the query:

Then clicking Open to run the query (or double-click it) to see the results:

Close the query result window (this result is called the datasheet view, and you can toggle back and forth between the datasheet view and the design view of the query by clicking on the view design/datasheet button:


Switch to Design View:

Look at how the query is constructed. Try clicking on the Total field in each column, changing the values to one of those in the drop-down list, and running the query from Design view by clicking on the ! (Run) button in the toolbar. (Don’t worry, you can exit without saving changes.)

What happens when the Show box is checked or unchecked? ______

(run the query with the box checked and then again with the box unchecked, and you can see that when the box is checked that field is shown in the query results, but if it is unchecked that field may still be used in selecting the data but that field is not shown in the results.)

What is the standard deviation for the PM2.5 values in 2004? (4.52) What is the variance? (Var).

(Note that the header in the resulting table will stay the same, but the actual values calculated will be those you select in the drop-down list. So if you change Min to StDev and click Run (!) the values in the resulting table will be StDevs.

(4) Find the query “Avg Summary by Site and Year” :


Run the query by clicking Open. The query will run and produce this summary table in datasheet view:

Open the query IN DESIGN VIEW:

Notice how this summary query is based on sub-queries. When you click on the join, the line between the tables, it becomes bold.

Delete the join (click delete when the join is bolded):

Run the query (click on the ! button in the top toolbar). Without that join from year-in-one-site-query to year-in-the-other-site-query, how many records are returned? ______

Why? (Without the join, Access must return all the records matched in all the ways possible with the other tables, rather than linking date to date. Making sure that the joins are set correctly is critical to Access working properly.) (Note that there are instances where you want the join line deleted.)

Close the query without saving it.

(5) Open the query “qry_Ft Smith Summary W/O H FLAGS” and run it. With the results of this query open, also run the “qry_Ft Smith Summary,” and compare the results. Note that since there were no H flags in 2004, the results from that year are the same. Close the “qry_Ft Smith Summary,” and open the “qry_Ft Smith Summary W/O H FLAGS” in Design view.

How are the records with H excluded?

Change the H in the criteria field to L. What is the average PM2.5 conc in 2004 with no records with L flags included in the calculations?______(Again, remember that the headers won’t change automatically based on you changing the criteria.)

These examples are a very partial list of expressions available in Access for use in queries; try experimenting with different values to see how the queries can be used to easily find data.

Examples of Expressions Used In Criteria Column:

Expression / Result
> 234 / For a Quantity field, numbers greater than 234
>= "Callahan" / For a LastName field, all names from Callahan through the end of the alphabet
Between #2/2/1999# And #12/1/1999# / For an Date field, dates from 2-Feb-99 through 1-Dec-99 (ANSI-89)
Between '2/2/1999' And '12/1/1999' / For an Date field, dates from 2-Feb-99 through 1-Dec-99 (ANSI-92)
Expression / Result
"London" / For a ShipCity field, orders shipped to London
"London" Or "Hedge End" / For a ShipCity field, orders shipped to London or Hedge End
>="N" / For a CompanyName field, orders shipped to companies whose name starts with the letters N through Z
Like "S*" / For a ShipName field, orders shipped to customers whose name starts with the letter S (ANSI-89)
Like "S%" / For a ShipName field, orders shipped to customers whose name starts with the letter S (ANSI-92)

SUPPLEMENTARY INFO:

The MS website has a good set of introductory Access trainings at (This is for Access 2003, but there is a parallel training for 2007). You will need to review the “Know Access” section, the material on Tables that is found in the “Objects” section and also all of the material in the “Queries” section. (The tutorials on Forms, Controls, and Labels are not necessary for what we will do in DAI). Attached is a word file containing excerpts from the Access help pages, and you may find that the MS Access quick reference card for query criteria has helpful info:

You can also get help while using Access by clicking on the blue circled question mark in the top right hand corner. Important concepts are:

Query design grid to construct basic select queries with criteria

Importing data into a table

Query design using multiple tables

Relationships between tables

Aggregate Functions Information:

You can create a new field that displays the results of a calculation you define with an expression that manipulates field values.

  1. Open the query in Design view.
  2. Type an expression in an empty cell in the Field row. If the expression includes a field name, you must place brackets around the name.

After you press ENTER or move to another cell, Microsoft Access enters the default field name ExprN, where N is an integer incremented for each new expression field in the query (e.g. Expr1 is the first new field created, then Expr2, etc.). The name appears before the expression and is followed by a colon. In a datasheet, this name is the column heading.

You can select ExprN and type a more descriptive name, such as NewPrice.

  1. If the expression includes one or more aggregate functions (Sum, Avg, Count, Min, Max, StDev, or Var), and if the design grid includes another field or fields that you want to use for grouping, click Totalson the toolbar (unless the Total row is already displayed). Leave Group By in the Total cell for the grouping field, and in the calculated field, change Group By to Expression.
  1. If you want, enter criteria to affect results of the calculation.

Limit groups before performing calculations on groups of records

  • Specify the criteria in the Group By fields as the following example shows.

This query totals extended prices for ...

... companies in Canada and the UK only.

  • Specify criteria in the field that contains the calculation as the following example shows.

This query totals extended prices for companies in Canada and the UK ...

... but shows only those that are less than $10,000.

Limit records before they are grouped and before the calculation is performed

  • Add to the design grid the field whose records you want to limit, and then specify criteria in the field's Criteria cell.

If you're calculating totals in the same query, set the Total cell for the field containing the criteria to Where. This rule applies whether you're calculating the total on all records or groups of records. (Microsoft Access automatically clears the Show check box.)

The following example uses the ExtendedPrice field twice, once to limit the records and once to calculate the total. However, you can use a different field to limit records by dragging that field to the design grid and setting its Total cell to Where.

From these records, the query retrieves only those with extended prices greater than $500.00 before it groups or totals ...

... and then it sums and shows only those totals for companies in Canada or the UK.

This total for Seven Seas Imports does not include the order for $210.00.

Notes

You might want to set field properties, such as the Format property, since the field doesn't inherit properties from the underlying table.