Access Queries

A query links data tables , retreives records based upon specified criteria, and averages them in various ways. The source data tables are not modified in this process.

This sheet describes queries distributed with the database. Most are intended as examples or templates. The format and record selection criteria within these queries may be modified to a limited extent by the user. Modification of some queries (in particular, master, master_filtered, or master_filtered_daily) may affect linkage to the excel applications, however. New queries can be added by access programmers.

Supporting excel workbooks (queries.xls and pivot_tables.xls ) facilitate retrieval and analysis of query output for users familiar with excel (in particular, excel pivot tables).

Query Categories

Queries are named in three categories:

'Master...' queries retrieve various data subsets.
'Inventory...' queries count records in various categories.
'Check..' queries verify table linkages, as described below.

Master Query

The master query links all tables in the database (except for field, buoy, & flow data). It also serves as a data source for many of the other queries that further screen or summarize it. Each record contains one analytical result and all sample descriptors, comments, and flags (~500,000 records)

A number of calculated fields are added to facilitate analysis of the data:

JULIAN Julian Day
MONTH Month of Year
YR_MONTH Year & Month in YYYYMM format
HOUR Hour of Day (0-24)
SEASON Season of Year (Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec)
DEPTH_ROUNDED Depth, rounded to nearest meter
DEPTH_INTERVAL U <=7 meters, L > 7 meters,
for lake upper & lower layer samples (change, as desired)
SITE_LEVEL SITE + DEPTH_INTERVAL (for lake samples)
VALUE Numerical Result (VRESULT field)

If the numerical results is below detection (QUALIFIER = "<") , it is multiplied by a constant factor 'K'. The K value (default = 1.0) is specified in the equation for the VALUE field in the 'master' query. Changing K here will change all query output.

The master query selects records according to the following criteria:

VRESULT is not blank (numerical result is reported)
EXCLUDE field in the LAB_PARAMETERS table is blank (default)

Master_Filtered Query

The master_filtered query screens the master_query output based upon the following criteria:

Primary water quality variables (those with defined PARAMETER codes)
Sample, Result, and Outlier Flags = blank or 1 (excludes 2, fatal flag)
Year >1984 Restricts data to period of tributary monitoring (can be easily changed)
Laboratory = Onondaga County (Lake, Nearshore Tributary) or UFI (Tributary, 1991-1992).
The UFI tributary data are included because replaced portions of the OC monitoring effort in 1991-1992.

This query has been designed to support the data summaries, load calculations, and trend analyses typically performed in perparing yearly lake monitoring reports. It also supplies data to the time series & load calculation workbooks, as well as to other queries.

Master_Filtered_Daily Query

This query averages the master_filtered results by day for a give site, parameter, date, depth, and qaqc code. It supplies data to the pivot_tables workbook.

Master_Filtered_Crosstab Query

Transforms master_filtered query output into conventional spreadsheet format (rows = samples, columns = parameters). Unlike most of the others, output from this query fits readily into an excel worksheet. A current copy of this query can be obtained in the queries.xls workbook.

Check Queries

'Check..' queries verify table linkages. Each is designed to retrieve no records if the tables are properly linked and the required linking variables are defined. These should be examined after updating the database.

Query.xls facilitates this process. Edit access tables & repeat until no records are listed in the "Check_" sheets .

The check queries provide limited verification of the database integrity, they are not intended to be comprehensive and other errors may develop when updating the databases and go undetected by the check queries.

Similarly, the queries may retrieve records that are not fatal flaws. For example, the check_sites query may identify site codes in the site table that are not present in the samples table. This will occur, for example, if the site code refers to a record in the field_data table but not the sample table.

Other Queries

Other example queries are listed access query menu screen .