Analysis of Historical Tributary Loads
File:
historical_loads.xls
Description
This workbook supports analysis of the historical tributary loading database (1985-present).
The historical_loads.mdb file stores daily output from the load calculation workbook.
Record contents are described in the data dictionary.
New records can be appended each year using the procedure described below.
See general instructions on installation and the user interface.
The workbook contains pivot tables and charts that illustrate different approaches to summarizing and analyzing the database.
New pivot tables can be created by copying an existing table and pasting onto a new sheet.
All pivot tables use the same query output, so refreshing any table should refresh all tables.
The date range can be restricted by editing the 'master' query in historical_loads.mdb or by changing the year range on the menu sheet and then reconnecting.
Units for flows, loads, and concentrations are defined in the access parameters table, though they may be rescaled on the variable_index sheet of load_calculations.xls.
Default units for flows are million cubic meters, loads in metric tons, & concentrations in mg/l (except FCOLI)
FCOLI (Fecal Coliform) units are 10^10 cells for load and cfu/100 ml for concentrations.
Updating Historical Loads File
The load calculations workbook generates a daily output file in text format.
This file can be imported to the 'loads_update' table of historical_loads.mdb.
The importing procedure is similar to that described for the other tables, except the imported files are in text instead of excel format.
The 'append_load_updates' query of the access database adds the loads_update table to the loads table.
Typically, yearly updates to support the lake monitooring report will utilize provisional flow data from the USGS. Final flow data are generally not available for a period of two years. To ensure that the historical loading file is based upon final USGS flows, the update interval should be the most recent 2-3 years. For example, the following procedure would be followed in updating the database to support production of the 2003 monitoring report:
