Home

database user`s manual - The Confederated Tribes Of The Colville

1. Appendix A Page 10 ptbl VIDEODATA MAINTENANCE SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE DATE MAINTENANCE Date Time Date of Maintenance TIME MAINTENANCE Long Integer Time of Mainenance hourly PCT MOTION Double Percent Motion Detection Partition TIMELAPSE MBFREE Long Integer Time Lapse MBytes Free MOTION MBFREE Long Integer Motion MBytes Free NOTES Text Notes VERSION Text HIST SAMPLEID Long Integer ptbl VIDEODATA STATUS SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE CAMERA Long Integer Camera CAMERA DATE START Date Time Start Date dd mmm yy VIDEO STATUS Text Video Status Complete or Partial NO MD CLIPS Double Number of MD Clips Reviewed LIGHT ON Yes No Light On or Off VIS NEAR WHT Yes No Visibility White Near VIS NEAR BLK Yes No Visibility Black Near Yes No Visibility White Far Yes No Visibility Black Far HISTSAMPLEI D Long Integer see CAMERA also see ptbl INDIVIDUAL FISH DATA Appendix A Page 11 atbl FLOW Sitel D Ikp SITES Date Time Start Date dd mmm yy Date Time Start Time 24 hour Double Flow CFS Text Qualifier Ikp_QUAL_FLOW Text Agency CCT ET DOE ENVIRONMENT CANADA USGS Text COMMENTS Text
2. 6 Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL lii 28 August 2007 1 0 INTRODUCTION 1 1 PROJECT BACKGROUND Since May 2006 Summit Environmental Consultants Ltd has been working with the Colville Confederated Tribes CCT to develop a fully functional Microsoft Access database designed and constructed for the Okanogan Basin Monitoring and Evaluation Project OBMEP The OBMEP project includes many sampling efforts data collected and managed by the CCT specifically snorkel surveys trap surveys video surveys habitat surveys redd surveys water chemistry analyses and hourly river water temperature measurements In addition to these sampling efforts the CCT also manages data collected by other agencies such as the Wells Dam Fish Passage data the Department of Ecology DOE water quality temperature and flow data the United States Geological Survey USGS temperature and flow data and the Environment Canada flow and temperature data The purpose of this manual is to provide Colville Confederated Tribes staff with details regarding development and use of the database including the final design the data entry forms and pre designed output queries reports and form templates 1 2 PROJECT OBJECTIVES The general objective of this project is to develop a database design in Microsoft Access format to accommodate the various data types that CCT manages The design stan
3. see SITES QUAL FLOW Appendix A Page 12 Text Sitel D Ikp SITES Date Time Date dd mmm yy Date Time Time 24 hour Double Water Temperature deg C Double Air Temperature deg C Text Agency CCT DOE ENVIRONMENT CANADA USGS Text COMMENTS see SITES Appendix A Page 13 atbl GATES Date Time Date dd mmm yy Date Time Time 24 hour Long Integer Long Integer Long Integer Long Integer Appendix A Page 14 atbl PROTOCOL BIOEVENT BioEvent SNORKEL TRAP VIDEO FISH PASSAGE HOURLY TEMP WATER QUALITY FLOWDATA REDD AGENCY Agency WELLS DAM BOXCAR DOE CCT USGS ENVIRONMENT CANADA PROTOCOL DESC DATA DESC Description of Data SOURCE Data Source Appendix A Page 15 Ikp CAMERA CAMERA Long Integer CAMERA SITENAME Text SITENAME Ikp SITES Bank Text Bank Ikp CHEMDI CT CHEMCODE CHEMCODE CHEMNAME Chemical Name Units Description Method STORETCODE Storet Code DL Detection Limit USGSCODE USGS Code Ikp CREW CREW ID Unique 2 Digit Initials of Sampling Crew use 3 if needed CREW NAME Full Name of Crew Member ORGANIZATION Name of Organization Crew Member is affiliated with WORK TITLE Work
4. Text Depth of water sample USGS chemistry Text HABITAT Length of entire sample reach from transect A through transect K meters USGS WO Location in cross section distance from left bank looking downstream feet VIS DIST START REDD Text Visibility in meters at the start of the redd survey VIS DIST END REDD Text Visibility in meters at the end of the redd survey Single Visibility at site in feet Text Visibility Poor Fair Good SURFACE AREA Text Surface area square miles LATITUDE HABDATA Text Latitude Habitat Data Individual transects LONGITUDE HABDATA Text Longitude Habitat Data Individual transects RUN Text Run Fish passage data SPRING SUMMER FALL COMMENTS Text Comments DATA COLLECTED Yes No Data Collected Yes or No BI OEVENT Text BioEvent SNORKEL TRAP VIDEO FISH PASSAGE HOURLY TEMP WATER QUALITY REDD Text Agency DOE CCT WELLS DAM BOXCAR Text WQ CONVERT Double USGS Water Quality Conversion Factor HIST SAMPLEID Long Integer HIST VIDEOSAMPLEID Long Integer see SITES CREW HARDDRIVE Appendix A Page 1 ptbl HAB CANOPY SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE TRANSDIR Text Position on transect CU Center facing up CL Center facing left CD Center facing down CR Cen
5. Organic carbon water unfiltered milligrams per liter p00680 OCHROMONAS 4 USGS ACL p96608 OOCYSTIS 4 USGS ACL p96218 Collected in acid washed passenger acid preserved shipped on ice EIM Method was EPA365 3M prior to 05 01 SM4500PG Organic nitrogen water filtered milligrams per liter Appendix B Page 12 00607 ORG_N_UNFILT Organic nitrogen water unfiltered milligrams per liter 900605 OSCILLATORIA OSCILLATORIA 4 VAUCHER 1803 098136 OXYGEN Winkler with biiodate correction for thiosulfate after Feb 89 EPA360 2 P_FILT Phosphorus water filtered milligrams per liter p00666 P_FILT_JIRKA Phosphorus water filtered modified jirka method milligrams per liter 099893 P UNFILT Phosphorus water unfiltered milligrams per liter p00665 P UNFILT AS P Phosphorus water unfiltered milligrams per liter as phosphate p71886 P UNFILT JIRKA Phosphorus water unfiltered modified jirka method milligrams per liter p99891 PANDORINA PANDORINA 4 USGS ACL p96038 Pb Lead Tot Rec EPA200 8 Pb DIS Lead Dissolved EPA200 8 PERIPHYTON ASH Biomass periphyton ash weight grams per square meter p00572 PERIPHYTON DRY Biomass periphyton dry weight grams per square meter p00573 PH Gel probe infrequent
6. Acid neutralizing capacity water unfiltered incremental titration field milligrams per liter as calcium carbonate p00419 ACHNANTHES 4 USGS ACL p96777 Silver Dissolved EPA200 8 Silver Tot Rec EPA200 8 AGMENELLUM 4 BREB 1839 098090 Alkalinity Alkalinity water filtered incremental titration field milligrams per liter as calcium carbonate SM2320 939086 ALUMINUM Aluminum water filtered micrograms per liter 901106 AMMONIA_N_FILT Ammonia plus organic nitrogen water filtered milligrams per liter as nitrogen Appendix B Page 2 900623 AMMONIA_N_JIRKA Ammonia plus organic nitrogen water filtered modified jirka method milligrams per liter as nitrogen 999894 AMMONIA_N_SUS Ammonia plus organic nitrogen suspended sediment total milligrams per liter as nitrogen 00624 AMMONIA_N_UNFILT Ammonia plus organic nitrogen water unfiltered milligrams per liter as nitrogen p00625 AMMONIA UNFILT Ammonia plus organic nitrogen water unfiltered modified jirka method milligrams per liter as nitrogen p99892 AMMONIA 4 FILT Ammonia water filtered milligrams per liter as NH4 071846 AMMONIA_NH4_UNFILT Ammonia water unfiltered milligrams per liter as NH4 71845 AMMONIA_UNFILT Ammonia water unfiltered milligrams per liter as
7. RLqry 31 SpCounts SNORKEL 31 SpCounts TRAP NONSAL ENUM RLqry 31 SpCounts TRAP SAL ENUM RLqry 31 SpCounts VIDEO RLary 31 SPRich FISH PASSAGE RLqry 31 SPRich SNORKEL RLary 31 SPRich TRAP NONSAL ENUM RLqry 31 SPRich TRAP SAL ENUM RLqry 31 SPRich VIDEO RLary 32 FISHDENSITYperWETTED SURFACE AREA RLqry A WATERYEAR FLOW RLqry A WATERYEAR FLOW check RLqry A WATERYEAR FLOW Crosstab RLqry A WATERYEAR FLOW Crosstab 365 days RLqry B WATERYEAR TEMP RLqry B WATERYEAR TEMP check RLqry B WATERYEAR TEMP Crosstab RLqry WATERYEAR WO RLqry C WATERYEAR WO check RLqry C WATERYEAR WO enterchemcode Crosstab RLary C WATERYEAR WQ nochemcode RLary C WATERYEAR WQ nochemcode check RLqry C WATERYEAR WQ nochemcode Crosstab RLqry_CONVERTDATE_UNIVFORMAT RLqry_D_FLOW_MOVINGAVG RLary D FLOW WATERYEAR AVGMINMAX RLqry J SpCounts TRAP NONSAL ENUM trapping period RLary TIMEDIFF Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 11 28 August 2007 4 2 2 Reports Reports are used to summarize the data in tables or calculated in queries The current database consists of XXX pre designed output reports Each of these reports has the prefix REPORT and is listed in Table 4 3 Table 4 3 Pre designed output reports _Report Name Description X X X The
8. Temperature deg C Thermister in river TEMPTHERMF TETRAEDRON 4 USGS ACL p96226 TETRASTRUM 4 USGS ACL p96245 Kjeldahl Nitrogen Total mg L EPA351 2 Total Organic Carbon mg L EPA415 1 M Total coliform M p31501 31503 MPN 100mL Total coliform completed test water most probable number per 100 milliliters p31507 Collected in acid washed passenger acid preserved shipped on ice Sometimes from MEL as SM4500PI sometimees as EPA365 1 Appendix B Page 16 EPA365 1 TP_PlnLine Latchet In line digestion Probable high bias in TP though not in OP or TPLL with this method Collected in acid washed passenger acid preserved shipped on ice Sometimes from MEL as SM4500PI sometimees as EPA365 1 SM4500PI Collected in acid washed passenger acid preserved shipped on ice Manchester reg10 VAX call this P100021 Former method valderama SM4500NB TRACHELOMONAS RACHELOMONAS 4 USGS ACL p96392 Turbidity Turbidity water unfiltered nephelometric turbidity units SM2130 P82079 900076 Turbidity All turbidities collected prior to 10 79 were set to this code JTUs on 7 feb 03 00070 Vanadium water filtered micrograms per liter 901085 Zinc water filtered micrograms per liter 901090 Zinc suspended sediment recoverable micrograms
9. Text Size class code of particle RS Bedrock smooth RR Bedrock BL Boulder LCB Large Cobble SCB Cobble tennis to softball GC Coarse Gravel GF Fine Gravel SA Sand FN Silt Clay Much HP Hardpan WDz Wood OT Other Iko SUBSTRATE EMBED_PCT Double Estimated embeddedness of particle 0 100 COMMENTS Text Descriptions for qualifiers OLDSAMPLEID Long Integer see SUBSTRATE Appendix A Page 3 ptbl WQ CHEMISTRY SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE CHEMCODE Text CHEMCODE CHEMDICT Text Value Text field for making tables includes qualifiers Double Value Number field Double Value Use this for calculations negative turbidity are zeros Text Qualifiers QUAL WQCHEM Text Agency DOE CCT COMMENTS Text Comments HISTSAMPLEI D Double OLDCHEMCODE Text see CHEMDICT QUAL WQCHEM Appendix A Page 4 FISH I NDIV SAMPLEID DATE VIDEO CAMERA VIDEO TIME CREWMEMBER SPECIES FORKLENGTH WEIGHT SEX 1 MARK TYPE 2 MARK TYPE Long Integer Date Time Long Integer Long Integer Text Text Double Double Text Text Text SamplelD ptbl MASTER SAMPLE Date of the Video Observance Camera Number sorted by SITENAME for Video Data Time fish collected 24hr Initials of Biosampler Species Ikp SPECIES Fork Length
10. follow the directions under Tools Security User Level Security Wizard For more information on user security measures refer to the MS Office Access Help under the Help menu 6 0 DATABASE LIMITATIONS Using MS Access presents some challenges to the database user Database users must be forewarned that all changes to the database are permanent and cannot be reversed using Undo commands e g deleting a table deleting a record updating information in a cell etc Because of this care must be taken when working with the database tables Caution is also needed when exporting the data into other applications For example MS Access and MS Excel often interpret formatted text in slightly different ways which can result in errors when exporting or importing data e g text formatted as a date in MS Excel is imported into MS Access as a string of meaningless numbers Therefore it is recommended that the database be backed up prior to importing large amounts of data and that checks be conducted after the data is imported to ensure that data quality has been maintained e g date format number of fields number of records In this way if bad data is imported it is possible to undo the changes by reverting to the backed up copy The same checks should be employed when exporting data to ensure that the exported product is correctly formatted and contains the correct information Database users need to be familiar
11. 12 lookup tables prefix 1 and 4 alternate tables prefix A detailed description of the database components is provided in Appendix A This appendix includes a description of the database design the content of each database table and a description of the information contained in each of the fields that comprise the tables Appendix B lists the standardized entries in the lookup tables e g Ikp Chemdict Ikp Sites Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 5 28 August 2007 Figure 3 1 Summary of the table relationships in the database design Relationships for WORKING CCT DB 18 Jul 07 July 18 2007 Ikp LIFESTAGE _ SALMONID ENUM REDD WQCHEM KELDATA EXTERNALMARK bl HAB HUMAN kp SUBSTRATE VIDEODATA STATUS VIDEODATA_ MAINTENANCE Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 USER S MANUAL 6 28 August 2007 The MASTER SAMPLE is the parent table for all of the other tables in the database The data tables are the tables that store the various types of data and the MASTER SAMPLE table contains relational fields i e key variables that remain consistent throughout the main tables of the database e g SAMPLEID It is important to use this table when creating and using queries reports and forms as it keeps the database connected thus making queries
12. Tools menu click Database Utilities and then click Compact and Repair Database Note that this utility will not function when the database is being shared among two or more users I recommend running the Compact and Repair utility weekly and make a backup copy prior to compacting 8 0 QUALITY ASSURANCE QUALITY CONTROL It is important to understand how data quality is maintained through standardized QA QC procedures The first step in the QA QC process involves verifying the data against the original data source Following translation of the data into the database the verified data Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 17 28 August 2007 should be further audited Pre import backup copies of the database should not be discarded until this verification is complete This auditing process involves analyses of outliers e g to identify potential inconsistencies with units and completeness e g to identify missing samples or missing data and checking sample identification numbers e g to ensure that data were not duplicated The auditing process should be regarded as a part of data entry and should not be put off or left even overnight If another person begins adding to the database after new data is imported but before it is checked and it turns out that data did not import correctly the backup copy will no longer be current and the current copy will contain errors Never b
13. milligrams per liter 071856 Nitrite water filtered milligrams per liter as nitrogen 900613 Nitrite Nitrogen EPA353 2 NO2 NO3 Collected in acid washed passenger acid preserved shipped on ice EIM Method was EPA353 2 prior to 09 00 SM4500NOSI NO2 FILT Nitrite plus nitrate water filtered milligrams per liter as nitrogen p00631 NO2 NO3 UNFILT Nitrite plus nitrate water unfiltered milligrams per liter as nitrogen p00630 NO2 UNFILT AS N Nitrite water unfiltered milligrams per liter as nitrogen p00615 FILT Nitrate water filtered milligrams per liter p71851 FILT AS Nitrate water filtered milligrams per liter as nitrogen Appendix B Page 11 p00618 NO3_N Nitrate Nitrogen EPA353 2 NO3_UNFILT Nitrate water unfiltered milligrams per liter p71850 NO3_UNFILT_AS_N Nitrate water unfiltered milligrams per liter as nitrogen 900620 O_PO4 Orthophosphate water filtered milligrams per liter p00660 O PO4 FILT AS P Orthophosphate water filtered milligrams per liter as phosphorus p00671 O PO4 UNFILT AS P Orthophosphate water unfiltered milligrams per liter as phosphorus p70507 Organic carbon water filtered milligrams per liter p00681 Organic carbon suspended sediment total milligrams per liter p00689
14. mm Weight g Sex of fish Male Female Unknown Primary Mark EXTERNALMARK Secondary Mark Ikp EXTERNALMARK TAG NO Text Tag Number PITTAG NO Text PitTag Number DNA VIAL NO Text DNA Vial BROODSTOCK Yes No Broodstock Yes No BROODYEAR Text Broodyear KELT Yes No Kelt Yes No LIFESTAGE Text AGE Long Integer Age INJURY Yes No Injury Yes No MORTALITY Yes No Mortality Yes No MORTALITY CAUSE Text Cause of Mortality STATUS Text DIRECTION MIGRATING Text QUANTITY Long Integer Quantity default of 1 ARCHI VED Yes No COMMENTS Text Comments REVIEWER Text Crew Member QAQC BY Text Crew Member DATE REVIEW Date Time DATE QAQC Date Time BI OEVENT Text Bioevent VI DEO TRAP OLD TIME Date Time HIST SAMPLEID Long Integer VERSION Text EXTERNAL MARK Ikp LIFESTAGE Appendix A Page 5 Fish type mortalities smolts juvenile YOY adults Ikp_LIFESTAGE Status Trasported to hatchery for broodstock TH Passed upstream PU Passed Downstream PD Direction Migrating Upstream Downstream Was this motion clip archived Date Reviewed dd mmm yy Date of QAQC dd mmm yy Time fish collected 24hr includes minutes SPECIES ptbl
15. nitrogen 900610 ANABAENOPSIS 4 USGS ACL 098166 ANACYSTIS 4 MENEGH 1837 p98082 ANKISTRODESMUS 4 USGS ACL p96202 Arsenic Dissolved Dissolved Arsenic EPA200 8 or ICP MS P1000 Arsenic water filtered micrograms per liter p01000 Arsenic suspended sediment total micrograms per liter Appendix B Page 3 p01001 Arsenic Tot Rec TOTAL RECOVERABLE ARSENIC EPA200 8 Arsenic water unfiltered micrograms per liter 901002 ASTERIONELLA 4 USGS ACL 096763 Barium water filtered micrograms per liter 901005 Barium suspended sediment recoverable micrograms per liter 001006 Barium water unfiltered recoverable micrograms per liter 001007 Beryllium water filtered micrograms pet liter 901010 Bicarbonate water unfiltered fixed endpoint 4 5 titration field milligrams per liter 900440 BICARB_TITR_FILT Bicarbonate water filtered incremental titration field milligrams per liter 900453 BICARB_TITR_UNFILT Bicarbonate water unfiltered incremental titration field milligrams per liter 099440 p00450 BIOMASS_CHLOR number Biomass chlorophyll ratio periphyton number 970950 BORON_FILT ug L Boron water filtered micrograms per liter 901020 BORON_UNFILT ug L Boron water unfiltered recoverable micro
16. per liter Appendix B Page 17 01091 Zinc water unfiltered recoverable micrograms per liter p01092 Zinc Tot Rec or Zinc Total EPA200 8 P1094 or Zinc Dissolved Appendix B Page 18 Ikp_CREW CREW_ID CREW_NAME ORGANIZATION WORK_TITLE EMAIL PHONE Bryan Nass LGL Ltd Contractor bnass lgl com 509 962 8294 Chris Fisher Colville Confederated Tribes Anadromous Fisheries Biologist III Chris Fisher colvilletripbes com 509 422 7427 Colette Louie Okanagan Nation Alliance Fisheries Technician Edward Berrigan Colville Confederated Tribes Fisheries Technician Esteban Cruz Colville Confederated Tribes Summer Youth Eliott Tonasket Okanagan Nation Alliance Fisheries Technician Fred Jordan Colville Confederated Tribes Fisheries Technician Fred Jordan colvilletribes com John Arterburn Colville Confederated Tribes Anadromous Fisheries Biologist Il John Arterburn colvilletribes com 509 422 7424 Jordan Leskinen Colville Confederated Tribes Fisheries Technician 509 322 3060 Keith Kistler Colville Confederated Tribes Anadromous Fisheries Biologist Keith Kistler colvilletribes com 509 422 7429 Kari Long Okanagan Nation Alliance Fisheries Biologist 250 707 0095 Kevin Manuel Colville Confederated Tribes Fisheries Tech
17. reports and forms possible The database includes a user interface switchboard that enables beginner database users to open the entry forms even when they do not have experience with the database design These forms open in a mode that allows for addition of new data 4 0 DATABASE PROCEDURES This section is intended to provide database users with instructions to support the entry and retrieval of information from the database using queries reports and forms The following sections will describe the pre designed queries reports and forms in the database design In addition this section will also outline procedures for the following e Using data entry forms e Running the pre designed output queries reports and forms e Creating simple queries e Creating simple forms and e Exporting output queries reports and forms for presentation 4 1 DATA ENTRY FORMS The current database design consists of four 4 data entry forms listed in Table 4 1 To simplify data entry the data entry forms were designed to mimic the current field forms used by the CCT field staff Each of these data entry forms has the prefix ENTRYFORM for sorting purposes The forms with the prefix SubEntryForm are subforms incorporated into complete data entry forms Importantly the sub entry forms have not been designed for Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 7 28 August 2007 independen
18. 0 75 4 Very heavy 275 US VEG Understory Vegetation type Deciduous Coniferous Evergreen broadleaf Mixed or None US_WOOD Proportion of UNDERSTORY as WOODY Shrubs and saplings 0 Absent 1 Sparse lt 10 2 Moderate 10 40 3 Heavy 40 75 4 Very heavy 275 US_NONWOOD Proportion of UNDERSTORY as NON WOODY Herbs grasses and forbs O Absent 1 Sparse lt 10 2 Moderate 10 40 3 Heavy 40 75 4 Very heavy 275 GC_WOOD Proportion of GROUNDCOVER as WOODY Shrubs and saplings 0 Absent 1 Sparse lt 10 2 Moderate 10 40 3 Heavy 40 75 4 Very heavy 275 GC_NONWOOD Proportion of GROUNDCOVER as NON WOODY Herbs grasses and forbs O Absent 1 Sparse lt 10 2 Moderate 10 40 3 Heavy 40 75 4 Very heavy gt 75 GC_BARE Doub Proportion of GROUNDCOVER as BARE 0 Absent 1 Sparse lt 10 2 Moderate 10 40 3 Heavy 40 75 4 Very heavy gt 75 GC_LWD Doub Proportion of GROUNDCOVER as LWD 0 Absent 1 Sparse lt 10 2 Moderate 10 40 3 Heavy 40 75 4 Very heavy gt 75 OLDSAMPLEID Long Integer ptbl HAB SUBSTRATE SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE STANUM Text POSITION Text Position on transect L Left bankfull stage LC left of center C center RC right of center R right bankfull stage DEPTH Double SIZE CLASS
19. B Page 23 Ikp_QUAL_WQCHEM QUALID possible quality problem with the result Actual value is known to be less than the value shown Actual value is known to be greater than the value shown Average value Reported result is an estimate Value is greater than result reported The analyte was positively identified The associated numerical result is an estimate Converted from older remark codes with various definitions Result should be considered an estimate Presence of material verified but not quantified Presumptive evidence of presence of material Most probable value The analyte was not detected at or above the reported result USGS Material specifically analyzed for but not detected The analyte was not detected at or above the reported estimated result Value affected by contamination lt gt A E G J J M c lt C oz Appendix B Page 24 APPENDIX C Original Requests for Outputs Analyst PBI PBI PBI PBI PBI PBI ISEMP ISEMP ISEMP ISEMP PBI PBI PBI PBI PBI ISEMP ISEMP PBI PBI ISEMP WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE ISEMP WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE W
20. COUNT Long Integer Count MORTALITIES Long Integer Number of Mortalities HIST SAMPLEID Long Integer VERSION Text ptbl TRAPDATA SALMONID ENUM SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE Text Species Ikp SPECIES Yes No AD Clipped Yes No LI FESTAGE Text Life Stage Ikp LIFESTAGE COUNT Long Integer Count of individuals of each FISH TYPE MORTALITIES Long Integer Number of Mortalities HIST SAMPLEID Long Integer VERSION Text ptbl TRAPDATA SALMONI D MARK RECAP SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE SPECIES Text Species IKp SPECIES TOTALNO MARKED Long Integer Total Marked i e dyed TOTALNO RECAP Long Integer Total Recaptured HIST SAMPLEID Long Integer VERSION Text see Ikp LIFESTAGE Ikp SPECIES also see ptbl INDIVIDUAL FISH DATA Appendix A Page 8 ptbl SNORKELDATA SAMPLEID Long Integer TRANSECT Text SPECIES Text SIZE CLASS Text NUM FISH Integer VERSION Text DELFISH CLASS Text HIST SAMPLEID Long Integer see SIZECLASS SPECIES Appendix A Page 9 SamplelD ptbl MASTER SAMPLE Transect A K Species Ikp SPECIES Descriptive size range Ikp SIZECLASS 100mm 100 300mm gt 300mm Number of fish counted
21. DE Text Channel or Side Channel INCREMENT Double Spacing between thalweg measurements meters for Habitat Data DATE START Date Time Start Date dd mmm yy Date Time End Date dd mmm yy Date Time Start Time 24 hour Date Time Stop Time 24 hour CREWMEMBER1 Text Crew Member CREW CREWMEMBER2 Text Crew Member CREW CREWMEMBER3 Text CREWMEMBER4 Text Crew Member Ikp CREW Crew Member CREW CREWMEMBERS Text Crew Member Ikp CREW CAMERA Long Integer Camera Number sorted by SITENAME HARDDRI VE Double Hard Drive HARDDRIVE DATE REVIEW Date Time Date Reviewed dd mmm yy DATE QAQC Date Time Date of QAQC dd mmm yy DISCHARGE Text Discharge CFS RPM TRAP Doub Trap RPM at Start WEATHER Text Weather TIME REDEPLOY Date Redeploy Start Time RPM REDEPLOY Doub Redeploy RPM 5FT 8FT Text 8 foot or 5 foot FISHING POS Long Integer Fishing Position 1 or 2 WATER TEMP START Double Start Water Temperature F WATER TEMP END Double End Water Temperature F WATER TEMP C Double Water Temperature C AIR TEMP Text Air Temperature C PRESSURE Text Barometric pressure millimeters of mercury Single Average width of stream at site in feet
22. DRAFT REPORT VERSION 1 OKANOGAN BASIN MONITORING AND EVALUATION PROJECT OBMEP DATABASE USER S MANUAL Prepared for Prepared by Colville Confederated Tribes Summit Environmental Consultants Ltd 23 Brooks Tracts Rd 200 2800 29th Street Omak WA 98841 Vernon B C VIT 9P9 Project 652 01 07 August 2007 SUMM Ur Ss iitants Lt c SUMMIT w ENVIRONMENTAL CONSULTANTS LTD ISO 9001 AND 14001 CERTIFIED August 28 2007 Reference 652 01 05 Mr John Arterburn Anadromous Fisheries Biologist Colville Confederated Tribes Fish and Wildlife Department 23 Brooks Tracts Rd Omak Washington 98841 Dear Mr Arterburn Re OBMEP Database User s Manual 200 2800 29th Street Vernon BC VIT 9 9 TEL 250 545 3672 FAX 250 545 3654 www summit environmental com Summit Environmental Consultants Ltd is pleased to provide Draft Version 1 of the User s Manual for the Microsoft Access Okanogan Basin Monitoring and Evaluation Project OBMEP database The purpose of this manual is to provide Colville Confederated Tribes staff with details regarding development of the database including the database design the data entry forms and pre designed output queries reports and form templates Also included are step by step database procedures for running pre designed queries reports and forms creating simple queries creating pivot chart forms and exporting output queries reports and forms for presentation and i
23. ENASTRUM SELENASTRUM 4 USGS ACL 996225 SES_SED_SIEVE Suspended sediment sieve diameter percent smaller than 0 063 millimeters 070331 mg L Silica water filtered milligrams per liter 900955 ug L Silver water filtered micrograms pet liter 901075 ug L Silver suspended sediment recoverable micrograms per liter 001076 ug L Silver water unfiltered recoverable micrograms per liter p01077 Specific Conductivity ms cm Specific conductance water unfiltered microsiemens per centimeter at 25 degrees Celsius 900095 microsiemen per centimeter at 25 degrees Celsius Specific conductance water unfiltered laboratory microsiemens per centimeter at 25 degrees Celsius 990095 colonies per 100 milliliters Fecal streptococci KF streptococcus MF method water colonies per 100 milliliters Appendix B Page 15 031673 ug L Strontium water filtered micrograms per liter p01080 mg L Sulfate water filtered milligrams per liter p00945 SULFATE_UNCORR mg L Sulfate water filtered uncorrected milligrams per liter 999890 SUS_SED_DISCHARGE tons per day Suspended sediment discharge tons per day p80155 SUS_SED_MPL mg L Suspended sediment concentration milligrams per liter p80154 Sussol mg L SM2540D SYNEDRA 4 USGS ACL 996765
24. FISH PASSAGE SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE SPECIES Text Species Ikp SPECIES LIFESTAGE Text LifeStage Ikp_LIFESTAGE for Chinook and Coho WILD_HATCH Text Wild or Hatchery SteelHead only COUNT Long Integer Count of Fish HISTSAMPLEID Double DELSPECIES Text see Ikp_SPECIES Ikp LIFESTAGE Appendix A Page 6 ptbl REDD SAMPLEID Long Integer Samplel D ptbl MASTER SAMPLE FLAGCOLOR Text Color of flagging used to mark new redds in the current survey Long Integer Start numbering at one and number each redd equentially as you survey the reach Long Integer Number of redds seen at this location Long Integer Number of fish with an adipose fin Long Integer Number of fish without an adipose fin Long Integer Number of fish that you can t tell if they have an adipose fin or not Long Integer Maximum vertical depth at the deepest redd observation Text Cardinal direction from the flag to the redd or group of redds N NE NW S SE SW E W Long Integer Distance m from the flag to the redd or group of redds Double Latitude Decimal Degrees Double Longitude Decimal Degrees Appendix A Page 7 TRAPDATA NONSALMONID ENUM SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE SPECIES Text Species Ikp SPECIES
25. MINACT present P2 10m C Within 10m B On bank Presence proximity of HUMAN INFLUENCE Mining Activity O Not present P gt 10m C Within 10m B On bank HU_DIVERT Presence proximity of HUMAN INFLUENCE Diversion O Not present P gt 10m C Within 10m B On bank Appendix A Page 2 UNSTABB Long Integer Percentage of 10 m length of bank at bankfull stage at each transect that appears unstable due to breakdown slumping cracking or bare steep surfaces 999 missing data COMMENTS Text Descriptors for qualifiers and general comments OLDSAMPLEID Long Integer ptbl HAB LGWOOD SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE LWD_GT_1M Double Large woody debris greater than 1m LWD_GT_2M Double Large woody debris greater than 2m OLDSAMPLEID Long Integer ptbl HAB RIPARIAN SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE Text Transect direction LB Left Bank plot RB Right Bank plot Text Canopy Vegetation type Deciduous Coniferous Evergreen broadleaf Mixed or None CP BIGTREE Double Proportion of canopy cover from BIG TREES gt 0 3m DBH 0 Absent 1 Sparse lt 10 2 Moderate 10 4096 3 Heavy 40 75 4 Very heavy 275 SMTREE Double Proportion of canopy cover from SMALL TREES 0 3m DBH 0 Absent 1 Sparse 1096 2 Moderate 10 4096 3 Heavy 4
26. OLDSAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE ptbl HAB HUMAN SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE Text Transect direction LB Left Bank plot RB Right Bank plot Text Presence proximity of HUMAN INFLUENCE Wall Revetment Riprap Dam O Not present P gt 10m C Within 10m B On bank Presence proximity of HUMAN INFLUENCE Buildings O Not present P gt 10m C Within 10m B On bank HU_RIVACC Presence proximity of HUMAN INFLUENCE River Access Sites O Not present P gt 10m C Within 10m bank HU PVMT Presence proximity of HUMAN INFLUENCE Pavement Cleared Lot O Not present P2 10m C Within 10m B On bank HU PIPE Presence proximity of HUMAN INFLUENCE Pipes as inlet or outlet Oz Not present P2 10m C Within 10m B On bank HU TRASH Presence proximity of HUMAN INFLUENCE Garbage Landfill Trash O Not present P2 10m C Within 10m B On bank HU CLEAR Presence proximity of HUMAN INFLUENCE Cleared Lot Park Lawn OzNot present P2 10m C Within 10m B On bank HU CROP Presence proximity of HUMAN INFLUENCE Orchards Row Crops OzNot present P2 10m C Within 10m B On bank HU PSTR Presence proximity of HUMAN INFLUENCE Pasture Range Hay Field O Not HU LOG present P2 10m C Within 10m bank Presence proximity of HUMAN INFLUENCE Logging Operations O Not HU
27. SDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE WSDOE ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP ISEMP Source Pacific Biodiversity Institute s GIS classification Pacific Biodiversity Institute s GIS classification Pacific Biodiversity Institute s GIS classification Pacific Biodiversity Institute s GIS classification Pacific Biodiversity Institute s GIS classification Pacific Biodiversity Institute s GIS classification Upstream catchment derived from 30m National Elevation Dataset Upstream catchment derived from 30m National Elevation Dataset Stream length derived from 1 100k National Hydrography Dataset Plus Attribute from 1 100k National Hydrography Dataset Plus Pacific Biodiversity Institute s GIS classification Pacific Biodiversity Institute s GIS classification Pacific Biodiversity Institute s GIS classification Pacific Biodiversity Institute s GIS classification Pacific Biodiversity Institute s GIS classification Road length determined from GIS based TIGER road layer Road length determined from GIS based TIGER road layer Pacific Biodiversity Institute s GIS classification Pacific Biodiversity Institute s GIS classification Determined from 30m National Elevation Dataset Field observa
28. Snorkel report Please have a look at the report The excel sheet for the snorkel is what would like to have in a report 5 Temperature So far have done the same for temperature as flow would like to have this kind of report along with Johns spread sheet report no excel sheet included Email from Mike Rayton dated May 11 2007 This was sent to me today from Kris Peterson at NOAA Fish as an example of the type of monthly report we need to submit in order to remain in compliance with our smolt monitoring activities specifically the write ups on pages 4 amp 5 Email from MR dated May 14 2007 have a question about parsing the rotary data believe that the Access program is tallying fish by calendar date 00 00 hours through 23 59 This unfortunately does not adequately reflect our trapping period Since we trap from approximately 19 00 to 04 00 hours on two separate calendar days the query counts fish collected from two separate overnight periods 00 00 to 04 00 and 19 00 to 23 59 Is there a way to write a query I know there must be a way but easy way that solves this problem RLary J SpCounts TRAP NO NSAL ENUM trapping period Phone Requests from KK and MR How can we calculate the time spent each day in the traps RLqry_K_TIMEDIFF Appendix C2 Page 2
29. TCd VBWidth VBConfine VBGrad RdDens RipRdindx Naiman valley bottom type in which the site is located ask Steve Numeric code for Naiman valley bottom type in which the site is located ask Steve LndOwn Primary owner of land upstream from the site Remote LndUse Primary land use upstream from the site Remote Elev Elevation m above sea level of the site LIDAR ChanTypLtr Rosgen channel type in which the site is located ChanTypCd Numeric code for the Rosgen channel type ChanGrad BFTypNa Montgomery Buffington bed form type within the site BFTypCd Sinuos RchLnth WetSurfArea Fine Snd FnGrav CrsGrav Cob SmBld LgBld Hrdpn RBedr SBedr Concr Wood Other DomSubCd Embedd LWD10 LWD15 LWD30 PIPool DamPool TotPool ResPIDpth FCAlgae FCMacroBryo Numeric code for the Montgomery Buffington bed form type Sinuosity measured within the site avg wetted width x reach length LWD GT 1M LWD GT 2M per KM PRIMARY POOL COUNT per REACH BEAVER POOL COUNT per REACH FCLrgWd Mean that large wood makes up areal extent of fish cover within the site Very similar metrics are collected 1 Proportion of canopy as Proportion of Understory FCBrsh Mean that brush makes up areal extent of fish cover within the site T
30. Title of Crew Member Ex Fisheries Technician EMAIL E Mail Address PHONE Phone Number EXTERNALMARK External Mark AD CA EL FL JT LV OT PC RV TR UM UN Description l HARDDRI VE HARDDRIVE Double HardDrive Number Text HardDrive Type Long Integer Total MBytes on Drive Ikp LIFESTAGE FISHTYPE ADULT ALEVIN FRY JUVENILE PARR SMOLT Description Alternate description STAGECODE Stagecode Ikp OUAL FLOW Oualifier ptbl WO CHEMISTRY Description of Oualifier Oualifier ptbl WO CHEMISTRY Description of Oualifier Text Site identifier assigned to each site OBMEP plus 3 digit site number Text Strata used in the survey design Text Identifies Base sample by panel number and Oversample by OverSamp STREAMNAME Text Name associated with the site DESCRIPTION Text Site description provided by CCT crew COUNTRY Text Country USA or Canada LONG DD Double Longitude Decimal Degrees Appendix A Page 16 Double Latitude Decimal Degrees Double Length of Site Double Altitude of land surface feet LANDOWNER Text Landowner REACH_CODE Text Reach Code ACTIVITY Text Activity CONTACT_NUMBER Text Contact Number Long Integer Long Integer SNORKEL_SORT Lo
31. _SUS Chromium suspended sediment recoverable micrograms per liter 001031 CHROMIUM UNFILT Chromium water unfiltered recoverable micrograms per liter p01034 CHROOMONA CHROOMONAS 4 USGS ACL p96426 Carbon dioxide water unfiltered milligrams per liter p00405 Cobalt water filtered micrograms per liter p01035 Cobalt suspended sediment recoverable micrograms per liter p01036 COBALT UNFILT Cobalt water unfiltered recoverable micrograms per liter p01037 COCCONEIS COCCONEIS 4 USGS ACL p96778 Color Pt Co Color water filtered platinum cobalt units EPA110 2 p00080 Conductivity umhos cm Daily instrument calibrations Former method SM2510 B SM2510B ug L Copper water filtered micrograms per liter Appendix B Page 6 p01040 COPPER_SUS Copper suspended sediment recoverable micrograms per liter p01041 COPPER_UNFILT Copper water unfiltered recoverable micrograms per liter p01042 COSMARIUM 4 USGS ACL p96313 Chromium Tot Rec hex and tri EPA200 8 Chromium Dissolved hex and tri EPA200 8 CRUCIGENIA 4 USGS ACL p96240 CRYPTOMONAS 4 USGS ACL p96430 CRYPTOPHYCEAE 1 USGS ACL p96423 Copper Tot Rec EPA200 8 P1042 or Copper Dissolved EPA200 8 P1040 CYCLOTELLA 4 USGS ACL
32. al reports and email requests demonstrating typical output calculations and graphs The sampling protocols for all data types being considered in the database design Links to internet sources of data collected by other agencies and managed by CCT Summit reviewed the original data files to identify database fields key variables and field types Unique queries were run on each field to determine the range of data that are collected and to review the arrangement of fields and tables The results of these queries were used to identify design modifications that will make the database run more efficiently Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 2 28 August 2007 2 2 MODIFICATION OF THE DATABASE DESIGN The original OBMEP database design received in July 2006 has been modified to a fully functional relational database These modifications include addition of lookup tables and lookup fields that enforce referential integrity of the data as well as indexing fields which will allow queries to run more efficiently To facilitate data entry into the database pre designed entry forms and or Excel templates can be used by users familiar with the database design Pre designed queries reports and forms were developed through review of annual reports and email requests from CCT staff 2 3 CONSISTENCY EVALUATION Summit has had numerous discussions with Steve Rentmeester Environmenta
33. calibration Appendix B Page 13 PHMETERF p00400 PH_LAB SU pH water unfiltered laboratory standard units 900403 PHYTOPLANKTON cells mL Phytoplankton total cells per milliliter 60050 PINNULARIA PINNULARIA 4 USGS ACL 096793 PO4 mg L Phosphate water unfiltered milligrams per liter p00650 PRESS Pressure mm Hg RESIDUE DISS tons per day Residue water dissolved tons per day p70302 RESIDUE EVAP mg L Residue on evaporation dried at 180 degrees Celsius water filtered milligrams per liter p70300 RESIDUE FILT tons per acre Residue water filtered tons per acre p70303 RESIDUE FILT sum mg L Residue water filtered sum of constituents milligrams per liter p70301 RHOICOSPHENIA RHOICOSPHENIA 4 USGS ACL p96779 SALINITY Salinity SCENEDESMUS SCENEDESMUS 4 USGS ACL p96243 SCHIZOTHRIX SCHIZOTHRIX 4 KUETZ 1843 p98150 SCHROEDERIA SCHROEDERIA 4 USGS ACL p96190 Bed sediment dry sieved sieve diameter percent smaller than 0 063 millimeters 80164 Selenium water filtered micrograms per liter Appendix B Page 14 p01145 SELEN_SUS Selenium suspended sediment total micrograms per liter p01146 SELEN UNFILT Selenium water unfiltered micrograms per liter p01147 SEL
34. d is by using Adobe Acrobat software to print the image to a file Reports on the other hand can be exported to Snapshot format snp which retains the graphics and formatting of the report However the best way to export reports is also using Adobe Acrobat software to print the image to a file Once the desired image has been printed to Adobe Acrobat format pdf the image can be manipulated i e text added or changed using Adobe Acrobat software or saved as a jpeg or png image which is easily inserted into Microsoft Word There is also other software available which allows for more detailed manipulations of pdf files e g merging rotating of images such as Adobe Photoshop 5 0 DATABASE SECURITY Microsoft Access has user security measures for protection of the database Implementing these measures can control the level of access different users to the database For example permissions could be set up such that the default user i e anyone who may not be familiar with the database would only have access to select queries reports and forms and Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 4652 01 07 OBMEP USER S MANUAL 15 28 August 2007 would not be able to modify any of the database design or contents More advanced users would enter their unique password in order to be able to modify the database design or contents To enable User Security on the database file currently on your computer server
35. dardizes the format of the various data types so that they can be combined for data analyses and reporting Specific objectives of this project are to e Develop a Microsoft Access database design to accommodate data e Develop uploading procedures i e the protocol document under separate cover e Develop appropriate analysis routines e Lead a training workshop to demonstrate the database operations to CCT staff and e Provide continuous support for uploading and developing analysis routines Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 1 28 August 2007 The purpose of the final report is to 2 0 2 1 Provide a description of the development methods e g data source review consistency evaluation of other available databases and collaboration with CCT staff Present the database design including the pre designed output queries reports and forms Provide database procedures e g running pre designed queries reports and forms creating simple queries creating pivot chart forms graphs and exporting output queries reports and forms Provide solutions for common troubleshooting problems and Describe the next phase of development DEVELOPMENT METHODS REVIEW OF DATA SOURCES The CCT has provided Summit with the following Original OBMEP database Excel spreadsheets containing examples of various data types and outputs collected and managed by CCT Annu
36. divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area summed biomass divided by wetted surface area summed biomass divided by wetted surface area summed biomass divided by wetted surface area summed biomass divided by wetted surface area summed biomass divided by wetted surface area summed biomass divided by wetted surface area summed biomass divided by wetted surface area summed biomass divided by wetted surface area summed biomass divided by wetted surface area Variable Wtrshed Yr SmpEvnt Description Name of watershed within the subbasin in which the site is located e g Nason Visit number that the site was sampled for habitat in a given year all Okanogan SurveySubType e g Day or Night irrelevant BailEcoR Name of the dominant ecoregion described by Bailey 1 ask Steve OmerEcoRNa Name of the ecoregion described by Omernik 1 ask Steve OmerEcoRCd Numeric code for the name of the ecoregion described by Omernik 1 ask Steve Prov Name of the Province in which the site is located 1 ask Steve GeolNa Name of the dominant geologic type upstream from the site ask Steve GeolCd Numeric code for the name of the dominant geologic type ask Steve BsnArea BsnRelf DrainDens VBTLtr VB
37. eck RLary B WATERYEAR TEM P Crosstab RLqry C WATERYEAR WQ RLqry C WATERYEAR WQ check RLqry C WATERYEAR WQ enterchemcode Crosstab note need to go into the design and type in the CHEMCODE RLqry C WATERYEAR WQ nochemcode RLqry C WATERYEAR WQ nochemcode check RLqry C WATERYEAR WQ nochemcode Crosstab Email from KK dated May 14 2007 1 Flow have charted a 12 year moving average versus the current year flow for all sites in the database also have plotted a 12 year trend using average flow for the year low flow average for the year and high flow average for the year RLary D FLOW MOVINGAVG RLary D FLOW WATERYEAR _AVGMINMAX CHART_D_FLOW_AVG MIN MAX CHART_D_FLOW_MOVINGA VG 2 Habitat What have done so far is to take all of the parameters that we collect similar to your stoplight spread sheet and done comparisons by year Then have charted these parameters against each other in simple charts to see how the data compares year top year What would be good for now is to take your stoplight spread sheet and chart those values with confidence intervals against each other by year Then make simple graphs like have done in the examples to do Appendix C2 Page 1 3 Water Quality would like to see charts made of all the values we doe collect by site using a 12 year moving average versus the current year have done this for some of the values in the example 4
38. ed micrograms per liter p01046 IRON SUS Iron suspended sediment recoverable micrograms per liter 01044 IRON_UNFILT Iron water unfiltered micrograms per liter 071885 IRON_UNFILT_RECOV Iron water unfiltered recoverable micrograms per liter 901045 Potassium 082068 Potassium water filtered milligrams per liter 900935 KIRCHNERIELLA KIRCHNERIELLA 4 USGS ACL Appendix B Page 8 996215 LEAD_FILT Lead water filtered micrograms per liter 901049 LEAD_SUS Lead suspended sediment recoverable micrograms per liter 901050 Lead water unfiltered recoverable micrograms per liter 001051 Lithium water filtered micrograms per liter 901130 LYNGBYA 4 AGARD 1824 098131 MALLOMONAS 4 USGS ACL 996595 MASTOGLOIA 4 USGS ACL 096790 MELOSIRA 4 USGS ACL 096707 MERCURY_FILT Mercury water filtered micrograms per liter 071890 MERCURY_SUS Mercury suspended sediment recoverable micrograms per liter 071895 MERCURY_UNFILT Mercury water unfiltered recoverable micrograms per liter 071900 Magnesium water filtered milligrams per liter p00925 Manganese water filtered micrograms per liter p01056 Manganese suspended sediment recoverable micrograms per liter p01054 Manganese water unfilte
39. egin a data import unless there is adequate time to complete the QA QC stage immediately afterwards In general auditing the database includes the following analyses e Check all parent to child one to many relationships to ensure the relationships are working appropriately e Ensure that the same number of records has been added as existed in the original data e Sum imported numeric data in a query and check the totals against the original data e Ensure that new data can be seen by reports and queries as expected e Perform unique queries on all fields to identify outliers e Perform min max queries on all fields to identify outliers and e Perform duplicate queries to check for duplicate entries Any suspicious values should be checked against original documentation Any changes that are made manually as a result of the checking should be double checked by a second auditor Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 18 28 August 2007 APPENDIX A Table and Field Descriptions ptbl MASTER SAMPLE SAMPLEID Long Integer Biological Sampling Event ID SITEID Text SitelD Ikp SITES sorted by Traps TRANSECT HAB Text Transect for Habitat Data only A B B C C D D E E F F G H I I J K Side channel data is denoted with an X AB BX CX DX EX FX GX HX I X J X KX TRANSECT LN Text Transect Length for Habitat Data CHANN SI
40. following instructions outline the basic steps required to run a report once it is created 1 a pre designed report 1 Under Objects on the left hand side of the Database window click Reports The names of the pre designed reports will be displayed in the Database window 2 Double click on the pre designed report of interest to view or print the results 4 2 3 Forms As mentioned in Section 4 1 forms in a database can be used for data entry and are an easy way to review the relational data However forms can also be used to graph chart the data for reporting The current database design consists of four 4 charting forms graphs Each of these forms has the prefix CHART or for sorting purposes and is listed in Table 4 4 The following instructions outline the basic steps required to open a form once it is created 1 a pre designed form 1 Under Objects on the left hand side of the Database window click Forms The names of the pre designed form will be displayed in the Database window 2 Double click on the pre designed form of interest to view the results Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 12 28 August 2007 Table 4 4 Pre designed charting output forms graphs Form Name Description CHART_D_FLOW_AVG MIN MAX CHART_D_FLOW_MOVINGAVG PIVOTCHART_VIDEO BY HOUR PIVOTCHART_VIDEO BY MONTH No
41. gned Output Queries Reports and Forms 8 qd CUCM SS Male 9 4222 12 42203 LOI I EDT 12 4 3 Creating Simple Queries EE ese GERS ES 13 4 4 Creating Pivot Chart Forms Graphs iis niente de de ee ede 14 4 5 Exporting Output Queries Reports and Forms 15 5 0 DATABASE SECURELY 15 6 0 DATABASE LIMITATIONS aon Une MERI Se ee DIE ee 16 7 0 TROUBLESHOOTING 17 8 0 QUALITY ASSURANCE QUALITY CONTROL ees ese esse see se ee ee ee se 17 Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL ii 28 August 2007 LIST OF APPENDICES Appendix A Table and Field Descriptions Appendix B Entries in the Lookup Tables Appendix C Original Requests for Outputs LIST OF TABLES Table 4 1 List and description of the data entry 8 Table 4 2 Pre desiened output QUEFIGS rece eorr eterna er a 11 Table4 3 Prre designed output reports 12 Table 4 4 Pre designed charting output forms charts 13 LIST OF FIGURES Figure 3 1 Summary of the table relationships in the database design
42. grams per liter Appendix B Page 4 p01022 Calcium water filtered milligrams per liter 900915 Carbonate water unfiltered fixed endpoint PH 8 3 titration field milligrams per liter 900445 CARB_TITR_FILT Carbonate water filtered incremental titration field milligrams per liter 00452 CARB_TITR_UNFILT Carbonate water unfiltered incremental titration field milligrams per liter p00447 p99445 CARB TITR UNFILT CACOS Carbonate water unfiltered incremental titration field milligrams per liter as calcium carbonate p99430 Cadmium Tot Rec ug L EPA200 8 Cadmium Dissolved ug L EPA200 8 ug L Cadmium water filtered micrograms per liter p01025 ug L Cadmium suspended sediment recoverable micrograms per liter p01026 ug L Cadmium water unfiltered micrograms per liter p01027 cells mL Total cell count water cells per milliliter p95200 CHLAMYDOMONAS 4 USGS ACL p96014 CHLORELLA 4 USGS ACL 996205 Chloride water filtered milligrams per liter Appendix Page 5 p00940 Chlorophyll a periphyton chromatographic 070957 Chlorophyll b periphyton chromatographic 070958 CHLOROCOCCUM CHLOROCOCCUM 4 USGS ACL 096167 CHROMIUM_FILT Chromium water filtered micrograms per liter 901030 CHROMIUM
43. gust 2007 Show Table window you may bring it back by choosing the Query menu and choosing Show Table 5 The query is completed by filling in the design grid as follows a To add a field to the design grid drag the field from the field list to a column in the design grid or double click the field name in the field list to remove a field from the design grid click the column selector to highlight the column and then press the Delete key b To sort records in the query results click in the Sort cell for the field you want to sort click the arrow and then select a sort order from the drop down list e g ascending c To limit the records that you see in the query s results specify criteria in the Criteria row for one or more fields e g to include only Chinook enter Chinook The asterisk is a wildcard symbol and can be used to search for partial words e g Chi would find Chinook and Chicago and d To perform calculations on the values in a field click Totals on the toolbar Sum icon or select Totals from the View menu to display the Total row in the design grid click the arrow and then select a function by using the drop down list e g sum average etc Note When you add Totals to your query all fields will automatically be grouped by unique entries for each calculation 6 View the results of the query by clicking the Run button exclamation mark icon on the
44. hey are collected as 0 Absent 1 Sparse FCLvTrRt Mean that live trees and roots make up areal extent of fish cover within the site lt 10 2 Moderate 10 40 3 Heavy 40 FCOvrhVeg Mean that overhanging vegetation makes up areal extent of fish cover within the site FCUndrBk Mean that undercut banks make up areal extent of fish cover within the site FCBlders Mean that boulders make up areal extent of fish cover within the site FCArtif Mean that artificial human made structures makes up areal extent of fish cover within the site Bckwtr Number of backwaters alcoves and sidepools per km LIDAR OffChanPI OffChanPd SidChanNb SidChanLgth BkflDpth LIDAR LIDAR LIDAR LIDAR LIDAR Thalweg depth plus Bankful height pers comm KH Bankful width Bankful depth pers comm KK UnstBk Mean of unstable banks within the site Don t Take bank Stability data pers comm CanConifer Percent of site with riparian canopy made up of conifers Do not have Percentage of tree type or D CanDecid Percent of site with riparian canopy made up of deciduous trees He eee ene ERE CanBrdleat Percent of site with riparian canopy made up of broadleaf evergreen trees none CAN T CALCULATE PERCENT SO GET CanMix Percent of site with riparian canopy made up of mixed trees COUNT OF EACH PER SITE CanNone Percent of site with no riparian cano
45. l Data Services Contractor to NOAA Fisheries to ensure that the OBMEP database design is compatible with the ISEMP Integrated Status and Effectiveness Monitoring Program database Data sharing between the two databases will therefore be straightforward 2 4 COLLABORATION WITH CCT STAFF Numerous design iterations were reviewed by CCT staff to ensure the design meets the needs of all staff i e ease of use of the entry forms incorporation of all data fields functionality of the database Summit also worked closely with CCT staff to develop a protocol document under separate cover to provide step by step procedures for translating the database components that are downloaded from Trimble units or from the internet into the pre designed OBMEP Microsoft Access database The development of this document has resulted in numerous modifications of the database 2 5 DEVELOPMENT OF USER S MANUAL AND DATABASE DESIGN Summit has prepared this user s manual and completed design modifications to the original database design Summit will continue to provide ongoing support for future translations and for developing queries to analyse data stored in the fully functional OBMEP database Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 4652 01 07 OBMEP USER S MANUAL 3 28 August 2007 3 0 DATABASE DESIGN The database design was developed in Microsoft Access MS Access 2003 version The database design can be saved in earlier versio
46. lculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods See FishAbundance mdb qry ForHillman SpRichness Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count divided by wetted surface area Count
47. nformation on database security database limitations trouble shooting and quality assurance quality control It is a pleasure working with the Colville Confederated Tribes on this important project Yours truly Summit Environmental Consultants Ltd Rebekka Lindskoog B Sc R P Bio Database Coordinator Biologist TABLE OF CONTENTS OF TRANSMITTA D u ve isc ewe in a i TABLE ii EIS TOE APPENDICES iii EIS FOELABEES es iii LISTVOF FIGURES PL M iii 1 0 INTRODUCTION 2 TE DE ue ds 1 1 1 a n Su w mo Ee 1 1 2 Project Objeetives P 1 2 0 DEVELOPMENT METHODS 2 2 1 Review Of Data bist 2 2 2 Modification of the Database Design uu sis sd sedes EE Be Ge SR nn enano Ge ee 3 2 3 15 3 2 4 Collaboration with CCT Staff 3 259 Development of User s Manual and Database Design 3 3 0 DATABASE DESIGN S Ge Ge ee ee iae MR 4 4 0 DATABASE PROCEDURES es inn N oe eg ee be oe EG bote 7 4 1 Data Entry sa abusata Oe EE 7 4 2 Pre Desi
48. ng Integer REDD SORT Long Integer RLENTRY Text SI ZECLASS SIZE CLASS Text Size class 100mm gt 300mm 100 300mm MID POINT Long Integer Mid point use for calculations SPECIES DBNAME Text Unique species name used in the database COMMONNAME Text Common name SPECIESNAME Text Species Scientific Name FAMILY Text Family DATECREATED Text CREATEDBY Text LASTUPDATED Text UPDATEDBY Text VIDEO SORT Long Integer Ikp SUBSTRATE SUBTRATEID BL FN GC GF HP LCB OT RR RS SA SCB WD SUBSTRATE DESC Description Appendix A Page 17 APPENDIX B Entries in the Lookup Tables Appendix B Entries in the Lookup Tables Ikp CAMERA CAMERA SITENAME OBMEP ZOSEL OBMEP ZOSEL OBMEP ZOSEL OBMEP ZOSEL OBMEP ZOSEL OBMEP ZOSEL OBMEP ZOSEL OBMEP ZOSEL BONAVS NI O Ni OO G N cO Appendix B Page 1 Ikp CHEMDICT CHEMCODE CHEMNAME DESC METHOD ORETCO USGSCODE ACID FIX FIELD Acid neutralizing capacity water unfiltered fixed endpoint PH 4 5 titration field milligrams per liter as calcium carbonate p00410 Acid neutralizing capacity water unfiltered fixed endpoint pH 4 5 titration laboratory milligrams per liter as calcium carbonate p90410
49. ng individual fish data 4 2 PRE DESIGNED OUTPUT QUERIES REPORTS AND FORMS The current database design consists of 70 pre designed output queries reports and forms which were designed based on the examples of outputs for reporting provided by CCT staff The following sections describe each of the pre designed output queries reports and forms Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 8 28 August 2007 4 2 1 Queries Queries are used to view change and analyze data in different ways The power of queries lies in being able to bring together or perform an action on data from more than one table in the database The most common type of query is a select query A select query retrieves data from one or more tables by using specified criteria and displays the data in a desired order e g ascending order based on a data value Other types of queries should only be used by very experienced database users because they can permanently alter or delete information in the database The current database design consists of 66 pre designed queries that were created exclusively for output and are not included in other forms or reports For reference the original request for each of these queries is provided in Appendix e g Hillman request spreadsheet often referred to as the stoplight spreadsheet Each of these queries has the prefix and is numbered for so
50. nician the_colville_n8ive yahoo com Lincoln Feddersen Colville Confederated Tribes Fisheries Technician Jill Bement LGL Ltd Contractor bnass lgl com 509 962 8294 Lynnea Niens Okanagan Nation Alliance Fisheries Technician Michael Rayton Colville Confederated Tribes Anadromous Fisheries Biologist Michael Rayton colvilletribes com 509 422 7434 Mason Squakim Okanagan Nation Alliance Fisheries Technician Michelle Walsh Okanagan Nation Alliance Nicole Peone Colville Confederated Tribes Fisheries Technician ONA general Okanaga Nation Alliance General Staff Oly Zacherle Colville Confederated Tribes Summer Youth Rhonda Dasher Colville Confederated Tribes Anadromous Fisheries Biologist Rhonda Dasher colvilletribes com 509 422 7439 Rich Tonasket Colville Confederated Tribes Fisheries Technician 509 633 3556 Smith Condon Colville Confederated Tribes Fisheries Technician Sidryn Sam Colville Confederated Tribes Fisheries Technician sincerethreeirons hotmail com Tim Erb Jr Colville Confederated Tribes Fisheries Technician Tim Erb colvilletribes com 509 422 7432 Tatum Gunn Colville Confederated Tribes Fisheries Technician 509 422 2294 Tony Moore Colville Confederated Tribes Summer Youth Von Peterson Colville Confederated Tribes Summer Y
51. ns however some of the features may not function properly in the earlier versions e g switchboards PivotChart View MS Access databases are particularly flexible as the component database tables can be easily exported to other database applications such as Microsoft Excel xls file and Borland Paradox dbf file In addition MS Access is widely used and is often the software of choice for beginner and intermediate database users Many of the database software systems that are designed to handle enormous quantities of data e g Oracle use MS Access as the user interface software Furthermore in using MS Access all information can be managed from a single database file 1 e mdb file The database was designed primarily as a data archiving system to store similar data types and perform simple data analyses for reporting purposes In order to design a database that most directly met the needs of CCT output examples were reviewed and tested with the current design using representative synthetic data The database is a relational database i e the database consists of several tables that are linked together to facilitate retrieval of the data in a wide variety of ways The purpose of defining relationships is to coordinate the retrieval of information in the different tables The main advantage of a relational database is that queries reports and forms can be created to display information from several tables at once Figure 3 1
52. out importing into Excel Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 4652 01 07 OBMEP USER S MANUAL 10 28 August 2007 Query Name Description RLqry_01_SITEID_YEAR amp BIOEVENT RLary 02 REACHLN RLary 03 WETTED WIDTH RLary 04 WETTED SURFACE AREA by SITEID RLary 04 WETTED SURFACE AREA by transect 05 SUBSTRATE 06 DOM SUBSTRATE RLqry 07 AVG EMBED RLqry_07_EMBEDDEDNESS RLqry_08_LWDperREACH RLqry_09_ POOLPPperREACH RLqry 10 POOLBPperREACH RLqry_11_POOLTOTperREACH RLary 12 POOLAVGDEPTH RLary 13 FISHCOVER PRESENT RLary 14 SIDECHAN BACKWAT COUNT RLary 15 BANKFULL DEPTH RLary 15 BANKFULL DEPTH AVG SD and COUNT 15 BANKFULL DEPTH AVG SD and COUNT by year RLary 15 BANKFULL DEPTH by year RLary 15 BANKFULL DEPTH CI RLary 15 BANKFULL DEPTH CI by year RLary 16 BANKFULLWIDTH AND THALWEG DEPTH RLary 17 BANKFULLWIDTH DEPTH RATIO 18 CANOPYVEG RLary 19 COUNT RLary 20 HUDIVERT COUNT RLary 20 HULOG COUNT RLqry_21_HUPSTR_COUNT 22 HUCROP COUNT 23 HUWALL COUNT RLary 24 HUBLDG COUNT 25 HUPIPE COUNT RLqry_26 HUCLEAR COUNT 27 HUTRASH COUNT RLary 28 HUPVMT COUNT 29 HURIVACC COUNT 30 DENSIOMETER RLqry 31 SpCounts FISH PASSAGE
53. outh Zacherle Ol Colville Confederated Tribes Summer Youth Appendix B Page 19 Ikp_EXTERNAL MARK MARK DESC Adipose Clip Caudal Clip Elastomer Floy Tag Jaw Tag Left Ventral Clip Other Pelvic Clip Right Ventral Clip Radio Transmitter Unmarked Unknown Appendix B Page 20 Ikp_HARDDRIVE HARDDRIVE HD Type Western Digital WD Caviar SE 250 0 GB Western Digital WD Caviar SE 250 0 GB Western Digital WD Caviar SE 250 0 GB Western Digital WD Caviar SE 250 0 GB O O OO KON cO Appendix Page 21 Ikp_LIFESTAGE FISHTYPE DESC ALT_DESC STAGECODE Adults Egg Sac YOY JUVENILE Juvenile Year 1 Sockeye Year 1 Osoyoos PARR Juvenile Year 2 Sockeye Year 2 Skaha SMOLT Smolts Appendix B Page 22 QUAL FLOW QUALID DESC The data were measured on site and used in the development of the stage discharge curve The reading is below the existing curve and therefore outside of the knowledge reference used to develop the stage discharge curve i e data are of questionable quality Data are estimated It is my assumption that this means no measurement was taken because technically even when you get measure stage height you end up estimating discharge Appendix
54. p96706 CYMBELLA 4 USGS ACL p96806 DIATOMA 4 USGS ACL 96759 DICTYOSPHAERIUM DICTYOSPHAERIUM 4 USGS ACL 996209 DO_MGL Dissolved oxygen water unfiltered milligrams per liter 900300 DO_PERCENT Dissolved oxygen water unfiltered percent of saturation 00301 ELAKATOTHRIX ELAKATOTHRIX 4 USGS ACL p96086 ENT Enterococci Bacteria 100ml Method also reported as SM17 9230C EPA1600 FECAL_COL Fecal Coliforms 100ml Fecal coliform M SM16 909C P31616 p31616 p31625 FLOW Flow CFS Ecology rating or Estimated by outside Appendix B Page 7 RATINGF or EST_GageF P60 FLUORIDE Fluoride water filtered milligrams per liter 900950 FRAGILARIA FRAGILARIA 4 USGS ACL 096764 GLENODINIUM GLENODINIUM 4 USGS ACL p96484 GOMPHONEMA GOMPHONEMA 4 USGS ACL 996802 HANTZSCHIA 4 USGS ACL p96811 Hardness Hardness water milligrams per liter as calcium carbonate SM2340B p00900 HARD NONCARB Noncarbonate hardness water unfiltered field milligrams per liter as calcium carbonate p00902 HARD NONCARB 5 Noncarbonate hardness water milligrams per liter as calcium carbonate p95902 Hg Mercury Total All Hg data should have been reported here EPA245 7 P71900 IRON FILT Iron water filter
55. py RDMine Site average of numerical value for presence of mining activity weighted by proximity to channel We have this info how is it calculated RDLog Site average of numerical value for presence of logging activity weighted by proximity to channel weighted COUNT Fee and Bs PER SITE RDPasture Site average of numerical value for presence of pasture range hay fields activity weighted by proximity to channel RDCrop Site average of numerical value for presence of row crops weighted by proximity to channel RDDike Site average of numerical value for presence of walls dikes revetments weighted by proximity to channel RDBldg Site average of numerical value for presence of buildings weighted by proximity to channel RDPipe Site average of numerical value for presence of inlet outlet pipes weighted by proximity to channel RDLndfill Site average of numerical value for presence of landfull trash weighted by proximity to channel RDPark Site average of numerical value for presence of parks lawns weighted by proximity to channel RDLot Site average of numerical value for presence of pavement cleared lots weighted by proximity to channel RDUnpvdRd Site average of numerical value for presence of unpaved roads trails railroads weighted by proximity to channel RDPvdRd Site average of numerical value for presence of paved roads trails railroads weighted by proximity to channel RDAIIRd Site average of numerical value for presence of all roads trails rail
56. red recoverable micrograms per liter Appendix B Page 9 p01055 Molybdenum water filtered micrograms per liter 001060 Sodium water filtered milligrams per liter p00930 Sodium adsorption ratio water number p00931 Sodium fraction of cations water percent in equivalents of major cations p00932 Sodium plus potassium water filtered milligrams per liter as sodium p00933 NAVICULA 4 USGS ACL 096791 Ammonia water filtered milligrams per liter as nitrogen 900608 NH3 N Collected using unspecified techniques and preservation EPA350 1 Nickel Dissolved EPA200 8 Nickel Tot Rec EPA200 8 Nickel water filtered micrograms per liter 001065 Nickel suspended sediment recoverable micrograms per liter 001066 Nickel water unfiltered recoverable micrograms per liter 001067 Total nitrogen water filtered milligrams per liter p00602 Total nitrogen water unfiltered milligrams per liter Appendix B Page 10 p00600 NIT_UNFILT_AS_N Total nitrogen water unfiltered milligrams per liter as nitrate 071887 NITZSCHIA 4 USGS ACL 996812 Nitrite Nitrogen Dissolved Collected using unspecified techniques and preservation or Collected in acid washed passenger acid preserved shipped on ice EPA353 2 Nitrite water filtered
57. roads weighted by proximity to channel CanCov SpRich Number of different species of fish observed within the site during daytime catostomA Density ha of all suckers in the site during daytime cottidA Density ha of all sculpins in the site during daytime cyprinA Density ha of all minnows Cyprinids in the site during daytime gasterA Density ha of all sticklebacks in the site during daytime prosopA Density ha of all whitefish in the site during daytime cuttA Density ha of all cutthroat in the site during daytime RbStA Density ha of all rainbow steelhead in the site during daytime RbCuttA Density ha of all rainbow cutthroat hybrids in the site during daytime sockA Density ha of all sockeye salmon in the site during daytime cohoA Density ha of all coho salmon in the site during daytime chinA Density ha of all chinook salmon in the site during daytime TotOncorA Density ha of all species of Oncorhynchus in the site during daytime bullA Density ha of all bull trout in the site during daytime brookA Density ha of all brook trout in the site during daytime TotCharA Density ha of all charr species in the site during daytime TotSalA Density ha of all salmon trout species in the site during daytime TotFishA Density ha of all fish in the site during daytime catostomB Biomass gm ha of all suckers in the site during daytime cottidB Bioma
58. rting purposes as per how each was requested and is listed in Table 4 2 However in the database design many other queries were created to perform analyses exclusively designed for pre designed output forms and reports prefix These queries have not been designed for running independently and should not be run by themselves The following instructions outline the basic steps required to run a query once it is created 1 a pre designed query 1 Under Objects on the left hand side of the Database window click Queries The names of the pre designed queries will be displayed in the Database window and will have the prefix RLary 2 Double click on the pre designed query of interest to view the results 3 Once you have viewed the results there are a few ways you can further analyse the data as follows e To print the data Under File select Print Preview You may want to adjust the margins or the page orientation before sending to the printer To adjust these Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 9 28 August 2007 characteristics click File Page Setup When you are ready to print click File Print To copy and paste into Microsoft Excel click Edit Select All Records then Edit Copy and Edit Paste into a new Microsoft Excel worksheet Note that you can sort the data in the window that you view the results in Microsoft Access with
59. ss gm ha of all sculpins in the site during daytime cyprinB Biomass gm ha of all minnows Cyprinids in the site during daytime gasterB Biomass gm ha of all sticklebacks in the site during daytime prosopB Biomass gm ha of all whitefish in the site during daytime cuttB Biomass gm ha of all cutthroat in the site during daytime RbStB Biomass gm ha of all rainbow steelhead in the site during daytime RbCuttB Biomass gm ha of all rainbow cutthroat hybrids in the site during daytime sockB Biomass gm ha of all sockeye salmon in the site during daytime Appendix C1 Page 1 Do we calculate an AVERAGE wetted surface area for the SITEID because we only can calculate using the HABITAT data How do you define daytime for example 6 AM to 6PM DO THIS WITH SNORKEL DATA THIS DATA IS ALWAYS COLLECTED IN DAYTIME ALSO DO NOT CALCULATE THIS WITH TRAP DATA B C THERE ARE TOO MANY SPECIAL CONSIDERATIONS Email from Tracy dated Jan 23 not calculating biomass Appendix C2 Original Requests for Outputs Email from KK dated July 10 2007 How can we query water quality flow and temperature data for the water year and not by separate years The water year goes from October 1st of one year to September 30th of the following year example October 1 2005 to September 30 2006 RLary A WATERYEAR FLOW RLary A WATERYEAR FLOW Check RLary A WATERYEAR FLOW Crosstab RLary B WATERYEAR TEM P RLary B WATERYEAR TEM P ch
60. summarizes the table relationships The key variables that connect the tables within the database are Sample Number SAMPLEID and Site ID SITEID The database has been assembled using eight 8 separate groups of tables and the design is based on relationships linking each of the groups of tables To simplify the table organization the tables that are relationally connected to the MASTER SAMPLE table are labelled permanent table and the tables that are stand alone but can be connected by DATE or SITEID to the other tables but are stand alone are labelled alternate table Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 4652 01 07 OBMEP USER S MANUAL 4 28 August 2007 The reason these tables e g FLOW TEMP are stand alone is because they consist of one data type e g SITEID DATE TIME TEMP instead of multiple for example the WQCHEMISTRY has many records for SITEID DATE TIME To create a one to many relationship with these tables to the MASTER SAMPLE would make the database too cumbersome The eight groups of tables are as follows Fish Passage data and 1 Individual Fish data collected via VIDEO or TRAP 2 Trap data 3 Video data 4 Water Quality Chemistry 5 Snorkel data 6 Redd data 7 8 Habitat data These groups are presented in different colours in Figure 3 1 The database design consists of 17 permanent data tables prefix ptbl
61. t use and should not be opened Examples of each data entry form are included on the CD that accompanies the protocol document The data entry forms include all tables in the database and incorporate all relationships Data should be entered into the database using these forms to ensure referential integrity Data can also be entered into the tables directly without the use of the forms or by using the excel templates see the protocol document under separate cover However it is very easy for the database user to make mistakes when entering data this way therefore only very experienced database users should enter the data in this manner Mistakes introduced into the database by these manual data entry methods could prevent queries and or reports from functioning properly After incorporation of the data into the database a thorough quality assurance quality control QA QC analysis should be performed to ensure referential integrity and data quality were maintained Table 4 1 List and description of the data entry forms Data Entry Form Name Description ENTRYFORM REDD DATA Adds new redd data ENTRYFORM SNORKEL DATA Adds new snorkel data ENTRYFORM TRAP DATA Adds new trap data salmonids non salmonids and mark and recapture includes a separate form for entering individual fish data ENTRYFORM VIDEO DATA Adds new video data includes maintenance and status information and a separate form for enteri
62. te that these charting forms are interactive and the database user can switch from the entry form style to a charting style by clicking View and selecting a different view from the drop down list e g PivotChart View Form View The chart type can also be modified In addition the user can modify the way the data is grouped in the charts using the following instructions 1 Right click the desired field in the chart and choose Properties 2 Under the Filter and Group tab choose from the Group Items By drop down list various grouping options e g sampling date by month or by year Using this same method intervals can also be selected to analyse a specific sub sample of the data e g only 2004 data 4 3 CREATING SIMPLE QUERIES The following instructions outline the basic steps required to create a select query in query Design View 1 Under Objects on the left hand side of the Database window click Queries 2 Click New top of the Database window 3 In New Query window select Design View then OK 4 Select the data you want to work with by adding the tables or queries that contain the data of interest in the Show Table window select tables and or queries then Add Once you have added the tables you are interested in querying press the Close button in the Show Table window Note If you accidentally close the Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 13 28 Au
63. ter facing right LB Left Bank RB Right Bank DENSI OMETER Integer Number of cross hairs upon which shade falls 0 17 COMMENTS Text Comments including descriptors for qualifiers OLDSAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE ptbl HAB CHANNEL SAMPLEID Long Integer SamplelD ptbl MASTER SAMPLE STA NUM Double Thalweg depth position between transects There are 10 Stations 0 through 9 Station 0 occurs at the associated transect Station 9 occurs 1 increment lower than the next transect THAL_DEPTH Double Wetted depth of the estimated thalweg cm GRADIENT Double Gradient Slope_Hor HAB_TYPE Text Habitat type GL Glide PT Pool Tailout BP Beaver Pond DRY Dry PP Primary Pool LCR Large Cobble Boulder Riffle SCR Small Cobble Gravel Riffle RA Rapid CF Cascade Falls WET_WIDTH Double Wetted width meters 99 9 means not applicable BF_WIDTH Double Bankfull width meters 99 9 means not applicable BF_HEIGHT Double Bankfull height meters 99 9 means not applicable FINES Text Fines Yes No SIDE CHAN Text Side channels Yes No BACKWATER Text Backwaters Yes No CA FA HT Double Cascade Falls Height CA FA LN Double Cascade Falls Length CA FA GRAD Double Cascade Falls Gradient BAR WIDTH Double Width of bar meters
64. tion Field observation Field observation Field observation Field observation WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods Field observation WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calc
65. toolbar or select Datasheet View from the View menu For more information on queries refer to the MS Office Access Help under the Help menu 4 4 CREATING PIVOT CHART FORMS GRAPHS Pivot Chart forms are the most interactive way a database user can analyse data The user can manipulate the way the data is grouped and can modify the way the data is presented without having to understand SQL Structured Query Language statements or modify the Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 14 28 August 2007 design of the form The following instructions outline the basic steps required to create Pivot Chart forms 1 Under Objects the left hand side of the Database window click Forms 2 Click New top of the Database window 3 In the New Form window select AutoForm PivotChart then choose the table or query that contains the data of interest from the drop down list and click OK 4 Highlight the fields you are interested in for each axis and drag them to the appropriate spot on the chart In addition the user can modify the way the data is grouped by following the instructions provided in Section 4 2 4 5 EXPORTING OUTPUT QUERIES REPORTS AND FORMS As described above Pivot Chart forms are the most interactive way a database user can analyse data However Pivot Chart forms cannot be electronically exported into other software applications The only way these charts can be exporte
66. ulated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE calculated metric following EMAP analytical methods WSDOE ca
67. with the database design and field definitions to enable them to design effective queries It is therefore necessary for database users to thoroughly Summit Environmental Consultants Ltd DRAFT REPORT V1 Project 652 01 07 OBMEP USER S MANUAL 16 28 August 2007 review the supporting documentation prior to designing queries modifying existing data and or adding data to the database Additional limitations with MS Access databases are e Text field only allow a maximum of 255 characters If more characters are required the field type can be modified from text to memo field type e Times will be stored according to 24 hour clock i e short time field type e Dates will be stored in medium date format e g 25 July 05 e The limnological results will be stored as text and again as numbers This ensures that significant figures and qualifiers are retained in the text field while the number field allows numerical calculations to be performed 7 0 TROUBLESHOOTING While the database is in operation e g entering data running and creating queries reports and forms temporary hidden objects are automatically created These temporary objects are not visible to the database user however the existence of these tables can expand the size of the database file causing the database to run at slower than optimal performance To remove these temporary tables the user should use the Compact and Repair utility found under

database user`s manual - The Confederated Tribes Of The Colville

Contents

Download Pdf Manuals

Related Search

Related Contents