Table of Contents
This page describes procedures for updating the database. These generally involve data entry in excel files and uploading to access tables. Templates for the lab database are in 'updates.xls '.
It is assumed that major updates will be performed on an annual basis to support data analysis and production of lake monitoring reports. More frequent updates can be performed, as appropriate. Minor additions and corrections can also be made directly to the access tables.
Remember to backup the
entire access file (and database directory, for that matter) before
embarking.
Useful references in this process include the database structure diagram and data dictionary.
Below, 'sheet' refers to a worksheet in an excel file, and 'table' refers to a table in the access database.
Update procedures are described below or the following database components:
| Component | Access Tables | Excel File |
| Lab Analytical Data | samples_update, results_update | updates.xls |
| Index Tables | sites, devices, parameters, etc. | udpates.xls |
| Hydrolab Field Data | field_data | field_data.xls |
| Lake Bouy Data | buoy_data | buoy_data.xls |
| Daily Hydrology Data | flows | flows.xls |
Worksheets are edited and subsequently uploaded to the
access database. This operation involves the following
steps:
Errors may result from incorrect data
types or primary key violations (e.g., duplicate keys, such as
sample_no in the sample table, or sample_no / lab_parameter combinations in the
results table). See field types and primary key definitions in the access
table design views or the data dictionary.
Import the named range 'Results_Database' from this excel
file using .
Select the option to append the records
to the existing access table (Results Update). Any import errors would reflect
errors in the excel file.
Edit excel table, save, close
the excel file, then repeat import procedure until no errors are encountered.
Run the 'Append Results' query to add the 'Results Update'
table to the existing 'Results' table
Updates.xls
contains templates for appending new sample and results records the database. The
templates are located in the samples_update sheets. A few
records are included in these sheets for demonstration purposes. These
records should be deleted before adding new ones. The workbook also
contains current copies of each index table in the lab database
(except for results) for reference purposes.
Enter Sample
Records
Retrieve sample records from
the lab database (1 record per sample).
See the template on the 'translation' sheet.
Copy & paste lab data into the appropriate columns of the Samples_Update sheet.
Copy & paste the formulas on the right side of the table (in blue) to each row.
Fill in remaining columns, as indicated in the template.
Sample numbers must be unique (no
duplicate records)
Translate 'Source' Field
In compiling
the historical database, the Source field has been translated into consistent
sample identifiers (SITE, DEPTH, QAQC, etc.). This translation will be
unnecessary in the future if consistent sample identifiers are initially entered
in the lab database.
The Source_Index sheet shows how the field was generally translated.
Records were subsequently edited based upon data fields.
Formulas in columns W-AA of the Samples sheet retrieve values from the Source_Index sheet
Edit these values, as appropriate (replacing calculated cells in the Samples_Update sheet)
Add rows to the Source_Index to reflect any new Source codes.
The Source field in the access Samples table is for reference purposes only to assist in the lab data translation. It is not directly linked to other access tables.
Translation rules for the
Source codes can be overriden, as long entries are valid for other sample
identifiers.
Check Sample Records
Click the 'Update References'
button at the top of menu sheet.
Refresh the Pivot Table on the Sample_Crosstab sheet
The Pivot Table can be reconfigured as desired to summarize the data in various categories
Check the data inventories in the tables & edit records in the Samples_Update sheet as needed.
Consistent codes must be used for fields linked to other tables in the access file.
These fields include SITE, SAMPLE_TYPE, QAQC, LAB, SAMPLE_DEVICE, SAMPLE_FLAG
Upload Sample Records
Click the 'Update References' button on the updates.xls menu to re-assign the named range 'database_samples' to the update sample records. Save and close the file.
Follow the above uploading procedure to import the named range 'database_samples' in the excel file to the 'samples_update' table of the access database. Make sure to delete any existing records in the 'samples_update' table beforehand.
Run the 'append_samples' query to append the 'samples_update' table to the 'samples' table.
Enter Results Records
Copy & paste data into the appropriate columns of the Results_Update sheet.
Copy & paste the formulas on the right side of the table (in blue) to each row.
Fill in remaining
columns, as appropriate (see translation
sheet
)
Translate Lab Parameter Codes
See discussion of lab_parameter and parameter
codes.
The Lab_Parameters sheet contains a current copy of the Lab_Parameters table.
The formula in column N of the results sheet checks for registration of the lab_parameter field in that table.
An 'N/A ' result indicates a problem. Edit
the record entry or add a new lab_parameter record to the lab_parameters sheet
(and to the access lab_parameter table, as well).
Check the Results Table
The sample number field should be registered in the
Samples_Update sheet. The formula in column M checks for this
registration. An exception to this would be if the sample corresponding to
the result record is already stored in the access samples table.
Combinations of Sample_No &
Lab_Parameter fields must be unique in the results_update records. These
fields are used as primary keys in the database results table. Each combination
of these fields should have only one record in the results table. To check this,
open the 'Results Crosstab' sheet of updates.xls and Refresh the Pivot Table (!
on menu). Verify that the table cells are either 1 or empty. Edit the
results_update sheet if duplicate records are found.
Upload Results Records
Click the 'Update References' button on the updates.xls
menu to reassign the named ranges for the sample & result
update tables. Follow the above uploading procedure to import
the named range 'database_results' in the excel file to the 'results_update'
table of the access database. Make sure to delete any existing records in
the access table beforehane. Then run the append_results query to append the
'results_update' table to the 'results' table.
Updating or Editing Index Files
Updates.xls contains a copy
of each index table
(parameters, sites, etc.), as well as the entire
samples table. These tables are automatically retrieved from
the access database by clicking the 'retrieve tables' button on the menu
page. They can be subsequently edited and uploaded using the procedure described above, replacing the entire
existing table. This may be more convenient than editing the tables
directly in access if extensive editing or updating of an index table is
required.
Checking Updated
Samples, Results, & Index Tables
Load queries.xls
and follow its instructions. This runs a series of queries that
summarize & check the entire access database for errors in linked
tables. Record inventories and copies of some tables are also produced.
Output from each query is copied to the corresponding worksheet.
Edit
the access tables until no errors are indicated by the 'Check' queries
.
Once
the updated records are in the access database, it may be
easier to edit any errors in the access table, rather than repeat the above
update uploading procedures.
If the
entire update procedure is repeated (starting with the excel sheets), updated
records in the Samples & Results tables must be deleted first.
Filtering the tables
based upon the 'RECORD_DATE' fields is a convenient way to identify updated
records.
Follow These Links for
