Home

(EDAS) A User`s Guide

Contents

1. Davis W S and T P Simon editors 1995 Biological assessment and criteria Tools for water resource planning and decision making Lewis Publishers Boca Raton Florida Gibson G R M L Bowman J Gerritsen and B D Snyder 2000 Estuarine and coastal marine waters Bioassessment and biocriteria technical guidance EPA 822 B 00 024 U S Environmental Protection Agency Office of Water Washington D C lt http www epa gov OST biocriteria States estuaries estuariesO html gt Hernandez M J 1997 Database Design for Mere Mortals A Hands on Guide to relational database design Addison Wesley Berkeley California ISBN 0 201 69471 9 Integrated Taxonomic Information System ITIS http www itis usda gov plantproj itis submit_first html Jennings R and M Harris 1997 Special Edition Using Microsoft Access 97 Que Corporation Indianapolis Indiana ISBN 0 7897 0916 3 Kaufeld J 1996 Access 97 for Windows For Dummies IDG Books Worldwide Inc Foster City California ISBN 0 7645 0048 1 Litwin P K Getz and M Gilbert 1997 Access97 Developer s Handbook Third Edition Sybex Inc ISBN 0 7821 1941 7 Merritt R W and K W Cummins editors 1996 An introduction to the aquatic insects of North America Third Edition Kendall Hunt Publishing Company Dubuque Iowa U S Environmental Protection Agency U S EPA 1998a Lake and reservoir bioassessment and biocriteria technical guidance document U S E
2. This will eliminate the need to key sample data into STORET By using tables and queries as established in EDAS a user can enter manipulate and print data The metrics used in most bioassessments can be calculated with simple queries that have already been created for the user New queries may be created so additional metrics can be calculated at the click of the mouse each time data are updated or changed If an operation on the data is too complex for one of the many default functions then the function can be written in code e g visual basic access and stored in a module for use in any query Repetitive steps can be handled with macros As the user develops the database other database elements such as forms and reports can be added This manual is designed to acquaint the user with the basic features of EDAS an application developed within MS Access v97 It is not intended as an Access manual and a basic understanding of computers and Access is assumed For those needing help with Access a list of supplemental support texts is included later in this document Nor is it intended as an exhaustive how to manual Rather it is intended for the average user who is familiar with Access to become acquainted with the features of EDAS EDAS is flexible enough to be customized to specific programs yet has been structured to ease the migration of data to STORET Most of the application is driven by code embedded within the program Visual Basic VB a
3. support includes automatic calculation of biological metrics as recommended by Barbour et al 1999 Future versions of EDAS may support other data types bioassessments based on other biological communities fish algae macrophytes EDAS version 3 0 was designed to simplify data input and analysis The front end of the application now includes easy to read forms that ensure data integrity by requiring data entry in a logical consecutive order This order of data entry from more general data e g station information to more specific data e g what organisms were collected is necessary to maintain adequate linkages between data elements The intended users of EDAS range from volunteer groups to state biological programs Users with limited database experience as well as advanced users should find the forms and reports to be simple to use while still providing sophisticated results EDAS is principally an analytical tool rather than a data warehouse As a relational database it can effectively store data in multiple tables that are uniquely reference But the strength of the program is in its ability to interact with the data retrieving specific data calculating biological metrics and common statistics compiling information in reports and outputting data in formats readily understood by other users and other computer programs EDAS User s Guide As an introductory description of the database architecture the tables within EDAS and
4. At USGS gage below culvert At RE 216 below bridge About 150 meters below darn At Kempenaar s Clambake Below culvert at parking lot nock Hill Road About 50 meters south below pool So ide of I sample taken from shore Manville Dam Millville Rt 122 Beaver River At Shan Figure 20 Selecting stations for batch upload 39 EDAS User s Guide 4 Click on Activity Type Sample to select the proper activity type Figure 21 Abbot Run Brook North al Route 120 bridge about 50 meters south of bridge Abbot Run Brook South Valley Road about 50 meters above bridge Adamsville Brook AtUSGS gage below culvert Ashaway River AERE 216 below bridge About 150 meters below dam At Kempenaar s Clambake Below culvert at parking lot Beaver River At Shannock Hill Road About 50 meters south below pool Bailey s Brook Big River South Side of Route 3 Sample taken fr t shore Blackstone AS Manville Dam Blackstone AS Milhzille Rt 12 Sample Figure 21 Selecting activity type 40 EDAS User s Guide 5 Click on Medium Biological for benthic macroinvertebrates or Water for water chemistry quality for a new option will appear Figure 22 Abbot Run Brook North _at Route 120 bridge about 50 meters south of bridge Abbot Run Brook South Valley Road about 50 meters above bridge Adamsville Brook ALUSGS gage below culvert Ashaway River AERE 216 below bridge About 150 meters below
5. and a minimum of 20 Megabytes of disk space EDAS is an application that runs within Access v97 SR2b on a Windows PC developed on Windows NT but tested operating systems include Windows 95 Windows 98 and Windows NT As of this writing the Access 97 SR2b patch can be obtained from the Microsoft website at http officeupdate microsoft com Articles sr2fact htm s downloadCatalog dldAccess as p If this link becomes invalid as frequently happens with Microsoft web pages try searching for the patch at the Microsoft homepage http officeupdate microsoft com EDAS has not been tested under Windows 2000 or Windows ME EDAS runs within Access v97 only due to code limitations The advanced version can be imported into Access 2000 but this is not recommended due to differences in the way the VB code is interpreted between the version of Access EDAS was designed for a screen resolution size of 800 x 600 regardless of monitor size Some options mostly within the reports sections run MS Excel and if it is not installed on the same computer as EDAS these options will not function Different sections of the database are designed for different types of users front end input forms for data entry metric calculation for data analysis and QA QC checks and access to the database structure for database administrators This user s guide is intended for use with EDAS v3 0 an upgrade from previous versions of EDAS Version 3 0 includes enhanced ease of use
6. are described in detail in Section 6 9 Customization of STORET is briefly described as well though greater detail can be found in the STORET User s Manual U S EPA 1998b STORET runs on ORACLE version 7 3 comparable to the way EDAS runs on ACCESS version 97 For directions on how to prepare your data for upload to STORET refer to section 6 9 and for additional information on STORET visit the STORET homepage at http www epa gov OWOW STORET 5 0 EDAS Basic Features A back up copy of the entire database should be made before starting to work with EDAS Unlike many windows based programs Access has no Save As function for the entire database file only for elements of the database tables queries etc Instead create a copy of the database file The start up screen displayed in EDAS has basic information on EDAS capabilities copyrights licensing and contacts Action buttons for navigating through the application appear at the bottom of the screen These action buttons call up the most commonly used components of EDAS These components include Calculate RBP Metrics for Samples Data QAQC Access Database Structure Add New Data Administrator Functions Review Taxa Information Review Personnel Information and Exit EDAS When using any of these components users must return to the start up screen by sequentially returning to previous screens Closing the EDAS program while in a component window may cause unintended erro
7. as there can be no universal import utility that works with every programs data there can be no universal import utility from one version of EDAS to another that will work for everyone This is because of the ability of users to customize and modify the database to their own specific needs This makes it impossible to anticipate any changes a user may have made in their copy of the database 8 0 Supplemental Support Texts Below is a list of texts for those needing help with Access e Kaufeld J 1996 Access 97 for Windows For Dummies IDG Books Worldwide Inc Foster City CA ISBN 0 7645 0048 1 This book serves as a reference for all the features of Access 97 Helps to solve problems with Access queries Contains simple techniques and tips to build your databases and tables e Hernandez M J 1997 Database Design for Mere Mortals A Hands on Guide to relational database design Addison Wesley Berkeley CA ISBN 0 201 69471 9 This book is a straightforward platform independent tutorial on the basic principles of relational database design e Catapult Inc and Microsoft Press 1997 Microsoft Access 97 Step by Step Microsoft Press Redmond WA ISBN 1 57231 316 1 Step by step self training guide through Access 97 Contains information on how to view enter customize data get and work with data stored outside of Microsoft Access analyzing data and queries create reports e Jennings R and M Harris 1997 Special Editio
8. been locked in the general distribution version This was necessitated by the fact that small changes in the code could have profound effects upon the integrity of the database i e causing it to not work or work improperly Thus EDAS is distributed as an MDE Microkernel Development Environment file that does not allow editing of the code or forms The full version will be available upon request but no support will be given Also a demonstration version of EDAS will be available that will be populated with test data to allow for testing and training A list of known issues is given in Section 7 ii EDAS User s Guide Table of Contents EXCCUlVe DIETE onsets SS ea clea eiue M t MM LE ii Tableof Contents cc ceste p set et Oe Nha E du i Ee iii Eist Of FIgUTE Susain ninna a Uy a a ea danas A E EA v Eist GLOPabIBS oed uide ou cl ors as iin mets cess ae ot tas A E A vi 1 0 HAMEL n nen pb EA a qub E N pas ee nue EE 1 2 0 Relational Databdses ue debiti oe aute too eed he rode nda dtes esas pa RER EA 3 3 0 EDAS Ecological Data Application System seen 4 4 0 STORET STOrage and REBTrieval isset enit Redes Ras e denscaeasee 6 5 0 EDAS Basic eat GS etse deco pea A tam desse atop tata due ps aM EE 7 5 1 Calculate RBP Metrics for Samples do seen attese stutdeddo pu d t S DUREE 7 5 2 Add New Mri 9 52 Benthic Macroinvertebrates see eoo eter beoe dae apu eee iba PRU ase Ee eeevs 13 3 22
9. data that facilitate queries The queries accessible through this option are not used by the EDAS forms and reports Changes to the queries will not affect the reports and export files generated through the EDAS front end This is because EDAS query form and report code is hidden though it references the accessible tables Therefore edits to the tables are significant edits to the queries and macros are experimental and edits to forms reports and modules are not possible Users may find that queries are the most useful tool for analyzing and exporting data Queries can be constructed to filter aggregate and calculate data For more advanced analysis and graphing queries can create spreadsheets for export Minimal information on constructing queries can be found in Section 6 0 However users should refer to ACCESS documentation and manuals for detailed instructions Section 8 0 The functionality of ACCESS is limited in the base version of EDAS Many of the ACCESS operations have been disabled in order to preserve database integrity The ACCESS Help menu displays operations that may not be functioning in EDAS Basic ACCESS functions are available within the Access Database Structure component and additional functions can be retrieved by knowledgeable ACCESS users Section 6 0 Editing the relationships links between tables may render EDAS inoperable This is important to note while deleting records An ACCESS error message may advise t
10. made to enter a sample for a non existent station or a taxon in a sample that was not in the Benthic Master Taxa list These errors are avoided or remedied when entering data through the forms of EDAS but advanced users appending and updating tables may run into error messages The hierarchy of tables can be expressed in an outline form Stations Samples Replicates Reference data units taxa attributes parameter characteristics Data number of individuals concentration of chemical habitat score Personnel Stations must be described in order for samples to belong to a location Many samples can be taken at the same location Samples must have been defined before multiple replicates of the sample can be compared Reference data must be defined to give the 30 EDAS User s Guide resulting data meaning And since people can be responsible for different aspects of each replicate sample the replicates must first be defined 6 8 Developing Metric Queries Several queries have already been constructed to calculate metrics References for many of these metrics are found in the RBPs Barbour et al 1999 available online at http www epa gov owow monitoring rbp ch07b html It would be impossible to foresee all metrics that might be used in biomonitoring programs but those available provide a basis for customized metric development The metrics are named using conventions that facilitate browsing through the list to find a metric that
11. may be applicable to your data Table 1 For example MBRic Total Taxa is a metric for benthic macroinvertebrate richness of all taxa in a sample Table 1 Naming conventions for metric calculation queries General Assemblage Metric category query class M Metric B Benthic Macroinvertebrate Ric Richness F Fish future EDAS versions Com Composition Tol Tolerance Intolerance Tro Trophic Feeding Group Hab Habit Mode of Locomotion Vol Voltinism Life Cycle Mis Miscellaneous Sum Summary Construction of specialized metrics can proceed by customizing an existing metric that is similar to the intended one Open the approximate metric and save it under a new name The most important variables in query design include fields displayed aggregation type criteria and equations calculated To add a field to the display double click on the field in the table Delete a field by selecting the entire column and pressing the delete key The aggregation type can be changed for the entire table by selecting the sigma symbol in the tool bar This toggles in or out the group by row in the query design For individual fields change the aggregation type by selecting from the pull down menu of the group by row The criteria are set in the lower portion of the query design view Criteria can be numerical logical or text depending on the values of the fields to b
12. of existing records General guidelines are given below for importing appending and updating data Properly formatting and importing complete historical databases can be a complex task and this manual only provides an overview 6 6 1 Appending and Updating EDAS Tables Format the original data within the original application usually a spreadsheet or database Data must be arranged in fields and tables resembling the destination EDAS fields and tables Data properties should be checked for compatibility size numeric text date etc Do not change EDAS field names or data properties but check the format in the design view of the destination table and change the original format if necessary Note that most primary keys are auto numbers and are generated in EDAS and do not need to be entered Import the tables into EDAS in the Advanced Mode of EDAS Select Get External Data from the File pull down menu and the Import option is then selected The file prepared for import should be selected and imported following prompts of the Import Wizard Appending data is a complex process that should be performed by a knowledgeable database administrator A few points to remember e primary key values must be unique e some primary key values are automatically generated as auto numbers by EDAS and do not need to be entered e data properties in imported data must agree with EDAS data properties e referential integrity rules in EDAS mandate a spec
13. of the manual In EDAS there are no special restrictions on the editing or creation of queries Tables however are part of the application and should not be renamed This restriction does not apply to new tables or new fields within tables and will not have an adverse impact on the operation of EDAS The data in tables can be edited by any user through forms or the Access Database Structure component Quality Assurance and Quality Control of the data must be maintained by database administrators the data is not locked it can be changed by any user Users can also change the names of fields or delete fields Tables are linked by table name and field name altering the table names field names or field properties should not be done as it may have unintended consequences Fields may be added to tables without altering EDAS and new tables may be created The names given to new fields and tables must not match any existing fields and tables Do not attempt to rename or replace imported tables with standard EDAS table names this type of substitution does not work 27 EDAS User s Guide 6 5 Working with Queries Advanced users should be concerned with migrating data between tables especially when re keying the data would be too tedious Populating a table with historical electronic data such as from previous EDAS versions can be accomplished using queries A query is a request to retrieve or manipulate data In addition to populating tab
14. the relationships between them are illustrated diagrammatically Figure 1 Each table is represented as a box with the table title at the top and fields within the table listed below Referencing primary keys from one table in another links the tables These links are represented as lines running between tables and are communicated within the program in SQL code Each table is organized around a data theme indicated by the primary key s the Stations table contains parameters that describe the station and that do not change over time the Samples tables contain parameters that specify the sample and that do not change between replicates and so on Microsoft Access Relationships A Sort ChemParamet ChemUnits CEREN Cheminis ChemMethod Measurement TypeOfUnit Description CollMethName Description StationID VisitNumber Activity Type zi Long Parameter Nar Parameter Name PhysCharValu Parameter Type I Enter Date zi Parameter Type II RoleID Ready T FW Figure 1 Relationships view of tables within EDAS A query is a request to retrieve or manipulate data from tables or other queries For instance biological metrics are calculated by querying the benthic macroinvertebrate community data from each sample Querying the tables can generate many other statistics The data displayed in a query are only temporarily organized Each time a query is opened the requested data are retrieved or c
15. uploading and STORET documentation and regional administrators should be consulted in the case of unresolved errors Small batches may be keyed into STORET using the printed record of un uploaded data 6 10 Creating a Password for Each Copy of EDAS In the Advanced Mode of EDAS it is possible to password protect the entire database This option is recommended to restrict data entry and retrieval to authorized and trained 46 EDAS User s Guide personnel From the Tools menu select Security and follow the prompts to Set Database Password Other options are available to set up user and group permissions and accounts These options are all functions of MS Access but access to these functions is restricted in the MDE version of EDAS 7 0 Known Issues Future Releases and Updates From Previous Versions of EDAS The current version of the database is available for download at ftp edas edas ftp planetwater com A history file is maintained at that location also that lists known issues A demonstration database EDAS v3 Demo mdb is maintained at this location also Passwords can be obtained by contacting Tetra Tech Inc using the contact information provided in Section 11 0 7 1 Known Issues EDAS version 3 0 is intended for a general audience of various users Development of this run time version of an ACCESS application for diverse users resulted in an imperfect product The errors that users may encounter will be addressed in updat
16. 6 9 2 Prepare EDAS oie toesu utet esie eio tee utu toni enlolad uidere gt e 35 6 9 3 Create Batch Pile eeie loses RES a ARa Meader a 36 6 9 4 POR Batch Files renent hd ree d usta doom ei petas ie sad beu 45 6 10 Creating a Password for Each Copy of EDAS sse 46 7 0 Known Issues Future Releases and Updates From Previous Versions of EDASA7 7 1 OWA ISSUES incre e aenn Er aE E a ESNE S 47 T2 F t re VerstODS onde eau eo Red bs A dut dao FD ME 48 1 3 Updates From Previous Versions of EDAS sse 49 8 0 Supplemental Support Texts cueste eiecti s pete eques uiae sex sn dote ad e UR ERREUR 49 9 0 Glossary OF Terms eei eresien e IS VAT IU CURT EU IN SIR ERE YN QN RS n ERA ra a NH RER See Np REA UNE 50 iii 10 0 Literature Cited EDAS User s Guide 11 0 Contact Information wo a Aaaa EEO AS AE ER SUAE iv EDAS User s Guide List of Figures Figure 1 Relationships view of tables within EDAS 000 0 eeeeeeeeeeneeceaeeeeeeteneeenees 5 Figure 2 Example of selecting a station to calculate RBP metrics esses 8 Figure 3 Selecting a station to input data seo ee ep bem Con I ott Los gnus 10 Figure 4 Utility for converting Latitude and Longitude degrees minutes seconds to deciitial deSpreesessns oso Un e TU E Gat usb ts wocca ds ad n xen eset cider aos 10 Figure 5 Entering station information ss ee oo qas correre tad eee e Facete depu uit 11 Figure 6 Adding replica
17. Access treats some numbers as text within EDAS When this data is exported MS Excel treats the data as text and left justifies the cells Depending upon the size of the cell and font size only a portion of the number may display Converting the cell to a number cell allows MS Excel to display the data properly This display issue does not affect calculations in either MS Access or MS Excel When using the import or export features of EDAS it is necessary that the user have installed in MS Access the ability to import or export the necessary file types e g ASCII text files or Excel spreadsheets If these features are not installed the options in EDAS utilizing import and or export files will not work as intended Tetra Tech cannot support users that have tweaked the database Tetra Tech will support users who are running the front end functions and are adhering to the warnings throughout this manual regarding possible corruptions of the underlying code Problems encountered while attempting to upload data to STORET may be attributable to limitations imposed by STORET and support for that application should be sought Future Versions 48 EDAS User s Guide In addition to the data that EDAS stores now it is projected that future versions will handle fish and periphyton data As well as some other commonly collected data e g pebble counts and cross sectional measurements 7 3 Updates From Previous Versions of EDAS Just
18. Ecological Data Application System EDAS A User s Guide ECOLOGICAL DATA APPLICATION SYSTEM iy VERSION 3 0 EDAS is a data management and analysis tool to facilitate biological monitoring of water quality Itincorporates a range of functions from relational storage of data to calculation of metrics to the creation of exportfiles including the creation of formatted batch files to take advantage of STORET s upload capabilities Modifications to any of the objects within this database this does not include data population may render itinoperable For more information see the EDAS manual Calculate RBP Metrics for Samples i i Review Taxa Information Data QA QC Administrator Functions Review Personnel Information Access Database Structure EXIT EDAS EDAS v3 0 is a custom database application for use with Microsoft Access97 Microsoft Windows and Access are trademarks of the Microsoft Corporation This application is regularly being updated and is subjectto change For the latest update contact Tetra Tech Inc 10045 Red Run Blvd Suite 110 Owings Mills MD 21117 410 356 8993 November 13 2000 Tetra Tech Inc EDAS User s Guide Executive Summary Ecological data derived from assessment and monitoring programs include biological physical geographical and chemical data The Ecological Data Application System EDAS is designed to enable the user to easily manage aggregate integrate and analyze data to make
19. Vawe Comments Nu AA J Wl m IABAIJA Sediment Oils Absent Sediment Oils Moderate Sediment Oils Profuse Sediment Oils Slight Inorganic Substrate Components Bedrock Inorganic Substrate Components Boulder Inorganic Substrate Components Clay Inorganic Substrate Components Cobble Return to Physical Characterization Replicates Figure 11 Selection of physical characterization parameters 5 3 Data QA QC Data Quality Control QC is a method by which the integrity of the data is measured and controlled Replication of sampling procedures allows estimation of sampling consistency and error rates Quality assurance QA and QC of many aspects of data integrity can not be automated Instead quality control often involves comparison of entered data and the original data sheets hard copy The metrics generated in EDAS should be systematically compared to manually calculated metrics to ensure accuracy of the data It is generally accepted that 10 of data entry and calculations should be checked for errors to ensure data quality Recurring errors can be corrected throughout the database and a random error rate can be estimated The Data QA QC function accessible in EDAS allows QC of the taxonomic attributes that are used in metric calculations This measures the percentage of taxa and individuals that have not been classified in tolerance feeding group and habit categories If a large percentage o
20. a second table linkages between tables relationships are established A database is distinguishable from a spreadsheet because it includes multiple linkages and multiple tables With such an organizational architecture a database has great capacity for data storage can store those data efficiently and is well referenced for accurate data extraction Other distinguishing traits of a database include the capacity for interaction with the data using forms queries and reports Entering data using forms is a logical and user friendly way to ensure that the data is entered or referenced in all of the associated tables queries and reports Queries are structured requests for the return of specific data or for actions upon the data Reports are text output of query results With the exception of individual tables these database objects are very complex to replicate using traditional spreadsheets All database objects are explained in greater detail in the following sections of this manual 3 0 EDAS Ecological Data Application System EDAS was designed to store and analyze several types of ecological data related to bioassessment of surface waters These data types include benthic macroinvertebrate enumerations water chemistry habitat assessments and the physical characteristics of sampling sites At present EDAS specifically supports bioassessment based on benthic macroinvertebrate community data and associated physical habitat parameters This
21. a spreadsheet or matrix In a database data in a record is commonly saved as Record ID Parameter Value This is a more efficient use of computer storage when all parameters are not consistently recorded For instance a listing of all sites by all taxa would result in a matrix of mostly zeros However a matrix of Record IDs by Parameters with Values at the intersection of cells is sometimes required To create such a matrix for export a Cross Tab Query should be used in the Advanced Mode of EDAS 6 6 Importing Data into EDAS Action Queries are used to create new tables Make Table Queries or alter data in existing tables Append Table Queries and Update Table Queries Queries take time to run and tables take space to store If a query can quickly call up data from existing tables there is no need to keep the queried data in a table However if a table is required e g as a record of a complex query a Make Table Query will store the query results as a table The resultant table can then be included in subsequent queries but will have to be rerun to update the data Making a table from other tables in the database creates multiple records of the same information and any subsequent data editing must be reconciled 28 EDAS User s Guide Append Table Queries and Update Table Queries are most useful in migrating data into EDAS Append Table Queries add entire records to existing tables and Update Table Queries add data in specific fields
22. alculated from tables This saves on computer memory space though complex queries may require more Random Access Memory RAM to run efficiently The data organized through queries can be reviewed on the computer screen printed as summary reports exported to spreadsheets or EDAS User s Guide transferred between tables Tables and queries are the principal database objects that EDAS users can manipulate Most users will interact with the data through forms only Forms organize data in logical displays for data entry review or editing The forms in EDAS also help users interact with standard queries to perform fundamental tasks of ecological data management and analysis These tasks include but are not limited to Data entry review and editing Metric calculations Quality Control Organizing data from multiple tables Creating spreadsheets for export Specialized administrative functions and Creating batch files for upload to STORET Reports macros and modules are additional database objects that EDAS utilizes but that are inaccessible to users 4 0 STORET STOrage and RETrieval EDAS is an analytical tool for use within biomonitoring programs It is easily accessed and manipulated for innovative interaction with the data STORET by contrast is a national data warehousing utility that STOres and RETrieves data U S EPA 1998b STORET is used as a system back up an archive and as a facility for disseminating data betw
23. ample Collection Procedure ID Benthicl Field Gear ID NNDP Person Doe John Bio Result Group 01 MTPC Char Group ID cg ben01 Result 1 4 Result 2 88 Result 5 12 When the preceding lines are viewed in a text display program Notepad Wordpad there should be no blanks after the comma of each line and no hard return after the last line that would create a blank line after the final result STORET has a batch upload procedure that requests the name of the batch file Errors may be encountered while uploading data No data will be uploaded if any errors are encountered and an error message will identify the probable cause of the failed upload STORET suggests that errors are often caused by insufficient background information This refers to the required data and data codes that must agree with EDAS data and data codes Batch upload errors are usually corrected by providing additional information to STORET When sample data has been partially uploaded as when some taxa codes are unavailable in STORET listed in the un uploaded data file of Step 3 A new batch file can be created in EDAS that will only include data that was not previously uploaded The new batch file will recognize that the station visit and Activity ID were previously uploaded and will eliminate the ADD suffix on those fields STORET should be able to import this new batch file and append the data This process is somewhat more difficult than initial batch
24. arameters Row ID within Characteristic Groups Specific parameter code taxon or chemical species Result Count concentration etc 33 EDAS User s Guide EDAS users are typically concerned with a single Project within a Program and Organization The Organization Program and Project information entered into STORET is not likely to change after the original set up The Organization ID is DEMOTEST in all STORET copies unless the STORET DBA has provided an authorized copy customized with your organization s valid ID Descriptive information about your organization can be entered including the Programs and Projects that it administers and information about those Programs and Projects Stations and Trips must be defined and assigned to the Project Station information can not be uploaded in batches to STORET but must be keyed individually Minimal station information that must be entered includes Station ID Station name Primary station type river stream station establishment date latitude longitude in degrees minutes and seconds state and county A Trip is a group of sampling events In EDAS the Trip ID is automatically defined as the 4 digit year associated with the sample collection date e g 1999 2000 2001 Such Trips should be defined in STORET and assigned to the appropriate Project Stations and Trips imported from EDAS will only be recognized if they have been described in STORET Sampl
25. boris onis PEN MEAE 14 3 23 Habitat Assessment anisina a TRS 15 5 2 4 Physical Characterizations sseseseseeeseeeesseesseessesssetesseeesseesseesseesseeesseee 17 CENT LEO TN bo 18 5 4 Access Database SIr Cctule usto siete o ae d EEE Ta a 19 5 5 Administrator FUNCIONS ori i eT E E E E EES 20 5 6 Review Taxa Information i isisi tec n et seina n 21 5 7 Review Personnel Information ssssesseessesssesessetesseesseesseesseresseeesseesseesseesseee 24 255 CEGOE EIAS sid cemere ea a a a a a a a eaa 25 6 07 EDAS Advanced Eeatufes seo a a a Ee L eas 25 6 1 Advanced Mode Or EDAS soosima e bes esie E a bans 26 6 2 Saving and copying EDAS oii vapercisivsdeasiavssaacsden ceeds sageassaaesd cateandeaeeceaeaacacepedens 26 6 3 Database amitetlategs scito iuh Uso obl itenim c ean Posti ELO NIE 26 GAs Working With Tables sees oc pte idco Pond mu d ove ib oto Ede Fog qu NM 27 6 5 Working With QUrIQS iie ees ret era teet se eee o be eau e EE Pee aepo e eee Ue Enea 28 6 6 Importing Data nto EDAS sun col sea toe etes vus eeu eri gesoet TUER een leas eoe in gessien 28 6 6 1 Appending and Updating EDAS Tables eene 29 6 7 The Hierarchy of EDAS Lables im eot o es tote rie oetbie tire dove der 30 6 8 Developing Metric QUEFPIS sop er e RIS Gedy IH MIE EARN Ue Qr och Co un ve dax 31 6 9 Uploading Data from EDAS to STORET eese eee nne 32 6 9 1 Customize SEORBE e eese aide tae ase t EE 32
26. cel Return to Previous Form Figure 15 Samples common to a specific taxa Selected from a button on the Taxa Review page The TSN of a taxon is defined by the Integrated Taxa Information System ITIS http www itis usda gov plantproj itis submit_first html The Parent TSN refers to the taxa group that is more general than the taxon of interest TSNs and Parent TSNs can be entered by selecting the action button below their display on the form Revise TSN ParentTSN CharGroupID The Characteristic Group ID is a data code that relates taxa in EDAS to those in STORET This should be edited by the DBA that maintains STORET The Taxa Administration action button is password protected This function allows knowledgeable biologists and DBAs to verify the information displayed with each taxon This function displays a check box for marking each taxon after the information has been verified Unfortunately verification does not automatically lock the taxa information from additional edits and verified information can be changed 5 7 Review Personnel Information Personnel information that can be reviewed and edited using this option includes contact information address phone and e mail training education and supervisor The first and last names of the personnel file appear in the pull down pick list for each sample 24 EDAS User s Guide replicate Figure 16 The role of each person is not permanent It is assigned in the sample repl
27. characterization and personnel Existing data can also be reviewed and edited using the Add New Data component of EDAS All edits additions changes and deletions made in this component will affect the underlying tables in the database No action is required to save edits the edits are made and saved immediately without additional prompts The first window that appears in the Add New Data option prompts the user to define a new station identification or to select from those already in the database Figure 3 Station IDs can be any combination of characters and numbers The next screen shows all the fields related to the location of the site and other information that is not likely to change over time Many of the fields have pull down pick lists Specific pick lists e g HUC county and ecoregion are customizable to a particular state or region Conversion of latitude and longitude from degrees minutes and seconds to decimal degrees is possible by clicking the action button to the right of the latitude and longitude fields during data entry Figure 4 The conversion option is not available if values exist in the latitude and longitude fields EDAS User s Guide Abbot Run Brook Sout Valley Road about 50 meters above bridge Adamsville Brook At USGS gage below culvert Ashaway River At Rt 216 below bridge About 150 meters b Bailey s Brook At Kempenaar s Clambake Below culvert at Beaver River At Shannock Hill Road About 50 met
28. chitecture that maintains data integrity That is data is uniquely identified from one table to the next or from one sample to the next Mechanisms within the database that ensure unique and specific data linkages include primary keys in the tables automatic assignment of primary key values and linkages between tables that are dependent on data in other tables With this array of rules EDAS enforces quality control by accepting only unique and identifiable data Understanding the rules will help advanced users when building queries or otherwise manipulating data The architecture of the database is displayed in the relationships window selected from the EDAS Utilities menu on the toolbar These relationships must be maintained for EDAS to function properly Many of the relationships symbolized by lines running between significant fields of tables are of a one to many character This means that a single record in one table can be referenced to multiple records in the linked table For example the attributes of a taxon need only be listed once in the database though that taxon appears in many sites The attributes appear with the taxon only once in the Benthic Master Taxa table but are linked to many samples in the Benthics table Furthermore those tables linked with referential integrity with one to many relationships require the reference data one before the popular data many can be entered This would become apparent if an attempt was
29. complished by selecting the Add Photo action button while the taxon of interest is displayed in the Select Taxon Name field Figure 14 The bitmap bmp or Windows Meta File wmf that contains the 22 EDAS User s Guide graphic can be referenced by typing the complete path and file name including the file extension when prompted The photo will appear whenever the associated taxon is selected in the Review Taxa Information component The user is warned that adding pictures increases the size of the database significantly And the larger the database file the slower it will run Ephemerellidae 7 Add A New Taxon o SELL Correct Spelling of Taxon Name A t N Eo DL gt ce e Revise TSN ParentTSN CharGrouplD X d Taxa Administration Return To Previous Form E gt E m Ai Figure 14 Adding a photo to the Taxa Review page Photo courtesy of Leonard Smock Virginia State University The sites where a taxon has been sampled can be displayed by selecting a taxon and then selecting the View Samples Which Include This Taxon action button The records retrieved include information about the site and sample The records can be reviewed on screen Figure 15 or saved as ASCII text or EXCEL spreadsheet files 23 EDAS User s Guide Long Dec Benthic Sample ID R Test 024 Test Trib Test1024 Test Trib Export Results as ASCII text file Export Results to Ex
30. dam Bailey s Brook At Kempenaar s Clambake Below culvert at parking lot Beaver River At Shannock Hill Road About 50 meters south below pool Big River South Side of Route 3 Sample taken from west s Blackstone AS Manville Darn Blackstone AS Millville Rt 122 Biological Figure 22 Selecting medium 41 EDAS User s Guide 6 If Biological data is being batched choose Intent Taxon Abundance Figure 23 and Community Benthic Macroinvertebrates Figure 24 Abbot Run Brook North at Route 120 bridge about 50 meters south of bridge Abbot Run Brook South Valley Road about 50 meters above bridge Adamsville Brook At USGS gage below culvert Ashaway River At Rt 216 below bridge About 150 meters below dam Bailey s Brook At Kempenaar s Clambake Below culvert at parking lot ELA River At Shannock Hill ROS About 50 meters south below pool g Rive outh Side of Route indetne AS Manville Dam Milhzille Rt 122 Figure 23 Selecting intent 42 EDAS User s Guide Abbot Run Brook North at Route 120 bridge about 50 meters south of bridge Abbot Run Brook South Valley Road about 50 meters above bridge Adamsville Brook AtUSGS gage below culvert Ashaway River At Rt 216 below bridge About 150 meters below dam Bailey s Brook At Kernpenaar s Clarnbake Below culvert at parking lot Beaver River At Shannock Hill Road About 50 meters south below pool Big River South Side of Route 3 Sample taken fro
31. e limited Equations can be built in the Field row of the query by right clicking and choosing the build wizard It is important to note that all metrics are based on the Benthics Collapsed query And also that organisms marked as Excluded Taxa are excluded from taxa richness dependant metrics but are included in percentage metrics 31 EDAS User s Guide 6 9 Uploading Data from EDAS to STORET The process of transferring data from EDAS to the local STORET requires four steps as follows 1 Customize STORET to receive your organization s data 2 Prepare EDAS with valid sufficient and properly formatted data 3 Create uploadable batch file using automated functions of EDAS 4 Import batch file into STORET EDAS is intended to be used as a complement to STORET the national data warehousing system To migrate data from EDAS to STORET each program must be prepared to communicate the data results in batches The batch file created automatically in EDAS is in a specific format recognized by STORET The batch upload process entails saving the batch file and importing it to STORET For additional information on STORET visit the STORET homepage at http www epa gov OWOW STORET 6 9 1 Customize STORET Once you have an authorized copy of STORET running and are familiar with the STORET application it must be customized with valid data and data codes The fields that must be populated in STORET are listed in Table 2 The cu
32. e after the data has been collapsed to remove stage and habitat The query Benthics Collapsed is an example of how the organisms are arranged before metrics are calculated EDAS automatically calculates the best candidate benthic metrics from the RBPs Barbour et al 1999 for the selected samples and displays the results in tabular format The results can be 1 reviewed on the screen 2 exported to an EXCEL file 3 exported as ASCII text or 4 viewed and printed as reports Reviewing metric results on the screen simply requires scrolling through the sample records and metric fields When the option to export to EXCEL is clicked EXCEL is automatically opened to a new workbook with the default name metricresults xls in the lt C EDAS gt folder This folder must be established for the file to be exported The workbook should be saved in a different folder or under a different name because subsequent EXCEL file exports will overwrite the lt C EDAS metricresults xls gt file Saving and other manipulations can be accomplished using the EXCEL application EDAS User s Guide Exporting the file as an comma delimited ASCII file is accomplished by selecting the action button from the RBP metric results window The file will be saved with a user specified path and name ASCII text files can be viewed in most word processing programs or in text viewing utilities such as Notepad or WordPad that are shipped with Windows They can also be impo
33. e screen or by choosing Close not Exit from the File menu 47 7 2 EDAS User s Guide The Access Database Structure component of EDAS allows users to view and edit tables and queries Tables contain valid data that is referenced by the table and field names Renaming tables or fields will destroy the linkages between tables and will disable much of the underlying code Also the linkages between tables relationships can be edited but doing so could cause errors in the code Queries may be safely edited as the queries are not referenced by the code The EDAS Advanced Functions component allows DBAs to create batch files for upload to STORET The sensitivity of STORET to specific file formats and data integrity requires that this function be password protected Do not be careless with the password only knowledgeable and trusted users should be working in this area Even DBAs could potentially cause unintended consequences with this function and care should be taken to understand STORET Running the Advanced version of EDAS allows for considerable functionality over the MDE version Do not use this manual as a reference for ACCESS functions Users of the Advanced version of EDAS should be well versed in ACCESS commands and should save a valid copy of EDAS before experimenting Some MS Excel export files may not display numbers correctly although the numbers in the cells are correct This is due to the fact that MS
34. ed versions Some of the errors can be avoided by strictly adhering to this User s Manual by following procedures exactly as outlined and by restricting the tendency that some users may have to improve the application Well intentioned manipulation of the database may damage the underlying code and careless tampering may certainly do so The EDAS DBA is encouraged to back up copies of EDAS initiate security procedures and supervise data entry and retrieval The most sensitive known issues remaining in EDAS version 3 0 are e Typical users will only be interested in the front end application where forms and common procedures are accessible Some of the sensitive areas of the database are password protected or require specialized knowledge to access In the front end of EDAS the greatest potential for damage to be done to the database is by improperly navigating through the various components This is related to the creation and deletion of temporary tables during data retrieval for forms reports and export files To avoid damage to one or more components of EDAS be sure to back out of any component that is return to previous screens when the option is given The program should always be closed using the Exit EDAS action button on the start up screen Report windows do not include a return button but should rather be closed by closing the reports window i e using the lower X in the upper right hand corner of th
35. een programs and users Because of STORET s function as an archive it requires limitations on data access greater security and is not used for day to day analysis A single database administrator DBA within each biomonitoring program may have responsibility for data integrity and entry into local and national STORET databases EDAS and STORET can be used independently though use of both databases together will enhance most biomonitoring programs Data entry into EDAS is simplified through an interactive user interface After data has been entered into EDAS it can be inspected managed analyzed and exported in batches that are correctly formatted for import into STORET Data that are in the category of results sample specifications enumerations and or concentrations can be automatically migrated in such batches For example a taxa list with abundances and associated sample information can be created in EDAS in a format that STORET will accept as import data ASCII text file Customizations of both EDAS and STORET are required before automatic batch uploads will be accepted Organization monitoring program and station information must exist in STORET before data can be assigned to the stations Data codes and metadata must be established and must be in agreement in both applications If the correct metadata is not entered into STORET the batch upload will not be successful The customization EDAS User s Guide procedures for EDAS
36. ers soul BigRiver e Side of Route 3 Sample taken from w Blackstone AS Manville Dam Figure 3 Selecting a station to input data Convert Geographic Coordinates from Degrees Minutes and Seconds to Figure 4 Utility for converting Latitude and Longitude degrees minutes seconds to decimal degrees In the lower portion of the stations form are action buttons for entry of four data types and for returning to the start up form Figure 5 The four data types include benthic macroinvertebrates water chemistry habitat assessment and physical characterizations Selection of any of the four data types starts a chain of forms that first define sample information then replicate and personnel data then parameters and values As an example selecting Benthic Macroinvertebrates will bring up the Benthic Macroinvertebrate Samples form If no samples are recorded for the station a prompt asks if the user is prepared to enter sample information A sample date must be typed and entered to save a new sample The form can not be closed without adding a sample date 10 EDAS User s Guide To add a new sample when samples already exist click on the action button with the writing pencil icon Scroll through existing samples using the arrow buttons Field Gear and Sample Collection Procedure information can be entered from pull down menus and is required if data are to be transferred to STORET South Side of Route 3 Sample ta
37. f taxa or individuals have not been classified metrics calculated from the remaining sample are inconclusive Two options available if a large percentage gt 15 20 of taxa or individuals are unclassified are to 1 classify the taxa using the taxa review module of EDAS or 2 exclude from analysis any metrics calculated using 18 EDAS User s Guide limited attributes EDAS does not provide values for the taxonomic attributes Such values are usually available from state biologists or other sources e g Merritt and Cummins 1996 Barbour et al 1999 5 4 Access Database Structure The Access Database Structure option allows access to tables and queries in standard ACCESS views either datasheet or design This option is for advanced EDAS users with sufficient knowledge of the ACCESS application Some of the more common tasks are described in Section 6 0 but users should refer to ACCESS documentation and manuals for more detailed instruction on ACCESS functions For a list of supplemental support texts see Section 8 0 To navigate back to the EDAS forms or front end click on the Start EDAS button in the tool bar Upon selecting the Access Database Structure option the database view is displayed Users can tab through the tables queries forms reports macros and modules Double clicking a table query or form will display that object The tables store the data displayed in forms and reports as well as additional
38. for data entry more metric queries supports a wider variety of data types and better upload capabilities to U S EPA s STORET The distribution copy of EDAS does not have a populated Benthics Master Taxa table This is because of the continual update to the phylogenetic ordering of the organisms and that tolerance values can vary from state to state A Demonstation version of EDAS that is populated with test data will be available This version of the database is for the user to become acquainted with the features of EDAS Modifications of existing tables or field names may have unintended consequences especially any hidden tables or queries Although new tables and queries and new fields within tables and queries may be added 2 0 Relational Databases EDAS is a relational database Databases differ from spreadsheets in the manner that data is stored and referenced As in typical spreadsheets tables in the database store data in rows records and columns fields Each field contains one type of information such EDAS User s Guide as the name of an invertebrate a chemical concentration or geographic coordinates In the database each record of a table is unique with a field or fields that are restricted to unique values The fields with mandatory unique values are called the primary keys of the table Primary keys minimize the opportunity for errors caused by duplicate data entry By referencing the primary keys of one table in
39. formats or codes refer to Table 1 Experimentation is discouraged except using dummy data in fake sites and the DEMOTEST Organization ID as this could mark data in EDAS as being uploaded when it has not been uploaded to STORET only exported from EDAS To create a batch file for upload to STORET 36 EDAS User s Guide 1 Select the Administrator Functions button from the start up form of EDAS enter the password and click OK Figure 18 EDAS Table Maintenance lation Figure 18 Enter Administrator password 37 EDAS User s Guide 2 Be sure that the organization ID is correct and click Create STORET Batch Upload File If this field is not populated this option will not be available Figure 19 R Department of Environmental Quality RDEQ Chemical Parameters Habitat Parameters Counties Ecoregions Hydrologic Unit Codes Physical Characterization Parameters Sample Collection Procedures Benthic Macroinvertebrate Taxa Figure 19 Create STORET Batch Upload File option 38 EDAS User s Guide 3 Select the station s to be included in the batch process If no stations are selected ALL stations will be included in the batch file Figure 20 AbbotNo Abbot Run Brook North AbbotSo Abbot Run Brook South Adamsvil Adamsville Brook Ashaway Ashaway River Bailey s Brook at Route 120 bridge about 50 meters south of bridge Valley Road about 50 meters above bridge
40. habitat assessment form used e g high gradient or low gradient streams 15 EDAS User s Guide EEE ALLG AddiReview Habitat Assessment Replicates Return to Stations Figure 9 Sample collection procedure for habitat assessment samples The Habitat Assessment Data entry form includes the following fields Habitat Parameter Value and Comments The Habitat Parameters available in the pull down Figure 10 pick list can be appended from the Administrator Functions component of EDAS Section 5 5 The values usually refer to scores on predefined scales e g 0 10 or 0 20 16 EDAS User s Guide Embeddedness Velocity Depth Regime Sediment Deposition Channel Flow Status Channel Alteration Frequency of Riffles or bends Bank Stability Left Bank Return to Habitat Assessment Replicates Figure 10 Selection of habitat assessment parameters 5 2 4 Physical Characterizations For Physical Characterizations there are no Sample Collection Procedures or Activity Categories to specify This type of data is flexible because of the broad range of parameters that may be included Figure 11 Some of the Parameter IDs actually contain values and need only to be selected to denote presence of the parameter and value in the sample Other parameters require values to be entered These fields can be modified in the Administrator Functions component of EDAS Section 5 5 17 EDAS User s Guide PaamD
41. hat deletion of a record requires breaking a relationship do not break relationships Breaking relationships may render EDAS inoperable even after rebuilding relationships Just as adding data must be performed in order Section 5 2 deletion of records with data in related tables must be approached in a specific order All data in a replicate must be deleted before the replicate can be deleted Likewise all replicate information must be deleted before the sample can be deleted and all sample information must be deleted before the station can be deleted 19 EDAS User s Guide Data may be imported into EDAS using standard ACCESS functions These functions are explained in detail in ACCESS documentation and manuals Section 8 0 EDAS can import spreadsheet and database tables which can then be used to append or update EDAS tables Section 6 6 This process is an efficient way to migrate multiple records that does not require re keying every record and field Compared to data entry using the EDAS forms there is more opportunity to neglect essential fields when importing data Before running EDAS code using the imported data especially creation of STORET batch upload files be certain that all necessary fields have been populated 5 5 Administrator Functions The Administrator Functions option allows the database administrator DBA to create uploadable files for STORET or maintain the tables that are referenced in EDAS pull down menus Thi
42. icate form Erik 410 356 8993 Erik Leppo tetratech corn Add New Personnel Record Return to Startup Figure 16 Personnel data 5 8 Exit EDAS This button closes the user s session of EDAS and the ACCESS application The Exit EDAS option from the start up form is the safest way to end an EDAS session Closing the program from other windows especially from report windows may disable EDAS components The data entered or edited during any session are automatically saved There is no action required to save data 6 0 EDAS Advanced Features This section goes beyond the standard front end and data entry portions of EDAS introduces some of the aspects of maintaining the user s database This section is intended for database administrators and those wanting to use functions other than those provided in the front end of EDAS As shipped EDAS operates in the Basic mode for access to and a description of the Advanced Mode of EDAS see Section 6 1 25 EDAS User s Guide There are two main sections of the database that are password protected Administrator Functions and Taxa Administation in the Taxa Review Section These sections allow the user to manipulate data within tables in EDAS to which data entry personnel should not have access Passwords may be obtained from Tetra Tech Inc using the contact information provided in Section 11 0 6 1 Advanced Mode of EDAS As distributed EDAS has some of the basic funct
43. ific order of data entry and e such data dependencies can be viewed in the relationships window To create an append query click New from the Query tab in the database view and select Design View Add the newly imported table to the graphical user interface of the query Change the query type to Append Query and select the EDAS table to be appended Stations data is usually appended first Select the fields to be appended and the corresponding fields in the EDAS table Aggregate the query if necessary to eliminate duplicate primary key values View the datasheet for quality control Return to the design view and run the query Update EDAS tables when records already exist in EDAS but fields within those records need to be populated To update a table create a new query including the table to be updated and the table containing the critical data Link the tables in the graphical design interface using unique identifiers from each table Switch to query type Update Query Select the fields to be updated In the Update To row of the query type in the table and field of the source data using the following format source table source field gt If 29 EDAS User s Guide some valid data exists in the field to be updated set the criteria for the field to Is Null Only blank cells will be updated View the datasheet for quality control return to the design view and run the query 6 7 The Hierarchy of EDAS Tables EDAS has a structural ar
44. informed decisions regarding the condition of water resources The majority of biological assessment and monitoring programs in the U S require aggregation of biological data lists and enumeration of taxonomic results into informative indicators multimetric indices Davis and Simon 1995 EDAS is designed to facilitate data analysis particularly the calculation of biological metrics and indices Pre designed queries that calculate a wide selection of biological metrics are included with EDAS EDAS is a custom designed relational database application created by Tetra Tech Inc for use with Microsoft Access 97 Service Release 2b SR2b Minimum system requirements are a Pentium or equivalent processor and at least Windows 95 EDAS provides flexibility in data manipulation through a collection of tables that are linked by one or more fields EDAS can be linked to various other applications e g ArcView Access supports Open Database Connectivity ODBC and Structured Query Language SQL thus making it compatible with most other databases or database software EDAS has the following functionality e Stores many types of ecological data o Benthic macroinvertebrates o Chemistry o Physical habitat o Physical characteristics Calculates metrics for benthic macroinvertebrates Management of data QA QC Reporting of data and results Preparation of flat file for upload to STORET For stability concerns the code portion of the EDAS database has
45. ing Procedures and Gear sampling equipment must be defined in STORET Text can be used to describe Sampling Procedures while Gear must be selected from a list of valid equipment the same list is provided in EDAS Analytical procedures can also be described for chemical parameters Personnel must be identified in STORET First and last names of people working on the project are required Their roles in the data generation process can be specified though it is optional All parameters that are to be uploaded to STORET must be identified by a unique Character Group ID and Row ID To accept batch uploads from EDAS all taxa in the Benthics Master Taxa table and all chemical species must be assigned to a Character Group ID and Row ID These identifying codes can be manipulated by the local EDAS STORET DBA but they must agree between the EDAS and STORET applications Each parameter must also be described in terms of units measured detection limits acceptable data ranges etc It is possible that a taxon or chemical species identified in your samples will not be found in STORET The taxa list in STORET is based on the Integrated Taxa Information Service ITIS list which is constantly undergoing review In addition the list does not support informal naming conventions or life stage information If a parameter is not in STORET you can not assign a Row ID to the parameter and it will be impossible to store the data in STORET You may decide that yo
46. ions of Access restricted e g the standard toolbars queries forms reports To regain the restricted functions and access the Advanced Mode in EDAS hold down the Shift key when opening the database This turns off some the restrictions and restores the normal toolbars to Access Opening the database in this manner also bypasses the opening EDAS startup screen It will be necessary to start EDAS by double clicking on the Startup form or clicking Start EDAS on the tool bar Note starting EDAS in this manner will still not allow the user to save export or edit forms reports macros or modules in the MDE version of EDAS 6 2 Saving and copying EDAS Data entered into tables is automatically saved in EDAS No commands are needed to save data Queries can be saved when created or revised by selecting the Save icon floppy disk in the toolbar The entire database can be saved and copied using MS Explorer or another file management program Saving under a new name is highly recommended to create back up copies or before experimenting with advanced features 6 3 Database Maintenance Occasionally it becomes necessary to compact or even repair your database These are both standard features of Access but repairing a database has been restricted to Advanced users For access to this function see section 6 1 The reason to compact your database is that Access stores much information that the user never sees but takes up space within the da
47. ken from west shore md West Greenwich HR Rhode Island 7 Reference Benthic Macroinvertebrates Habitat Assessments Water Chemistry Physical Characterizations Return To Startup Figure 5 Entering station information When sample information is complete proceed to the replicate window by clicking the Add Review Replicates action button The Activity Category must be entered for each replicate except for Physical Characterization data For Benthic Macroinvertebrates the Activity Category will be Routine Sample for the first replicate of any sample Additional replicates should be assigned an Activity Category of Field Replicate Other Activity Categories with other data types should be checked for compatibility with STORET see STORET User s Manual Replicates are added by clicking on the action button with the writing pencil icon Figure 6 Scroll through existing replicates using the arrow buttons The Personnel Name field must be completed for any records that are to be transferred to STORET The Personnel Name and Role pull down menus can be edited using the Review Personnel Information component from the EDAS start up screen Section 5 7 If the Personnel field is edited incorrectly delete it by pressing the escape key A person can be assigned to a replicate only once with a single role 11 EDAS User s Guide A Return to Stations Figure 6 Adding replicate information After completing the replicate f
48. l aquatic resources has led to the development of biological assessment techniques to measure biological integrity Biological surveys biocriteria and bioassessments complement physical and chemical assessments of water quality by reflecting cumulative effects of human activities and natural disturbances on a waterbody including the possible causes of these effects Approaches and methods for biological assessment and biocriteria development have been demonstrated in a series of guidance documents issued by U S EPA e Rapid Bioassessment Protocols for Use in Streams and Wadeable Rivers Periphyton Benthic Macroinvertebrates and Fish Second Edition EPA 841 B 99 002 e U S Environmental Protection Agency U S EPA 1998 Lake and reservoir bioassessment and biocriteria technical guidance document U S Environmental Protection Agency Office of Water Washington D C EPA 841 B 98 007 e U S Environmental Protection Agency U S EPA 1996 Biological criteria Technical guidance for streams and small rivers U S Environmental Protection Agency Office of Water Washington D C EPA 822 B 96 001 e Gibson G R M L Bowman J Gerritsen and B D Snyder 2000 Estuarine and coastal marine waters Bioassessment and biocriteria technical guidance EPA 822 B 00 024 U S Environmental Protection Agency Office of Water Washington D C A critical component of any biological monitoring and assessment program is data management data reducti
49. les queries can also be used to filter data with criteria analyze data e g metrics or QAQC statistics or to reorganize data from several tables into a single spreadsheet Such spreadsheets may be exported for more complex statistical analysis or graphing The queries in EDAS mostly metrics are Select Queries Select Queries can filter data by setting criteria perform mathematic and logical operations and display the results in tabular format Simple analyses can be easily carried out using Select Queries determine the number of sites with sufficient biological sample size for analysis select only samples collected during a given time period find the average value of a parameter recorded multiple times at a site etc Complete instructions on constructing queries should be sought in ACCESS documentation and manuals Section 8 0 Only Select Queries are available in the normal mode of EDAS Building new Select Queries and manipulating the existing queries will not alter EDAS functionality nor will it change results of the metrics displayed in the Calculate RBP Metrics component Additional information on working with select queries is provided in Section 6 8 Developing Metric Queries Other query types are available in the Advanced Mode of EDAS The following brief explanations should be supplemented by reference to ACCESS documentation and manuals Cross Tab Queries summarize data from one or more database tables and display the data as
50. m west shore Blackstone AS Manville Dam iks gt AS Millville Rt 12 Biological Figure 24 Selecting community 43 EDAS User s Guide 7 Ifyou are confident in your data integrity and completeness select the Prepare Batch File button Figure 25 allow duplicate batch uploads Abbot Run Brook North Abbot Run Brook South Adamsville Brook Ashaway River Bailey s Brook Beaver River Big River Blackstone AS Blackstone AS Figure 25 Selecting to prepare batch upload file This will update EDAS tables and will not at Route 120 bridge about 50 meters south of bridge Valley Road about 50 meters above bridge AtUSGS gage below culvert At Rt 216 below bridge About 150 meters below dam At Kernpenaar s Clambake Below culvert at parking lot At Shannock Hill Road About 50 meters south below pool South Side of Route 9 Manville Dam Millville Rt 122 hore ple taken from 44 EDAS User s Guide 8 Review the batch file and the un uploaded data file Figure 26 Stations Abbot Run Brook North at Route 120 bridge about 50 meters south of bridge Abbot Run Brook South Valley Road about 50 meters above bridge Adamsville Brook AtUSGS gage below culvert Ashaway River AtRt 216 below bridge About 150 meters below darn Bailey s Brook At Kempenaar s Clarnbake Below culvert at parking lot Beaver River At Shannock Hill Road About 50 meters south below SER Big River South Side of Ro
51. medii uo eec coitu eed EE cet eee ee 41 Figure 23 Selecting mitent t 0gei n secon ter bac ttr bein erecti cte EESE ab ae Lea Eaa 42 Figure 24 Selecting community iei iet epe ur ER RH AR EORIT Loc EROR ETE ed PE ES eto QURE 43 Figure 25 Selecting to prepare batch upload file o ee e tr teretes 44 Figure 26 Reviewing un nloaded d ta e Eee aded 45 EDAS User s Guide List of Tables Table 1 Naming conventions for metric calculation queries esses 31 Table 2 STORET data fields that must be populated or described prior to batch upload and fields that are populated during batch uploads from EDAS Minimal requirements are shown other options are available depending on customization of DS LORET ror 32 Table 3 EDAS fields required for generation of successful batch file for STORET 35 vi EDAS User s Guide 1 0 Introduction The objective of the Clean Water Act of 1972 is to maintain and restore the chemical physical and biological integrity of the Nation s waters At that time the discharge of chemicals was the primary source of pollution to our waterbodies Since 1972 we have been largely successful in reducing the number and quantity of chemicals discharged into waterbodies However it has also become clear that aquatic ecosystems are impacted by more than just chemicals The recognition that chemical water quality analyses do not adequately predict or reflect the condition of al
52. n Using Microsoft Access 97 Que Corporation Indianapolis IN ISBN 0 7897 0916 3 Teaches you how to master the new features of 32 bit Access 97 and the Jet 3 5 database engine Uses step by step instructions for creating relational tables to programming with VBA 5 0 and the Jet 3 5 Data Access Object Serves as a guide of the fundamentals of working with Access tables and queries Goes into detail of form and report design 49 EDAS User s Guide writing Jet SQL Provides advice for replicating your database use Access across a network with multi user databases and creating client server applications using ODBCDirect e Callahan E 1997 Microsoft Access 97 Visual Basic Step by Step Microsoft Press Redmond WA ISBN 1 57231 319 6 Step by step self training guide through Access 97 Visual Basic Contains information on how to automate database tasks add command buttons to a form find and filter records in a form write functions and event procedures create a customer user interface work with data and objects update data with action queries etc 9 0 Glossary of Terms Included below is a short list of terms that users should be familiar with when using Access and EDAS EDAS Ecological Data Application System A database developed in Access to aid in the analysis of ecological data Excluded Taxa A field in EDAS used to mark taxa to not be counted for taxa richness metrics Those marked as excluded taxa will s
53. nd should not be edited except by someone with code experience Existing database elements e g fields within tables table names or query names should be left alone as parts of the code may refer to them Modifying table names or field names may compromise the integrity and functionality of the code embedded within EDAS and may have unexpected consequences The distributed copy of EDAS is an MDE Microkernel Development Environment file that allows the file to be smaller and run faster by compiling and removing the source Visual Basic VB code This allows full functionality with less chance for damaging the file as there is no code to edit or change There are several restrictions that are imposed by the MDE file format e You cannot view any module in the database You cannot create any new forms reports or modules You cannot change any of the VBA code You cannot export or import forms reports or modules however you can export and import tables queries and macros Litwin et al 1997 EDAS User s Guide Tetra Tech Inc will provide unlocked versions of EDAS upon request but will not support users of such modified versions This version of the database will be provided as is with no guarantees This is because of the embedded code and once the end user takes possession of the database the integrity of the code cannot be verified System requirements for EDAS are a Pentium class processor with 64 Megabytes of RAM
54. nvironmental Protection Agency Office of Water Washington D C EPA 841 B 98 007 lt http www epa gov owow monitoring tech lakes html gt U S Environmental Protection Agency U S EPA 1998b STORET Version 1 1 User Guide and Reference Manual U S EPA Office of Water SDC 0055 074 EB 8014 Available on STORET CD ROM Technical Support 1 800 424 9067 U S Environmental Protection Agency U S EPA 1996 Biological criteria Technical guidance for streams and small rivers U S Environmental Protection Agency Office of Water Washington D C EPA 822 B 96 001 lt http www epa gov OST biocriteria States streams streams0 html gt 51 EDAS User s Guide 11 0 Contact Information Chris Faulkner USEPA HQ Mail Code 4503F Ariel Rios Bldg 1200 Pennsylvania Ave NW Washington D C 20460 Faulkner Chris epa gov Tetra Tech Inc 10045 Red Run Blvd 110 Owings Mills MD 21117 Erik Leppo tetratech com Benjamin Jessup G tetratech com 52
55. o taxa being collected 5 2 2 Water Chemistry The Water Chemistry data entry form contains the fields Parameter Units Method Value Below Detection and Comments The Parameter and Units fields can be selected from a single pull down pick list These fields can be edited in the Administrator Functions component of EDAS Section 5 5 The Method field denotes analytical methods as opposed to the field Sample Collection Method or Field Gear described in the Water Chemistry Sample form Completion of this field is optional but recommended It is not included in automatic batch upload files for STORET The Below Detection checkbox can be checked when concentrations are below detection limits for the analytical method Figure 8 14 EDAS User s Guide Parameter Units Method Value BelowDetection Commi P ate a e mg l as P mg l mgl m NTU Azimuth ug l Return to Water Chemistry Replicates Figure 8 Selection of water chemistry parameters 5 2 3 Habitat Assessment The screens leading up to the Habitat Assessment Data entry form are somewhat different than the other Sample and Replicate forms The Sample Collection Procedure field in the Habitat Assessment Samples form is optional Figure 9 The Activity Category can be either a Routine or Replicate Measurement Observation Msr Obs in the Replicates form Also in the Replicates form the Form Version must be selected The Form Version refers to the type of
56. on calculation and statistical analysis This manual acquaints the user with EDAS Ecological Data Application System an application for data management data reduction and initial data analysis for aquatic biological monitoring data from water quality monitoring programs In the past data collected as part of state bioassessment programs were entered stored and analyzed in easily obtainable spreadsheet programs This method of data management becomes cumbersome as the database grows in volume An alternative to spreadsheet programs is a multiuser relational database management system RDMS Most relational database software is designed for the Windows operating system and offer menu driven interfaces and ranges of toolbars that provide quick access to many routine database tasks Automated tools help users quickly create forms for data input EDAS User s Guide and lookup tables reports and complex queries about the data The U S EPA is developing a multiuser relational database management system that can transfer sampling data to STORET This relational database management system is EDAS Ecological Data Application System and allows the user to input compile and analyze complex ecological data to make assessments of ecosystem condition EDAS includes tools to format sampling data so it may be loaded into STORET as a batch file These batch files are formatted as flat ASCII text and can be loaded transferred electronically to STORET
57. orm proceed to the Add Review Benthic Data form or other data type by clicking on the appropriate action button Station sample and replicate information will appear at the top of the screen Figure 7 For each data type a taxon or parameter must be selected from pull down menus the critical value must be entered and qualifying codes units or comments can be added 12 EDAS User s Guide Final Individuals Stage Excluded Taxa Commens X Ephemerellidae Ephemeroptera Erpobdella punctata Erpobdellidae Eubrianax Return to Benthic Replicates Figure 7 Adding benthic macroinvertebrates to a sample 5 2 1 Benthic Macroinvertebrates The Benthic Macroinvertebrate data entry form shows the Final ID Individuals Stage Excluded Taxa and Comments fields pertaining to the selected Station Sample and Replicate The Final ID can be edited with a pull down menu that displays all taxa in the Master Taxa Table alphabetically Figure 7 Highlighting a taxa will enter it in the field Taxa can be added by typing a new Final ID and following the prompts Any taxa that are added in this manner will not have associated attributes taxonomic hierarchy functional feeding group tolerance value character group ID row ID etc until these attributes are edited in the Master Taxa Table through the Review Taxa Information component Section 5 6 In the Individuals field enter the number of individuals of the taxon identified in
58. oup ID Available categories of results groups of taxa or parameters Row ID within Characteristic Groups Available specific results taxa chemical species habitat parameters etc Additional Parameter Information several required fields Units detection limits acceptable value range etc STORET fields populated during batch uploads from EDAS Description of field Visit number A number of each visit to the station within the same trip Activity ID Identifies the field activity often a sample Replicate Number Uniquely identifies replicates Start date Date that sampling started Activity Type Sample or Field Msr Obs measurement observation Activity Category Further defines activity type e g routine sample field replicate etc Result Group General data type Medium if activity type is sample Biological Water Soil Air etc Intent if medium is biological Community if intent is taxon abundance Describes data type enumeration tissue bioassay etc Description of assemblage sampled Sample Collection Procedure ID Identifies specific collection methods Field Gear ID Analytical Procedure ID Identifies specific collection equipment Identifies analytical laboratory procedures when applicable Personnel People responsible for sample collection processing and or data entry Characteristic Group ID Categories of p
59. quired by STORET for first time entry of visit and activity data to STORET BenReps or Activity category Routine Sample or Field Replicate ChemReps VisactSuffix Inserts the Add statement required by STORET for first time entry of visit and activity data to STORET Personnel LastName FirstName Names of responsible sample and data processors Benthics Character Group ID Class of results taxa or chemical group Row ID Numeric code for specific parameter within the Character Group taxon or chemical Individuals The count of individuals of each taxon Uploaded Y or N this field is automatically maintained by EDAS 6 9 3 Create Batch File Manual editing of this field will probably result in unsuccessful batch uploads to STORET Batch files can be created automatically within the Administrator Functions dialog of EDAS Batch file creation requires a password and should only be performed by the local STORET and EDAS DBA This function automatically marks those records that were included in batch uploads and will not allow records to be uploaded twice Reversal of batch file creation is complex at best and attempts are highly discouraged Files batched after reversal attempts may cause errors during uploads to STORET Therefore it is important that all steps have been carefully performed prior to batch file creation In particular required fields must be completed using valid data
60. r basis monthly annually and should never be stockpiled The data transfer process batch file creation upload to local STORET and transfer to the national STORET should be completed without long interruptions Table 3 EDAS fields required for generation of successful batch file for STORET Table Field Acceptable data format or codes N A Trip ID Four digit year of sample collection automatically generated by EDAS from sample collection date Organization Organization ID DEMOTEST or unique value assigned by the STORET DBA Stations Station ID Unique ID for Station BenSamps VisitNumber Numerical station visit among multiple visits during the or same trip to which the data are related Chemsamps Activity ID Unique identifier for the sample automatically generated by EDAS Activity Type Sample Field Gear ID STORET code designating sample collection equipment CollMeth Method by which sample was collected a k a STORET Sample Collection Procedure CollDate Sample collection date a k a STORET start date 35 EDAS User s Guide Medium Biological or Water Intent Taxon Abundance if medium is Biological Community Benthic Macroinvertebrates if Intent is Taxon Abundance BioResultGroupID 01 if intent is Taxon Abundance signifying that the results are for Multiple Taxa Population Census VisactSuffix Inserts the Add statement re
61. rs and possibly even render some components inoperable This is due to the creation of temporary tables that will only be deleted by backing out of each component and returning to the start up window A demonstration database populated with hypothetical data is available for experimentation Testing of data entry and migration procedures using the demonstration database is recommended before such procedures are attempted with the valid EDAS copy 5 1 Calculate RBP Metrics for Samples The calculation of biological metrics from benthic macroinvertebrate abundance data is a primary operation of the EDAS program Automatic calculation of Rapid Bioassessment EDAS User s Guide Protocol RBP metrics is easily accomplished by selecting the option from the start up form The subsequent form will allow selection of sites and or a range of dates Figure 2 If no stations or dates are selected metrics for all stations and dates will be calculated Bailey s Brook At Kempenaar s Clambake Below culvert at parkin Beaver River At Shannock Hill Road About 50 meters south bel Big River South Side of Route 3 Sample taken from west sh Blackstone AS Manville Dam Blackstone AS Millville Rt 122 BlackRBP Blackstone River RBP below Manville Darn BlackiVioo Blackstone AS Woonsocket Singleton St Return to Startup Figure 2 Example of selecting a station to calculate RBP metrics Metrics are calculated on the contents of the Benthics tabl
62. rted into spreadsheet or database files that recognize the commas as field separators The directory lt C EDAS gt must exist or the user must specify an existing directory or this function will not work Reports organize data in one page summaries of metrics and associated sample information Clicking on the printer icon in the toolbar will send all reports to the default printer Return to the results window by closing the reports window close in the file pull down menu or the lower X in the upper right corner Selecting Start EDAS exit or the upper X from the reports window could cause a fatal error within EDAS Other metrics besides those in the RBP component can be calculated and displayed in EDAS using individual queries These metrics and queries are accessed from the Access Database button on the Start screen and are described in further detail in Section 6 8 The forms and reports can be modified using Visual Basic code in the Advanced version of EDAS available upon request 5 2 Add New Data The Add New Data component of EDAS facilitates the basic and necessary task of data entry Users click on this action button when they are prepared to enter data from field or laboratory data sheets or when entering basic station information The data entry forms appear in a logical sequence allowing data entry of all types station and sample information benthic macroinvertebrates water chemistry habitat assessments physical
63. s set of features is password protected to guard against possible misuse by data entry personnel Passwords can be obtained by contacting Tetra Tech Inc see Section 11 0 for contact information Creating a STORET batch upload file is an advanced operation explained in Section 6 9 The table maintenance feature allows data in pull down menus to be edited added and deleted To edit a table simply select it from the list of tables and click the Access Table action button Though only a limited number of fields will appear in pull down menus on data entry forms the entire table can be edited in this view units Character Group IDs Row IDs Comments etc New records can be added by typing in the empty cells at the bottom of the table For a blank database there is a feature that allows the user to populate some tables Ecoregions Counties and HUCS based on location Figure 12 20 EDAS User s Guide R Department of Environmental Quality Rhode Island Chemical Parameters Habitat Parameters Physical Characterization Parameters Sample Collection Procedures Benthic Macroinvertebrate Taxa Figure 12 Population of tables based on location 5 6 Review Taxa Information The Benthic Master Taxa table can be reviewed and edited using the Review Taxa Information function This function opens a form that displays all the attributes of a single taxa on one screen Figure 13 Knowledgeable biologists can edi
64. stomized data includes information about your organization program and methods The data codes ensure proper labeling of all transferred data For example each taxon is identified by a Character Group and Row and those identifying codes must be established in both EDAS and STORET Table 2 STORET data fields that must be populated or described prior to batch upload and fields that are populated during batch uploads from EDAS Minimal requirements are shown other options are available depending on customization of STORET STORET fields that must be populated Description of field before import of batch files Organization ID Identifies the source of the data Program ID Identifies the program within the organization Project ID Identifies a specific project within the program Trip ID Identifies the year the sample was taken Station ID Identifies a specific sampling site Additional Station Information several Geographic coordinates waterbody type state county required fields and descriptive data associated with each station 32 EDAS User s Guide STORET fields that must be described before import of batch files Description of field Sample collection methods analytical procedures and personnel several required fields Available procedures equipment and personnel within each project e g Sample Collection Procedure ID Field Gear ID Personnel etc Characteristic Gr
65. t any of the fields and the DBA can verify the information after entering a password 21 EDAS User s Guide Ephemerellidae Add A New Taxon Arthropoda Ephemeroptera Ephemerellidae Correct Spelling of Taxon Name Revise TSN ParentTSN CharGrouplD Taxa Administration Return To Previous Form Figure 13 Benthic macroinvertebrate taxa review Taxa can be selected from the alphabetical list of Final IDs For each taxon selected the taxonomic hierarchy tolerance values functional feeding group habit and voltinism attributes are displayed These attributes can be edited by typing in the appropriate fields The spelling of the Final ID can also be edited by selecting the Correct Spelling of Taxon Name action button Other options available to any reviewer include adding a taxon importing a taxa list adding a photo reviewing the sites where the taxa was sampled or editing the Taxonomic Serial Number TSN Parent TSN or Characteristic Group Taxa that are added individually or from an imported list will not have associated attributes The attributes must be typed into the appropriate spaces of the form To import a taxa list using the Review Taxa Information component of EDAS first prepare a list in the first field of an EXCEL spreadsheet Taxa list imports can also be performed by importing complete tables and appending data to the Benthics Master Taxa table Section 6 6 Adding a photo to represent a taxon is easily ac
66. tabase Also deleted data tables take up space until the database is compacted Compacting a database removes blank records will reset auto numbers to be consecutively ordered from the highest existing number allows the database to run faster and saves space when saving To compact a database click on EDAS Utilities from the toolbar and then select Compact Database Figure 17 26 EDAS User s Guide EDAS 3 0 EDAS_3 0 Database m Ex File Window Help la xi Start EDAS Li SB EDAS utilities select sl Sort HA o Relationships Compact Database j Activity Info Contents and Index Personnel PersonnelRole Tables I Reports Macros amp amp Modules BenPerson BenReps PhysChar_Parameter_Descriptions PhysCharPerson PhysCharReps PhysCharSamps ES BenSamps ES Benthics Benthics Master Taxa ChemPerson PhysCharValues ES ChemReps PhysCharValues Units ChemSamps SampleAssembly ES ChemValues SampleCollectionProcedures SourcesforTaxalnformation StateNeighbors ES ChemValues Parameter Descriptions ij ChemValues Units Stations Functional Feeding Groups ES Habit Behavior ES HabPerson HabReps ij HabSamps E HabValues ES HabValues Parameter Descriptions Es Organization Ready pex pepe puspespeet pos Figure 17 Compacting the database 6 4 Working with Tables Tables should be familiar to anyone exploring this section
67. te information eese enne enne 12 Figure 7 Adding benthic macroinvertebrates to a sample sees 13 Figure 8 Selection of water chemistry parameters eseseeeeeeeeeeeeenenen 15 Figure 9 Sample collection procedure for habitat assessment samples 16 Figure 10 Selection of habitat assessment parameters sse 17 Figure 11 Selection of physical characterization parameters esee 18 Figure 12 Population of tables based on location eee 2l Figure 13 Benthic macroinvertebrate taxa review ssesseeeeeeeeeeeeeee eee 22 Figure 14 Adding a photo to the Taxa Review page Photo courtesy of Leonard Smock Virginia State University sco eee eee Qe e eei c uu antananencyeseees 23 Figure 15 Samples common to a specific taxa Selected from a button on the Taxa BREVIS We Ae doe e E E aid epe Rue 24 Fig re L6 Personnel ddtqooius dieere 8 oss ce tote dens tas hd e tanita suede eet Condes walenue 25 Figure 17 Compacting the database iei ren a de URS e teases dessaceas 27 Figure 18 Enter Administrator password eese nennen nennen 37 Figure 19 Create STORET Batch Upload File option seen 38 Figure 20 Selecting stations for batch upload eee 39 Fig re 21 Selecting activity type tenseso Seed Open a a tie draio 40 Ligure 22 Selectine
68. the sample The Final ID cannot appear more than once in a replicate unless the Stage or Habitat fields are unique Stage refers to the life stage of the specimen Adult Larva Nymph Pupa and Habitat from where organisms were collected e g snags riffles leaf packs etc Habitat is not used by most programs but was included for those who wish to use it For taxa that are normally sampled in only one aquatic life stage the field can remain unaltered X Otherwise an abbreviation denoting life stage can be entered Habitat is not an EDAS required field 13 EDAS User s Guide An Excluded Taxa check box has been included in the Benthics table for those occasions when an organism is identified in a sample but is not to be included in taxa richness metrics For instance small or damaged specimens that can only be identified to the family level in the same replicate with taxa of the same family that are identified to the genus level should be excluded from richness calculations The Excluded Taxa field is not automated but must be manually checked The Excluded Taxa field should not be used for terrestrial specimens as these should not be entered into any metric calculations for multiple life stages or for habitats Terrestrial specimens including non aquatic life stages exuvia or empty shells should be noted in the Comments field of the Benthic Macroinvertebrate Samples form Also note in the comments field if a sample resulted in n
69. till be included in percentage based metrics This is a feature if used allows the user to mark a taxon to not be counted if other taxa of the same phylogenetic level e g family are present in the same sample but are identified a lower level e g genus QA QC Quality Assurance and Quality Control Practices used to ensure the integrity of your data SQL an acronym pronounced as sequel SQL is a Structured Query Language for processing data SQL is a standardized database query language This language gives Access the ability to link to other data management applications that use SQL STORET a repository for water quality biological and physical data and is used by state environmental agencies EPA and other federal agencies universities private citizens and many others 10 0 Literature Cited Barbour M T J Gerritsen B D Snyder and J B Stribling 1999 Rapid Bioassessment Protocols For Use in Streams and Wadeable Rivers Periphyton Benthic Macroinvertebrates and Fish Second Edition EPA 841 B 99 002 U S Environmental Protection Agency Office of Water Washington D C lt http www epa gov OWOW monitoring rbp gt 50 EDAS User s Guide Callahan E 1997 Microsoft Access 97 Visual Basic Step by Step Microsoft Press Redmond Washington ISBN 1 57231 319 6 Catapult Inc and Microsoft Press 1997 Microsoft Access 97 Step by Step Microsoft Press Redmond Washington ISBN 1 57231 316 1
70. ur parameter was incorrect and change it to a STORET acceptable parameter This linkage is made through the Row ID The name used in EDAS is not required to match the STORET name Data may be stored under one name in EDAS and a different name in STORET as long as the Character Group IDs 34 EDAS User s Guide and Row IDs match You may decide not to upload the data that is mis matched between EDAS and STORET In this case notify the regional STORET administrator of the missing parameter and upload the data when it becomes available in STORET 6 9 2 Prepare EDAS Populate data tables through import or data entry functions User must populate required fields list by table using acceptable formats Must assign codes exactly as in STORET e g taxon character group and row ID s sample collection procedures etc Some fields must use STORET default values and other fields can be customized in STORET The fields that must use STORET default values include Organization ID Activity Type Activity Category Medium Intent Community and Bio Result Group Organization ID will be DEMOTEST or a unique value assigned by the STORET database administrator DBA STORET has strict data formatting requirements that are satisfied using automatic functions in EDAS for data entry and batch file generation Table 3 STORET is sensitive to batch files created from data that were excessively managed Batch file uploads should be performed on a regula
71. ute 3 Sample taken from Blackstone AS Manville Darn Millville Rt 122 Activity Type Meciurn Intent axon Abundance Create New Batch File Return to Previous Form Community Figure 26 Reviewing un unloaded data 9 Export the batch file to a valid directory Do NOT skip this step as you will lose the ability to retrieve the batch file after exiting this function 10 Print the un uploaded data file as a record of data that were incomplete These data should be included in future batch uploads or can be entered manually into STORET 6 9 4 Import Batch File EDAS prepares the batch upload file but does not perform the actual upload nor will it in future versions The file created automatically in EDAS is in rich text format and must be converted to ASCII DOS text or text only This conversion can be accomplished in several standard word processing programs by opening the rich text file and saving as text only The file should appear in the following format for biological data with standard headers data or codes after commas no blank lines and no spaces unless they are characters of the header or data 45 EDAS User s Guide Organization ID DemoTest Trip ID 2000 Station ID TestSite Visit Number 1 ADD Activity ID B 123 ADD Activity Type Sample Activity Category Routine Sample Medium Biological Intent Taxon Abundance Community Benthic Macroinvertebrates Start Date 09 01 2000 S

Download Pdf Manuals

Related Search

Related Contents

取扱説明書 - オプテックス  User Manual (N50T122-403)  Web Caster 720 取扱説明書  Cables Direct CDL-012 USB cable    Istruzioni per l`uso  EV_cat26C ing_sp  Monitor and True Up Budget Description  DE Originalbetriebsanleitung GB Translation of the original  MVI69-PDPS User Manual  

Copyright © All rights reserved. Failed to retrieve file