United States Department of Agriculture Forest Service Northeastern Area State and Private Forestry Newtown Square, PA NA–FR–02–07 November 2007 

# Best Management Practices (BMP) Manual— Desk Reference: Implementation and Effectiveness for Protection of Water ResourcesCover photo credits: Upper left: New York City Watershed Agricultural Council Upper right: Vermont Division of Forests Remaining photos: U.S. Department of Agriculture, Forest Service

# Best Management Practices (BMP) Manual—Desk Reference: Implementation and Effectiveness for Protection of Water Resources

Kristina Ferrare, 1 

# David Welsch, 2

# William Frament, 2 

# Thomas Luther, 2

# and Paul Barten 1

P ublished by : United States Department of Agriculture Forest Service Northeastern Area State and Private Forestry 11 Campus Boulevard, Suite 200 Newtown Square, PA 19073 NA–FR–02–07 November 2007 

### www.na.fs.fed.us

1 University of Massachusetts–Amherst, Watershed and Exchange Technology Partnership
2 U.S. Department of Agriculture, Forest Service, Northeastern Area State and Private Forestry

# Origin of the BMP Protocol Project The BMP protocol project is a cooperative effort of the Forest Service, U.S. Department of Agriculture, and the Northeastern Area Association of State Foresters– Water Resources Committee (NAASF–WRC), Steven Koehn, Maryland State Forester, chairperson. The project has been funded by grants from the U.S. Forest Service and the U.S. Environmental Protection Agency (EPA). The original concept and question sequence was developed by Roger Ryder and Tim Post of the Maine Forest Service in collaboration with David Welsch and Albert Todd of the U.S. Forest Service, Northeastern Area State and Private Forestry (NA S&PF). The NA S&PF proposed the method to the NAASF–WRC and the EPA for development as a potential regional protocol. After the withdrawal of the Maine Forest Service, David Welsch served as the project coordinator through the development, testing, and implementation of the project. State forestry agencies from Delaware, Indiana, Maine, Maryland, Massachusetts, New Hampshire, New York, Ohio, Pennsylvania, Vermont, Virginia, West Virginia, and Wisconsin; the New York City Watershed Agricultural Council Forestry Program; and the U.S. Forest Service Northern Research Station and NA S&PF have collaborated in the development and testing of the BMP protocol.Table of Contents 

# Table of Contents Chapter 1—Introduction

.......................................................................................................................................... 1 Background

.................................................................................................................................................. 1 Purpose of the BMP Protocol

...................................................................................................................... 1 Description of the BMP Protocol

................................................................................................................ 2 Organization of the BMP Manual

............................................................................................................... 2 Chapter 2—Best Management Practices Management Information System

........................................................... 3 Requirements for the BMP MIS

.................................................................................................................. 3 Structure of the BMP MIS

.......................................................................................................................... 3 Access Database

............................................................................................................................... 4 Excel Worksheet

............................................................................................................................... 5 Word Document

............................................................................................................................... 6 Chapter 3—Installing and Using the BMP MIS

..................................................................................................... 7 Copying the BMP Files to Your Computer

.................................................................................................. 7 Step 3.1. Copy the BMP Folder to Your C Drive

............................................................................ 7 Step 3.2. Make a Copy of the Sample BMP MIS Folder

................................................................. 8 Step 3.3. View the Files in the Sample BMP MIS Folder

............................................................... 8 Importing Field Data Into Access

................................................................................................................ 9 Step 3.4. Open the Access Database File Using the Password

......................................................... 9 Step 3.5. Import Field Data Into an Access Table

.......................................................................... 10 Step 3.6. Resolve Import Errors

..................................................................................................... 18 Step 3.7. Export Access Queries to the Excel Analysis File

........................................................... 21 Analyzing Field Data in Excel

.................................................................................................................... 28 Step 3.8. Open the Excel Analysis File

........................................................................................... 28 Step 3.9. Review the Contents of the Excel Analysis File

.............................................................. 29 Step 3.10. Close the Excel Analysis File

......................................................................................... 29 Generating Standard Data Summaries in Word

........................................................................................ 30 Step 3.11. Open the SDS File and Update the Links

..................................................................... 30 Step 3.12. Repeat the Process With the Remaining SDS Files

...................................................... 31 Processing Actual Field Data

...................................................................................................................... 33 Chapter 4—SDSs and Comprehensive Standard Data Summaries

....................................................................... 35 Structure of the Standard Data Summaries

................................................................................................ 35 Calculation of Proportions and the Presentation of Null Values

................................................................ 37 Creating Comprehensive Standard Data Summaries

................................................................................. 38 Step 4.1. Create the Comprehensive Standard Data Summary Document

.................................... 38 Step 4.2. Insert a File Into the New Document

............................................................................. 39 Step 4.3. Insert the Remaining SDS Files Into the Document

...................................................... 40BMP Manual—Desk Reference Step 4.4. Save the Comprehensive Standard Data Summary File

.................................................. 40 Breaking the Links

..................................................................................................................................... 40 Step 4.5. Select the Links

............................................................................................................... 41 Step 4.6. Break the Links

............................................................................................................... 42 Adding Text and Images

............................................................................................................................. 43 Step 4.7. Add Text

.......................................................................................................................... 44 Step 4.8. Add Images

..................................................................................................................... 44 Step 4.9. Remove Unused Placeholders

.......................................................................................... 45 Finalizing the Comprehensive Standard Data Summary

........................................................................... 45 Tips for Troubleshooting

............................................................................................................................ 46 Chapter 5—Custom Queries and Data Summaries

............................................................................................... 49 Creating a Query in Access

........................................................................................................................ 49 Step 5.1. Gather the Documentation Needed

................................................................................ 49 Step 5.2. Navigate to the Query Screen

.......................................................................................... 50 Step 5.3. Verify That Access Is Set to Return All Fields in a Query

............................................... 51 Step 5.4. Create a New Query

........................................................................................................ 53 Step 5.5. Define the Contents of the New Query in Properties

..................................................... 62 Exporting the Query to Excel

.................................................................................................................... 64 Step 5.6. Select the Query to Be Exported

..................................................................................... 64 Step 5.7. Select the File Where the Query Will Be Exported

........................................................ 66 Step 5.8. Export the Query

............................................................................................................ 67 Step 5.9. Verify That the New Query Is in the Excel Analysis File

................................................ 67 Analyzing Queried Data in the Excel Analysis File

................................................................................... 68 Step 5.10. Set Up the Worksheet

................................................................................................... 69 Step 5.11. Perform the Calculations

............................................................................................... 70 Step 5.12. Create a Chart

............................................................................................................... 82 Creating a Custom SDS in Word

............................................................................................................... 98 Step 5.13. Open and Name the New Word Document

.................................................................. 98 Step 5.14. Write the Report

........................................................................................................... 99 Step 5.15. Paste a Data Link Into the Word Document

.............................................................. 100 Step 5.16. Paste a Chart Link Into the Word Document

............................................................. 103 Step 5.17. Update the Links

......................................................................................................... 106 Removing Worksheet Protections From the Excel Analysis File

.............................................................. 111 Step 5.18. Remove the Worksheet Protections

............................................................................. 111 Step 5.19. Replace the Worksheet Protections

............................................................................. 113 Chapter 6—Performing Spatial Queries

.............................................................................................................. 115 Step 6.1. Create a .dbf File From the .xls File

............................................................................... 116 Step 6.2. Initiate the BMP Feature Generator Tool

..................................................................... 124 Step 6.3. View the Result and the Types of Information Available

.............................................. 129 Step 6.4. Select Sample Data From Specific Geographic Areas

................................................... 131Table of Contents Step 6.5. Copy the Selected Data to a .dbf File

............................................................................ 136 Step 6.6. Convert the .dbf File to an .xls File

............................................................................... 138 Chapter 7—Sampling Design

.............................................................................................................................. 139 Timber Sales and Sample Units

............................................................................................................... 139 Sample Unit Identification

....................................................................................................................... 139 Sample Design Using Probability Sampling

............................................................................................. 140 Random Sampling Without Replacement

................................................................................... 140 Random Sampling With Replacement and Varying Probability

.................................................. 141 Random Sampling With Stratification

........................................................................................ 145 Sample Size

.............................................................................................................................................. 146 Examples From Protocol Testing

.............................................................................................................. 146 Importance of Monitoring Over Time

..................................................................................................... 148 Recommendations

............................................................................................ ........................................ 148 Chapter 8—Quality Control

............................................................................................ .................................... 149 BMP Protocol Quality Control Principles

............................................................................................... 149 Quality Control Results During Pilot Testing

......................................................................................... 150 Quality Control Analysis

.......................................................................................................................... 150 Quality Control Analysis Worksheet

....................................................................................................... 151 Chapter 9—Recordkeeping and Data Security

.................................................................................................... 153 Recordkeeping

.......................................................................................................................................... 153 Data Security

............................................................................................................................................ 153 Appendix A—Abbreviations

............................................................................................................................... A-1 Appendix B—Queries

............................................................................................ .............................................. B-1 Appendix C—Standard Data Summaries

........................................................................................................... C-1 Overview Information

............................................................................................................................. C-1 Data Summaries

............................................................................................ .......................................... C-1 Appendix D—Example Comprehensive Standard Data Summary

.................................................................... D-1 Appendix E—Question Map

............................................................................................................................... E-1 Appendix F—Access Query Expressions

............................................................................................................. F-1 Criteria Expressions

........................................................................................... ...................................... F-1 Operators for Building Queries

................................................................................................................ F-1 Specifying Multiple Criteria for a Single Field

........................................................................................ F-2 Appendix G—Excel Functions and Operators

.................................................................................................... G-1Chapter 1—Introduction 

# Chapter 1—Introduction Section 319 of the Federal Water Pollution Control Act of 1972 (33 U.S.C. sec. 1251–1387), known as the Clean Water Act, defines the term best management practice (BMP)as a practice or measure that has been demonstrated to be successful in protecting the water resources of the United States from nonpoint source pollution. The BMP Protocol Project is an effort to develop a standard method for monitoring the use and effectiveness of best management practices commonly used in timber harvesting. 

## Background The U.S. Environmental Protection Agency (EPA) was tasked with executing the Clean Water Act (CWA). Sections 208, 319, and 404 of the CWA require States to identify and quantify nonpoint sources of pollution as well as to develop and implement programs to address and lessen the amount of pollutants entering U.S. waters. Forestry operations such as timber harvesting—or silvicultural activities as they are described in the CWA—are among the many activities regulated under sections 208, 319, and 404 of the CWA. Permits are required in order to conduct activities that may impact the waters of the United States. Although silvicultural activities are responsible for relatively small contributions to surface water pollution compared with other land uses, nonpoint source pollution resulting from silvicultural activities can result in the deterioration of water quality. The CWA provides an exemption from the permitting requirement if appropriate BMPs are used and are effective in preventing adverse effects on the quality of water resources. The silvicultural exemption, as the clause is known, thus relieves silvicultural activities from the permitting requirement of the CWA if BMPs are used effectively. Monitoring is needed, however, to document the use and effectiveness of BMPs in forestry. Over the years, litigation has attacked the silvicultural exemption, and the need to comply with Total Maximum Daily Loads has further reinforced the need for the effective use of BMPs. As a result, the EPA has been seeking BMP use and effectiveness data that are reliable and comparable among States as evidence of compliance with the provisions of the CWA. Research studies have repeatedly shown that BMPs can be effective if applied correctly and in a timely manner. 1 To date, however, monitoring of effective BMP usage has often been sporadic, inconsistent, and largely anecdotal. 

## Purpose of the BMP Protocol The specific purpose of the BMP protocol is to create an economical, standardized, and repeatable BMP monitoring process that is completely automated, from data gathering through report generation, in order to provide measured data, ease of use, and compatibility with State BMP programs. The protocol was developed to meet the following needs:

• Document the use and effectiveness of BMPs in

protecting water resources during forest harvesting operations.

• Document the degree of compliance with the Clean

Water Act as well as the Coastal Zone Management Act and various State laws and regulations.

• Assess water resource protection based on the

effectiveness of a collective set of BMPs rather than documenting the use of individual prescriptive methods or practices.

• Increase credibility through the measurement of

results as opposed to observation and anecdotal assessments.

• Respond to public concerns regarding the potential

effects of timber harvesting based on measured evidence.

• Identify opportunities for improvement in water

resource protection by identifying causes of BMP failure.

1 Kochenderfer, James N.; Hornbeck, James W. 1999.

Contrasting timber harvesting operations illustrate the value of BMPs. In: Stringer, Jeffrey W.; Loftis, David L., eds. Proceedings, 12 thcentral hardwood forest conference; 1999 February 28–March 2; Lexington, KY. Gen. Tech. Rep. SRS–24. Asheville, NC: U.S. Department of Agriculture, Forest Service, Southern Research Station: 128–136. 1BMP Manual—Desk Reference

• Facilitate focused agency and industry BMP

training programs by identifying problems with the implementation of individual practices.

• Identify opportunities to improve or simplify BMP

practice specifications.

• Facilitate self assessment of water resource

protection efforts by States, industry, and other forestry organizations, and encourage more frequent monitoring.

• Facilitate green certification. 

## Description of the BMP Protocol The BMP protocol is an automated monitoring system with three functions: (1) data collection, (2) data analysis, and (3) report generation. Data are collected using a branched question set designed to efficiently address those areas of the timber harvest with the greatest potential to impact water resources: water crossings; haul roads, log landings, and skid trails; chemical pollutants; buffer/filter strips; and wetlands. The protocol question software was developed for use with commonly available software and hardware platforms. A variety of inexpensive pocket PC units or more costly GPS units can be used for field data collection. These field units use Windows Mobile software to link and upload data to a desktop computer. Microsoft Access, Excel, and Word—inexpensive software programs commonly available in forestry offices—are used to analyze the data and produce an automated data summary. GPS data recorded in the process permit analysis of any conceivable geographical unit. Users can create customized reports suitable for different audiences by adding commentary and photos to the data summary to further explain results. Features of the protocol include the following:

• State- or user-controlled statistical sample design

• Single field assessor–single visit sampling procedure

• Branched question set and data recorder program

• Computer-generated standard data summary

• Discrete georeferenced sample units

• Quality control resampling • Analysis of potential site- and operator-associated risks by geographical area or individual timber sale

• State or user interpretation and report design

• Ability to design custom data queries 

## Organization of the BMP Manual The documentation to accompany the BMP protocol software is contained in two volumes—a field guide and this desk reference. The two-volume set is informally referred to as the BMP manual. This desk reference focuses on analyzing and summarizing the data that have been collected using the BMP Management Information System (BMP MIS). Chapter 3 covers installation of the BMP MIS software, running of the preprogrammed queries of the data, and generation of the standard data summaries. Chapter 4 addresses assembling the standard data summaries into a single Comprehensive Standard Data Summary. Chapter 5 contains instructions for creating custom queries and data summaries, which user’s may want in order to address unique harvesting situations or activities. Chapter 6 covers the use of the spatial query tool to create subsets of the field data, based on GPS coordinates, to assess BMP effectiveness for specific geographical areas. The remaining chapters address such topics as statistical sample design, quality control, and recordkeeping. The companion field guide contains the full text of the protocol questions and covers such topics as installing the field data collection software, training field personnel, and collecting data in the field.

2Chapter 2—BMP Management Information System 

# Chapter 2—Best Management Practices Management Information System Data collection using the BMP protocol is only the first step in determining BMP performance. The Best Management Practices Management Information System (BMP MIS) is a tool with which users can efficiently analyze and interpret a wide range of data from the BMP protocol and report on it to resource managers and the public. It consists of a set of electronic files that are used to organize, sort, and present the information in a set of standard data summaries. The standard data summaries, which report on individual focus areas, can then be combined into a Comprehensive Standard Data Summary of BMP performance. The BMP MIS also allows users to design custom queries to supplement the information in the standard data summaries. The BMP MIS consists of the following components:
• This desk reference, which is a guide to using the

BMP MIS to sort and query field data and present them in standard and custom data summaries.

• A CD containing the electronic files needed for

processing BMP field data, as well as a set of sample files for use in practice exercises. 

## Requirements for the BMP MIS The BMP MIS is designed to operate on hardware and software typically available in most government and industrial forestry offices in order to maximize efficiency and minimize additional cost. The following components are needed:
• Desktop (or laptop) computer with the following

features:
♦ USB port to connect to a pocket PC
♦ 512 MB RAM
♦ 1500 MHz processor
• Windows 2000 or Windows XP operating system
• Microsoft Office Professional 2003 software

applications, including Access, Excel, and Word You can confirm the specifications on your computerby opening My Computer on your desktop and rightclicking anywhere in that window. Select Properties from the menu. The System Propertiesbox will open. The Generaltab lists the system specifications of the computer.

Important : The BMP MIS was designed to work with the configuration specified. The BMP MIS will operate with different hardware-software configurations, but the screens may vary slightly from those shown in this manual. If using the 2002 version of Microsoft Word, download and install the Office XP Service Pack 3 from the Microsoft Web site.


## Structure of the BMP MIS The BMP MIS utilizes Microsoft Excel, Access, and Word to produce the standard data summaries that will form the basis of your reports. Each software application performs a specific function in the process (figure 2.1). 

## BMP MIS Data Flow
D o wnload field da ta: E xc el

S or t and filt er da ta: A cc ess
Q uer y da ta: E xc el
Produce report: Word Figure 2.1. Different Microsoft programs are utilized in each step of the process, from downloading field data to producing a final report. 3BMP Manual—Desk Reference You received two different BMP MIS folders on the CD included with this BMP desk reference: Sample BMP MIS and BMP MIS . The Sample BMP MIS folder is provided for demonstration and training. The BMP MIS folder contains the files you will populate with your field data in order to generate reports. In each folder you will find the following:
• An Access database
• An Excel worksheet formatted for field data analysis
• Fifteen Word files containing standard data summary

templates In addition, the Sample BMP MIS folder contains an Excel file with sample field data. A description of how each Microsoft application is used to manage and query field data to create the standard data summaries is provided below. 

## Access Database Data downloaded from the field data recorder are stored in an Excel worksheet. When sampling is complete, the worksheet will be imported into Access, where it becomes the primary database for BMP protocol data. All data collected for your State or organization are stored in an Access table. From this table, the field data are sorted and filtered numerous ways. The various sorts of the data in Access are referred to as queries. The goal of sorting data is to look at many different subsets of data that focus on specific areas in the sample unit, such as approaches to a surface water crossing or crossing structures, or specific conditions , such as soil movement or chemical pollutants. A number of queries have been defined in Access, as shown in the screen below. Each of the files shown is a subset of field data. The queries are named using abbreviations for the data they contain. A list of abbreviations used to name the queries can be found in appendix A; a list of the query names and descriptions is in appendix B.

4

## Excel Worksheet After data are sorted in Access as queries, each query is exported to a worksheet in an Excel file. This file contains worksheets that (1) contain copies of the Access data queries, (2) further query and analyze Access data, or (3) contain charts and graphs. The Excel file organizes the data sorted in Access into one file, so that more specific queries can be applied to the field data. The goal in Excel is to use the Access queries to answer the questions that the protocol seeks to address, such as the following:
• Are there problems with soil movement on harvest

units? • What are the problems? Where and why are they

happening?
• What is the use and effectiveness of BMPs? Chapter 2—BMP Management Information System Calculations are organized on worksheets by subject area, such as approaches to the water crossing, the crossing structure, chemical pollutants, and riparian area evaluation. An example of an Excel worksheet from the analysis file is shown below. Charts, graphs, and other figures are compiled from these worksheets and stored in a separate worksheet by subject area. Figures and information from the Excel analysis file are exported to Word files and become components of the standard data summaries.

5BMP Manual—Desk Reference 

## Word Document The standard data summaries that report the results of the queries are formatted in Word. Fifteen standard data summary templates focusing on specific areas of the BMP protocol are included with the BMP MIS (appendix C). The standard data summaries present the data using text and charts; additional text, figures, and photographs may be inserted to tailor the reports to various audiences. The individual standard data summaries, such as the sample shown below, can also be combined into a Comprehensive Standard Data Summary. While each software application has a specific job in the BMP MIS, it is important to understand that they work together to make analysis of field data possible and accessible to all participants using readily available software programs. The following chapters will guide you through the steps to set up and use the BMP MIS effectively to create a complete and comprehensive report from your field data.



## Overview of Sample Units 309 new sample unites were sampled. Number and Proportion of Sample Units by Feature A sample unit is likely to have more than one of activity or condition recorded; therefore, the total of activities and conditions will exceed the number of sample units (e.g., 493 activities and conditions in 309 sample units). 

## Number of New Sample Units by Feature

Wetland Chemical spill crossing=28 or pollutant No surface containers=4 water crossing=72

Riparian Haul road area=156 crossing=66

Haul road in the buffer=10 Skidder crossing=157

Figure X. (n=309) 

### Figure X. (n=309)

6Chapter 3—Installing and Using the BMP MIS 

# Chapter 3—Installing and Using the BMP MIS The CD provided with this desk reference includes several folders containing the electronic files you will need to process your field data. Two of the folders are referenced in this chapter: the Sample BMP MIS folder contains the files you will use in the practice exercises, and the BMP MIS folder contains files you will use to process the actual field data collected by your State or organization. This chapter guides you through the installation of the Sample BMP MIS folder onto your computer and illustrates how to use the files contained in it to generate standard data summaries. Instructions for compiling the standard data summaries into a Comprehensive Standard Data Summary are provided in chapter 4. After you have successfully completed the practice exercise using the files in the Sample BMP MIS folder, you will follow the same procedure for your actual field data using the files in the BMP MIS folder. Throughout this chapter, text boxes contain instructions for using the BMP MIS with actual field data that differ from those for the Sample BMP MIS practice exercise (see example below).

After completing the practice exercise using the Sample BMP MIS folder, use the same sequence of steps to copy the BMP MIS folder to your computer to analyze your actual field data. Rename the BMP MIS folder, using a unique name that represents the geographic area and the year in which the data were collected (e.g., BMP MIS Allagash 2007). Substitute BMP MIS (name) for Sample BMP MIS in the instructions in this chapter wherever applicable. 

## Copying the BMP Files to Your Computer Step 3.1. Copy the BMP Folder to Your C Drive Insert the BMP desk reference CD into your computer’s compact disc drive; the following window will open. Highlight the folder BMPand copy it to the C drive of your computer. The files in the BMP folder must remain in this folder on the C drive! You will be instructed to make copies of certain files and folders as necessary throughout the chapters of this document. Keep the original CD in a safe location.

7BMP Manual—Desk Reference 

## Step 3.2. Make a Copy of the Sample BMP MIS Folder Navigate to the BMPfolder you just copied onto your C drive and open it. Make a copy of the Sample BMP MIS folder and save it to a location of your choosing on your C drive (it should not be filed in the C:\BMP folder).

When using the BMP MIS to process your actual field data, copy the BMP MIS folder and rename it as BMP MIS (name) in this step.



## Step 3.3. View the Files in the Sample BMP MIS Folder Navigate to the Sample BMP MIS folder you just copied onto your computer and double-click on it. The following window will open, listing the files contained in the folder.

8Chapter 3—Installing and Using the BMP MIS This folder contains the following files:
• An Access database ( BMP MIS Field Data.mdb )
• Two Excel worksheets: one containing sample field data (XX Field Data.xls ) and one containing analysis of field

data (xx new sample unit analysis.xls )
• Fifteen Word files containing standard data summary (SDS) templates
• A Word file containing the cover, introduction, and table of contents to the Comprehensive Standard Data

Summary ( Comprehensive_Report_Cover_and_Introduction.doc) Keep your field data Excel file for the same data collection period in the BMP MIS (name) folder along with the standard data summaries you will be creating in a later step. Continued use of the BMP protocol will result in several uniquely named BMP MIS folders; following this procedure will help you to keep track of the field data files, query files, and data summary files according to the geographic area and time periods in which the data were collected. 

## Importing Field Data Into Access Data collected in the field are sorted and queried in Access. These sorts and queries are already defined in the Access file in the Sample BMP MIS folder; they simply need to be populated with your field data. In this section, you will practice importing a field data file to Access and creating a table using files in the Sample BMP MIS folder. Once the table is created, you will view the queries and export them to the Excel analysis file.

When processing actual field data, you should finish collecting data in the field and upload the data to an Excel file before beginning this procedure (see the BMP field guide, appendix A for instructions on uploading field data to an Excel file). If you upload additional field data after following the steps outlined in this chapter to generate standard data summaries, you will have to repeat the entire process to include the new data in the standard data summaries. 

## Step 3.4. Open the Access Database File Using the Password The Access database file in the Sample BMP MIS folder is password protected to ensure the security of field data. Double-click on the file BMP MIS Field Data.mdb to open it. A dialog box appears asking for the database password. The password is BMP MIS (it is case sensitive). Enter the password and select OK to open the database file.

9BMP Manual—Desk Reference 

## Step 3.5. Import Field Data Into an Access Table Using the menu on the left-hand side of the screen, under Objects , select Tables

.

You will create a new table here using your Excel field data file. Select Fileon the main toolbar, then select Get External Data and Import from the drop-down menus.

10Chapter 3—Installing and Using the BMP MIS The Import window will open. Since the sample field data are stored in an Excel file, change the file type in the Files of typebox at the bottom of the window to Microsoft Excel (*.xls) . Click on the down arrow and select this file type from the drop-down menu.

11BMP Manual—Desk Reference Navigate to the location of the Excel field data file. In this practice exercise, the sample field data file XX Field Data.xls is in the Sample BMP MIS folder. Select the file name to highlight it, then select Import

.

When processing your actual field data, you will navigate to and import your Excel actual field data file. If you have not already done so, copy the actual field data file into the BMP MIS (name) folder you have created. When you have finished the steps outlined in this chapter, you will have the actual field data file, the query files, and the standard data summary files all in the BMP MIS (name) folder named for the geographic area and time period in which the data were collected.

12Chapter 3—Installing and Using the BMP MIS The Import Spreadsheet Wizardwindow will open. The first window will ask which worksheet or range you would like to import. Select Show Worksheets . Make sure that Sheet1 is highlighted in the box. Select Next

.

When the following dialog box appears, select OK to proceed to the next step. Access will simply assign field names, or column headings, to the field data.

13BMP Manual—Desk Reference The next screen will ask if there are column headings on the table. Check First Row Contains Column Headings and select Next

.

14Chapter 3—Installing and Using the BMP MIS The next screen will ask where you would like to store your data. Select In a New Table , then select Next

.

The next screen will ask you about field options. Select Next to advance past this window. Do not enter any information here

.

15BMP Manual—Desk Reference The next screen will ask about indexing the data table with a primary key. Select Let Access add primary key, then select Next

.

The last screen in the Import Spreadsheet Wizardwill ask you to name the table. The title of the worksheet appears as the default. Delete that table name and enter BMP_Protocol_Field_Data . Select Finish

. Important : The table must be named BMP_Protocol_Field_Data . Access queries will not work unless this name is used.

16Chapter 3—Installing and Using the BMP MIS It is important to use the table name BMP_Protocol_Field_Data when you process your actual field data. The BMP MIS process will then produce a file called BMP_Protocol_Field_Data.xls . Each subsequent actual field data file will use this same file name; therefore, it is important that you create a new BMP MIS (name) folder for each geographic area and time period in which the data were collected. Place the actual field data file into this folder to keep it from being confused with actual field data files from other locations and time periods.

This table must be named BMP_Protocol_Field_Data.

Access will import the field data from the Excel file and save them into the table BMP_Protocol_Field_Data

. When the import is complete, the following dialog box will appear. Select OK

.

17BMP Manual—Desk Reference You will be returned to Access. The new table will appear in the Tables window. If the field data table is imported successfully, proceed to step 3.7.



## Step 3.6. Resolve Import Errors You may experience errors when importing data into Access. Errors usually result when there is unrecognizable data in a cell in the Excel file. This “bad” data may simply be a keystroke error made when collecting data in the field. If you have any import errors, the following dialog box will open when Access tries to complete the import process. Select OK

.

18Chapter 3—Installing and Using the BMP MIS Access will save references to the errors in a table so that you may locate and correct them. To locate the errors in the BMP_Protocol_Field_Data table, open the ImportErrors table (in this practice exercise, it is called Sheet1 $_ImportErrors ) by double-clicking on the table name. ( Note : The table name may vary depending on the input data file name, but the file name will always end in _ImportErrors .)

The ImportErrorstable is a matrix describing the type of error, and the field (column heading) and row where it is located. The field name corresponds to the BMP protocol question number. A Type Conversionerror means that Access does not recognize the data in the cell.

The Field refers to the column heading. It corresponds to a BMP protocol question number

.

19BMP Manual—Desk Reference To correct the errors, open the BMP_Protocol_Field_Data table by double-clicking on the table name. Locate the cell at the intersection of the field (column) and row listed in the ImportErrors table. In this example, there is a caret (^) in the cell at the intersection of column X66 and row 38 indicating an import error.

Field

Carat indicates import error

.

Row number

Correct the error to the best of your ability. It may help to look at the BMP field guide to determine what question X66 is asking. Check each cell reference in the ImportErrors table and correct the errors. When you have finished, close the BMP_Protocol_Field_Data table and continue with step 3.7. Important : Correct all errors in the BMP_Protocol_Field_Data table before proceeding.

20Chapter 3—Installing and Using the BMP MIS 

## Step 3.7. Export Access Queries to the Excel Analysis File The Access database file BMP MIS Field Data.mdbis populated with queries that sort and filter the field data in numerous ways. When you imported the sample field data into Access, the queries were automatically applied to the data. View the list of Access query files by selecting Queriesunder Objects . Each file is a separate query—a set of filtered and sorted field data.

21BMP Manual—Desk Reference Double-click on the query file N Sed WB Bin to view a query file, as shown below. Close the window when finished.

22Chapter 3—Installing and Using the BMP MIS Each query is named according to the filtered data it contains. The list of abbreviations used to name the queries can be found in appendix A; a list of query names and descriptions is in appendix B. You may also find a description of each query by right-clicking on the query name in Access and selecting Properties . The following is an example of the Propertieswindow of the query N Sed WB Bin , including the query description. Select Cancel to close the Properties window and return to the list of queries.

While Access queries sort field data in a variety of ways, data analysis occurs in Excel. The data that will be presented in the standard data summaries are linked to the Excel file in which the analysis occurs. The Access queries must be exported from Access to Excel before the summaries can be generated. Eachquery in the Access file must be exported individually. The instructions provided walk you through the step-by-step process for exporting one query using the sample files.

23BMP Manual—Desk Reference In this example, you will export the Access query New Samples , which contains data from all new sample units in the Sample BMP MIS folder. Right-click on the query New Samplesand select Export from the drop-down menu.

24Chapter 3—Installing and Using the BMP MIS The Export Query ‘New Samples’ To... window will open. Select the type of file you are exporting the query to in the Save as typebox at the bottom of the window. Click on the down arrow to open the drop-down menu and select the most recent version of Microsoft Excel available.

Select the most recent version of Excel available.

25BMP Manual—Desk Reference Navigate to the Excel file where the query will be exported. In the Sample BMP MIS folder, the Excel file is called xx new sample unit analysis.xls . Select this file name; the file name will also appear in the File namebox at the bottom of the Export Query ‘New Samples’ To... window. Select Export

.

When working with your actual field data in the BMP MIS (name) folder, the Excel analysis file is named new sample unit analysis.xls

.

Select the Excel analysis file. The file name will appear in the File Name box.

Access will attempt to export the query to the Excel file you have selected. Because the Excel analysis file has an empty worksheet named for each Access query (the blank worksheet acts as a placeholder for your data), Access will display the following message as it attempts to export the query. Select Yes . Access will export the data and populate the Excel worksheet with the query of your field data.

26Chapter 3—Installing and Using the BMP MIS Repeat this process with several other queries in order to become familiar with the process. When you are working with actual field data, it will be necessary to export eachof the 118 Access queries to the Excel analysis file individually before proceeding to the next step. When all of the Access queries have been exported to Excel, close Access.

When working with your actual field data in the BMP MIS (name) folder, you must export eachquery file individually. The export process must be repeated for every one of the Access queries.

27BMP Manual—Desk Reference 

## Analyzing Field Data in Excel Once the Excel analysis file is populated with the Access queries, it will perform calculations based on the queries. In order for Excel to complete the calculations, you must open the Excel analysis file; the formulas in the worksheets will recalculate based on the queries you just exported to the Excel analysis file. 

## Step 3.8. Open the Excel Analysis File Open the Excel analysis file xx new sample unit analysis.xls

.

When you are working with your actual field data in the BMP MIS (name) folder, the Excel analysis file will be called new sample unit analysis.xls

. The file contains several worksheets, with color-coded tabs, presented in order as follows:
• Blue-tabbed worksheets analyze field data.
• Yellow-tabbed worksheets contain charts and graphs created from information in the field data analysis

worksheets.
• White- or gray-tabbed worksheets are copies of the Access queries. The Excel analysis file will appear as shown below when opened (note the tab colors at the bottom left of the screen).

28Chapter 3—Installing and Using the BMP MIS 

## Step 3.9. Review the Contents of the Excel Analysis File Familiarize yourself with the organization of the Excel analysis file; the worksheets contain a great deal of information. Note that the Excel analysis worksheets are not the standard data summaries, which present the results in a clear and concise format and give the preparer the opportunity to further interpret and explain the findings. Information in the analysis (blue-tabbed) and charts (yellow-tabbed) worksheets is linked to the standard data summaries, which are formatted in Word. Because the standard data summaries depend on accurate links to the Excel analysis file, the analysis and charts worksheets are protected—you can view information in the analysis and charts worksheets but cannot make changes to them. If you attempt to modify a protected worksheet, you will see the following error message.

Although Excel gives you the option to remove protection from the worksheet, it is strongly recommended that worksheet protections remain intact . Accidentally changing the analysis and chart worksheets may result in broken or undefined links in the standard data summaries. Select OK to continue. Important : Do not remove protections from the Excel analysis or charts worksheets. 

## Step 3.10. Close the Excel Analysis File Close the file xx new sample unit analysis.xls after viewing its contents. You will be asked if you wish to save changes to the file. Select Yes to save the calculations that Excel made on the analysis worksheets. Results of these calculations will be pulled into the standard data summaries as discussed in the next section.

29BMP Manual—Desk Reference 

## Generating Standard Data Summaries in Word After the data has been extensively queried in Excel, the resulting information is presented in a series of standard data summaries (SDSs). The SDSs are organized to correspond to the focus areas in the BMP protocol, such as general information, approaches to a surface water crossing, crossing structures, and chemical pollutants. Each SDS may be used individually or combined with others to create a Comprehensive Standard Data Summary of your field data. A list and description of all the SDSs provided in the BMP MIS appear in appendix C. In this part of the practice exercise, you will populate the SDSs with the newly calculated information from the Excel analysis file xx new sample unit analysis.xls . Chapter 4 discusses SDSs and the creation of a Comprehensive Standard Data Summary in greater detail. 

## Step 3.11. Open the SDS File and Update the Links Open the Sample BMP MIS folder and review the SDS files it contains. All of the SDS files have the acronym SDS in the file name. In this example, you will work with the file New Sample Unit SDS.doc

.

Double-click on the file New Sample Unit SDS.docto open it. The following dialog box will appear.

30Chapter 3—Installing and Using the BMP MIS As discussed previously, the information in the SDS files is linked to the Excel analysis file. Answering Yes directs the computer to seek out the Excel analysis file and update each piece of numeric information and each chart or graph in the SDS. Answering Nosimply opens the file and presents the information as it was last saved. If you were to select No , the SDS would open but it would not update the links to data in the Excel analysis file. With no linked data, graphs and charts would be incomplete as shown below.



## Overview of Sample Units 0 new sample units were sampled. Number and Proportion of Sample Units by Feature A sample unit is likely to have more than one of activity or condition recorded; therefore, the total of activities and conditions will exceed the number of sample units (e.g., 0activities and conditions in 0 sample units). 

## Number of New Sample Units by Feature Haul road in the Chemical spill or buffer/filter strip, Haul road No surface water Wetland crossing, Skidder 10 pollutant crossings, 66 crossing, 72 28 crossings, 157 Riparian area, 156 containers, 4

Figure X. (n=0)

31BMP Manual—Desk Reference If you select Yes , Word will update the links with the information you just exported into the Excel analysis file. The SDS template now contains current information.



## Overview of Sample Units 309 new sample unites were sampled. Number and Proportion of Sample Units by Feature A sample unit is likely to have more than one of activity or condition recorded; therefore, the total of activities and conditions will exceed the number of sample units (e.g., 493 activities and conditions in 309 sample units). 

## Number of New Sample Units by Feature

Wetland Chemical spill No surface crossing=72

Riparian Haul road area=156 crossing=66

Haul road in the buffer=10 crossing=157 cr ossing=28 or p ollutan t c on tainers=4 w a t er

S k idder

Figure X. (n=309) 

### Figure X. (n=309)

After updating the links, save and close the file New Sample Unit SDS.doc . Do not change the file name. Note : All file names should remain unchanged unless specifically instructed otherwise. As long as files are placed in a uniquely named folder (e.g., BMP MIS Allagash 2007), there will be no confusion with other similarly named files.

32Chapter 3—Installing and Using the BMP MIS 

## Step 3.12. Repeat the Process With the Remaining SDS Files Open and update the links in eachof the 15 SDS template files in the Sample BMP MIS folder. Becoming familiar with these steps will make generating the SDSs and creating a Comprehensive Standard Data Summary with actual field data much easier. Chapter 4 provides instruction and other information necessary for assembling the SDSs into a Comprehensive Standard Data Summary. 

## Processing Actual Field Data After completing the entire practice exercise using the Sample BMP MIS folder, make a copy of the original BMP MIS folder and rename it BMP MIS (name) . Use the BMP MIS (name) folder to analyze your actual field data. Repeat the steps in this chapter, substituting BMP MIS (name) for Sample BMP MIS whenever necessary and following the instructions specific to the BMP MIS (name) folder.

33Chapter 4—SDSs and Comprehensive Standard Data Summaries 

# Chapter 4—SDSs and Comprehensive Standard Data Summaries The standard data summaries (SDSs) provided in the BMP MIS present the results of BMP performance in a concise and easy-to-read format. The SDSs are intended to be combined into a Comprehensive Standard Data Summary, but they may be used individually to assess BMP performance for individual focus areas. You may add explanations, interpretation, and additional supporting figures and photographs to generate complete and comprehensive reports tailored to various audiences. The reports may then be used to identify opportunities for improvement through additional planning, training, supervision, and enforcement, thus strengthening your State’s or organization’s capacity to address water quality problems related to forest management activities.


## Structure of the Standard Data Summaries Each of the 15 SDS template files provided in the BMP MIS is named for the focus area presented in the SDS. For example, the file New SU Approaches SDS.docpresents information regarding approaches to surface water crossings on new sample units. A description of each SDS file may be found by right-clicking on the file name and choosing Properties from the drop-down menu. Select the Summary tab at the top right of the Properties window. A description of the SDS is in the Commentsfield. An example of the Propertieswindow for New SU Approaches SDS.docappears below. A list and description of all the SDS template files available also appears in appendix C.

35BMP Manual—Desk Reference The SDSs are linked directly to the Excel analysis file. If data are changed or updated in the Access query and exported to the Excel analysis file, you have the opportunity to update the links automatically in the SDS file when the document is opened. The following dialog box appears when you open an SDS file. Selecting Yes directs the computer to seek out the linked information in the Excel analysis file and update the information in the SDS file.

A sample page from the SDS New SU Approaches SDS.docis shown below. The highlighted elements are those linked to the Excel analysis file. The ability to update linked information in SDSs will be especially useful when creating custom queries, which is discussed in chapter 5.



## Approaches to the Water Crossing There are 4 opportunities to observe the occurrence of soil movement, sedimentation, or stabilization from the approaches to a surface water crossing. They are at Approach Area A–Outside the Buffer/ Filter Strip, Approach Area A–Inside the Buffer/Filter Strip, Approach Area B–Inside the Buffer/Filter Strip, and Approach Area B–Outside the Buffer/Filter Strip. Proportions are based on the total number of opportunities to make observations about soil conditions at the approaches. For the 309 new sample units, there are 1236 opportunities to observe soil conditions.

100%

80%
60%
40% 43%
19% 7% 6% 27% 20%

0% Soil stable Sedimentation (trace) No surface water crossing Soil moves (does not

reach water) Sedimentation (measurable) Observations of Soil Stabilization, Movement, and Sedimentation at the Approaches

Figure X . Proportions are based on the total number of opportunities to observe soil conditions at the approaches (n= 1236 ). 

### Discussion <OPTION: User may insert analysis, interpretation, and photographs here.>
36Chapter 4—SDSs and Comprehensive Standard Data Summaries As shown on the previous page, the SDSs begin with a brief description of the data presented and how the information was calculated, followed by results of the calculations and supporting figures. There are also placeholders in the SDS files where text and images can be inserted to explain, interpret, and analyze the information presented. The SDSs may be used alone or compiled into a Comprehensive Standard Data Summary evaluating a project’s BMP performance. 

## Calculation of Proportions and the Presentation of Null Values Where applicable, percentages presented in the SDSs are calculated on the total number of possibilities for a condition to occur based on the number of questions about that condition in the BMP protocol. For example, when evaluating the approaches to the surface water crossing, if there are 75 new sample units and 4 questions in the protocol where soil stabilization, movement, or sedimentation are evaluated, there are a total of 300 possible opportunities to make observations about soil conditions at the approaches (75 × 4 = 300). Those new sample units without a surface water crossing, and thus no approaches, are included as part of this total, as there are still four questions addressing conditions for these sample units. The number of opportunities to make observations about conditions at the approaches on sample units where approaches do not exist is a null value

. The null values are included in the SDSs to ensure that the percentages total 100 percent and the frequency of problems is accurately reported

. The chart below shows the proportion of observations of soil stabilization, movement, and sedimentation at the approaches to a surface water crossing from a sample data set. There are 309 sample units in this data set, each with 4 questions assessing soil conditions at the approaches; therefore, there are 1,236 opportunities to observe soil conditions within this data set (n = 1,236). Although 83 sample units do not have a surface water crossing, there are still 332 opportunities to observe soil conditions on these sample units. Therefore, 332, or 27 percent, of the total number of possible observations of soil conditions are made on sample units without a surface water crossing.

100%

80%
60%
40% 43%
19% 7% 6% 27% 20%

0% Soil stable Sedimentation (trace) No surface water crossing Soil moves (does not

reach water) Sedimentation (measurable) Observations of Soil Stabilization, Movement, and Sedimentation at the Approaches

You will see null values presented in many SDSs. They allow the reader to assess performance of the BMP protocol based on the total number of possible observations of a given condition, not as a proportion of a subset of the data. 37BMP Manual—Desk Reference 

## Creating Comprehensive Standard Data Summaries The 15 SDSs can be used individually or compiled into a Comprehensive Standard Data Summary. It is advisable to compile the completed set of SDSs into a Comprehensive Standard Data Summary summarizing performance on the entire protocol. A logical sequence for combining the SDSs into a Comprehensive Standard Data Summary is provided in appendix C. An example of a Comprehensive Standard Data Summary consisting of portions of all 15 SDSs is presented in appendix D. There are two ways to compile a Comprehensive Standard Data Summary: 1.

Print each SDS individually and assemble them into one report, or 2.

Electronically combine the SDSs into a comprehensive report. When SDSs are first combined into a Comprehensive Standard Data Summary, links to the Excel analysis file are maintained within the new document. Since maintaining and updating links requires a significant amount of computer memory, it is recommended that you close all other computer programs or applications before compiling the Comprehensive Standard Data Summary . If available computer memory becomes a problem when compiling the Comprehensive Standard Data Summary, create two or more smaller files instead of one large file.

Note : Once the Comprehensive Standard Data Summary is compiled from the individual SDS files, the electronic links may be broken, which will greatly reduce the file size and facilitate e-mailing of the document. Breaking the electronic links is discussed later in this chapter. To assemble individual SDSs into one report, simply print out each SDS and attach the documents together. To create a Comprehensive Standard Data Summary within the BMP MIS, follow steps 4.1 through 4.4.

As in chapter 3, the steps outlined in this chapter use the files in the Sample BMP MIS folder. Once you have completed the steps successfully using the Sample BMP MIS folder, repeat the process using the files in your BMP MIS (name) folder. 

## Step 4.1. Create the Comprehensive Standard Data Summary Document Navigate to the Sample BMP MIS folder. Open the file Comprehensive_Report_Cover_and_Introduction. doc . Select Filefrom the main toolbar again and Save Asfrom the drop-down menu. When the Save As window appears, name the document using clear nomenclature that includes the State or organizational name or abbreviation and the date. In the sample screen shown on the following page, the new file has been named Comprehensive Report New SU XX 081506.doc . Similar nomenclature can be used, substituting the State or organization abbreviation for XX. The Comprehensive Standard Data Summary file must remain in the same folder as the Excel analysis file in order for the links to remain active and defined.

38Chapter 4—SDSs and Comprehensive Standard Data Summaries

Include your State or organization name and the date in the file name of the Comprehensive Standard Data Summary

.



## Step 4.2. Insert a File Into the New Document Place your cursor at the very end of the document you just created. Select Insert from the main toolbar and File from the drop-down menu.

39BMP Manual—Desk Reference The Insert Filewindow will open. Select the file New SU General Information SDS.docto highlight it, then select Insert . The contents of the file will be copied into the Comprehensive Standard Data Summary file. The cursor will be left at the end of the inserted file.



## Step 4.3. Insert the Remaining SDS Files Into the Document Repeat step 4.2 for each SDS to be inserted into the Comprehensive Standard Data Summary. Appendix C includes the recommended sequence of SDSs, but you may choose what SDSs to include and in what order. 

## Step 4.4. Save the Comprehensive Standard Data Summary File Once you have copied all of the desired SDS files into the Comprehensive Standard Data Summary, save the file before closing it. Maintain this file as your “master” Comprehensive Standard Data Summary. You may wish to augment the information presented with additional text or images (see the Adding Text and Images section in this chapter). 

## Breaking the Links There are two important benefits to breaking the links with the Excel analysis file after the Comprehensive Standard Data Summary file is complete: 1.

Files without links require less memory and storage space than those files maintaining links. 2.

Files without links can be easily e-mailed because they are smaller and they can be sent without including the

Excel analysis file. If the links were not broken and the Excel analysis file were not e-mailed along with the Comprehensive Standard Data Summary file, the recipient would encounter an error when opening the file.
40Chapter 4—SDSs and Comprehensive Standard Data Summaries The procedure for breaking the links affects only the document you are in when you break the links. Therefore, when you open the Comprehensive Standard Data Summary file and break the links, you break only the links to that file. Links between the query files and the SDS files are not affected. Once the links are broken, however, the procedure cannot be reversed. Do NOT break the links with the Excel analysis file until the Comprehensive Standard Data Summary is COMPLETE, as this action cannot be undone

. Once the Comprehensive Standard Data Summary is complete, it is advisable to break all links with the Excel analysis file to reduce the overall file size. 

## Step 4.5. Select the Links Open the Comprehensive Standard Data Summary file. (It may take some time to open the file as Word updates the data links.) Select Edit from the main toolbar and Linksfrom the drop-down menu.

41BMP Manual—Desk Reference The Links window will open. Each piece of linked information in the Comprehensive Standard Data Summary will be listed.

Each line in this box is one piece of information linked to the Excel analysis file.

The first link will be highlighted. Select all of the links listed in the box by holding down the Shift key and pressing thedown-arrow key until each link is highlighted.

Highlight all links.



## Step 4.6. Break the Links When all of the links are highlighted, select Break Link to the right of the box. You will have an opportunity to cancel this action before the links are broken. A dialog box will open asking if you are sure you want to break the 42Chapter 4—SDSs and Comprehensive Standard Data Summaries links. Select Yes to break all of the links. The document is now a standard Word document with no active links to the Excel analysis file. It may be copied or moved to another file folder for archiving. Save the document before closing it.



## Adding Text and Images Text and images may be added to the individual SDSs or the Comprehensive Standard Data Summary. This feature allows you to explain, interpret, or analyze the information presented. You may want to customize the Comprehensive Standard Data Summary for different purposes or audiences, each with a different level of detail or emphasis. If so, save a copy of the Comprehensive Standard Data Summary and add or delete text and images as necessary for each use. The SDS template files include placeholders in the body of the document for adding text and images. These placeholders will be copied into the Comprehensive Standard Data Summary and appear as follows: Discussion <OPTION: User may insert analysis, interpretation, and photographs here.> The location of these discussion sections is a suggestion only; because the Comprehensive Standard Data Summary file is a Word document, sections can be moved to wherever the report preparer wishes. Likewise, additional discussion sections may be added, or the discussion sections may be eliminated all together. Even without the discussion sections, the Comprehensive Standard Data Summary reports on BMP performance and effectiveness. The discussion sections do, however, offer the opportunity to add explanatory details.
43BMP Manual—Desk Reference 

## Step 4.7. Add Text To add text into the Comprehensive Standard Data Summary, simply open the document, highlight the line “<OPTION: User may insert analysis, interpretation, and photographs here.>”, and type your own text or copy text from another document. Save the document before closing it. 

## Step 4.8. Add Images To add images such as digital photographs, place the cursor where you wish to insert an image into the Comprehensive Standard Data Summary. Select Insert from the main toolbar, then select Pictureand From File from the drop-down menus.

44Chapter 4—SDSs and Comprehensive Standard Data Summaries When the Insert Picturewindow opens, navigate to the location of the file to be inserted. Select the file name in the Insert Picturewindow to highlight it and select Insert in the lower right-hand corner of the window. The image will be inserted into the document. Formatting options are available by right-clicking on the image and selecting Format Picturefrom the drop-down menu.

Repeat the process as necessary for additional images. Save the document before closing it. 

## Step 4.9. Remove Unused Placeholders When the Comprehensive Standard Data Summary is complete and all desired text and images have been added, scan through the document and delete all unused discussion section placeholders. Save the document before closing it. 

## Finalizing the Comprehensive Standard Data Summary Once the content of the Comprehensive Standard Data Summary is complete, it is recommended that you “clean up” some of the document formatting. The formatting of the SDSs was kept fairly simple for ease of use; as a result, some manual formatting is necessary. The items listed below are simply suggestions; you may customize the document however you prefer. See the example in appendix D.
• Insert the report title and other information suggested on the cover.
• Insert the number of sample units measured under the Data Summary heading on page ii. The number can be

found under the General Information Feature heading.
• Update the Contents on page iii to reflect the SDSs you included and the order in which they appear.
• Insert page breaks where necessary to keep headings and data on the same page, or to start major headings on a

right-facing page. Delete any unnecessary page breaks. 45BMP Manual—Desk Reference
• Delete any text or graphics that are not needed for the purpose of your report, including unused placeholders.
• Number the figures and tables sequentially, or remove the figure and table identifiers.
• Once the document is complete, insert the page numbers of the major headings in the Contents on page iii. 

## Tips for Troubleshooting Here are some suggestions for managing common questions or problems. Scenario 1 : An SDS file is inserted into the Comprehensive Standard Data Summary in error. Solution : You may immediately remove the SDS file most recently inserted into the Comprehensive Standard Data Summary by selecting Edit from the main toolbar and Undo Filefrom the drop-down menu. You may also delete an SDS (or text or an image from within an SDS) from within the Comprehensive Standard Data Summary by simply highlighting and deleting the material as you would in any other Word document.

Scenario 2 : Error messages appear for some of the data in the SDS. Solution : Some calculations in the Excel analysis file may return an error message, as in the sediment volume table shown on the following page. The error message simply means that no volume measurements were recorded for that condition. As a result, Excel will return an error message when it is not able to perform the mathematical operation in the formula. 46Chapter 4—SDSs and Comprehensive Standard Data Summaries In the example shown, Excel returned a #DIV/0! error in the average column because it is not possible to divide by zero. Similarly, the median calculation does not count zeros or empty cells because the formula in the Excel analysis file directs Excel to ignore these entries. Since there are no other entries greater than zero in that column, Excel returned a #NUM! error when the median value was calculated. Once you have completed your Comprehensive Standard Data Summary and broken all of the links to the Excel analysis file, you may edit the error messages out of the tables. For example, you may substitute an “N/A” for not applicable where these error messages occur. Table X. Quantities of Sedimentation by Crossing Structure Type Sediment Volumes (cubic feet) Average Median Maximum Unimproved ford 16 5 80 Improved/constructed ford #DIV/0! #NUM! 0 Pole/brush ford 116 9 600 Single culvert 27 4 123 Multiple culvert 6 4 15 Bridge/box culvert, closed top 59 12 200 Bridge/box culvert, open top 16 6 54 Structure removed 70 6 606 Unknown/other 76 76 150 Nonnumeric values indicate that no volume measurements were recorded.

Scenario 3 : You have attached your Comprehensive Standard Data Summary to an e-mail and sent it. When the recipient opens the report and tries to update the links, the following error appears.

Solution : The Comprehensive Standard Data Summary is maintaining links to the Excel analysis file. Make sure that the links have been broken in your Comprehensive Standard Data Summary file before e-mailing it (see the Breaking the Links section in this chapter).

47Chapter 5—Custom Queries and Data Summaries 

# Chapter 5—Custom Queries and Data Summaries The standard data summaries provided in the BMP MIS present the results of BMP performance by linking electronically to queries that sort information from the protocol data. Although several standard data summaries (SDSs) are provided, you may wish to focus on some of the protocol data in more detail. There are many possibilities for queries from your data; it would be impossible to address them all here. You may wish to create custom queries beyond the standard queries included with the protocol to more completely explore BMP performance. This chapter includes basic guidelines for creating custom queries in Access, writing formulas for higher level analysis in Excel, and creating your own custom SDSs. In order to develop a custom query, the appropriate data must be filtered out of the field data in the Access database and exported to a worksheet in an Excel analysis file, where you can work on the data in more detail. A number of queries of field data already exist in the Access database; you can them use in creating additional SDSs. Standard data summaries are often based on several queries. Some of the queries you need to create new SDSs may already exist in the Access database. A list of the Access queries provided in the BMP MIS and their descriptions appear in appendix B. Review these existing queries carefully to determine if the subset of data you need has already been created. If the query you are looking for already exists, you can skip to the second section of this chapter (Exporting the Query to Excel). If you need to create a new query of field data in Access, begin with step 5.1. The process of creating a new query, although not difficult, requires concentration and a lengthy written explanation. Since all queries are created using the same steps, this chapter will explain the creation of a single query. You can then follow the same process to create additional queries of your own choosing. Note that most SDSs utilize information from several queries, so it is important to check to appendix B to determine if the queries you need already exist.

Important : This chapter uses data in the Sample BMP MIS folder to demonstrate building a simple query. It is recommended that you follow the steps outlined before attempting to design your own custom query. A query based on the criteria used—new sample units where a logger is responsible for BMP implementation by written contract (as part of the sale agreement)—already exists in the BMP MIS. The query will be given a slightly different name to differentiate it from the existing query.


## Creating a Query in Access Step 5.1. Gather the Documentation Needed Before you begin designing a query in Access, be sure you have the following tools available:
• BMP field guide (the companion guide to this publication), which contains the complete text of all of the BMP

protocol questions
• Map of the BMP protocol questions, which appears in appendix E of this desk reference The question map breaks the BMP protocol into focus areas. It acts as a shorthand list of the subject of each question and a guide to the column in which each question appears in the Excel analysis file. The question map is extremely helpful when defining queries in Access and writing formulas to analyze queries in Excel.

49BMP Manual—Desk Reference 

## Step 5.2. Navigate to the Query Screen Open the Access database ( BMP MIS Field Data.mdb ), using the password provided in chapter 3. On the lefthand side of the screen under Objects , select Queriesto view the entire list of available queries.

50Chapter 5—Custom Queries and Data Summaries 

## Step 5.3. Verify That Access Is Set to Return All Fields in a Query Access filters information, but unless told to do otherwise, it will return only the fields defined as criteria. In the BMP MIS, Access must return allof the fields defined in the protocol. To ensure that all data points recorded for each sample unit are returned in the query, select Tools from the main toolbar and Optionsfrom the drop-down menu.

51BMP Manual—Desk Reference The Options window will open. Select the Tables/Queriestab. In the left-hand side of the window is the heading Query design . Make sure the Output all fieldsbox is checked. Select OK to close the Optionswindow.

This box must be checked

52Chapter 5—Custom Queries and Data Summaries 

## Step 5.4. Create a New Query To begin creating a new query, select the New button on the Access toolbar (below the main toolbar).

The New Query window will open. You will use Design View to create your query in Access. Highlight Design View and select OK

.

53BMP Manual—Desk Reference The Design View of the new query will open, along with the Show Tablewindow. The Show Table window lists the tables and queries on which the criteria in the new query can be based. The Tables tab lists the main database table where all field data are stored. As discussed in previous chapters, the table containing the field data must always be called BMP_Protocol_Field_Data in order for the queries to work properly.

The Queries tab lists all of the queries already defined in Access.

54Chapter 5—Custom Queries and Data Summaries The Both tab lists both tables and queries together. The new query may be based on the entire field data table or on an existing query. If the new query is a subset of information in an existing query, the new query may be based on the existing query. For example, if the new query will return all new sample units that have evidence of sediment delivered to a water body, it may be based on the existing query New Samples , which returns all new sample units in the database. For the purposes of this exercise, the new query will be based on the table BMP_ Protocol_Field_Data

. In the Show Table window, select the Tables tab, highlight the table BMP_Protocol_Field_Data , and select Add . The table will be added to the Design View screen. Select Close

.

55BMP Manual—Desk Reference The next screen is divided into two parts:
• The top of the screen contains a box with the table you just selected, BMP_Protocol_Field_Data . The fields that

make up this table, which are also the question numbers from the BMP protocol, are listed in the box. You may increase the size of the box for easier viewing by clicking on its border and dragging it to the desired size.
• The bottom half of the screen is a blank table in which you will define the criteria for your new query.

Your query will be based on the following criteria: new sample units where a logger is responsible for BMP implementation by written contract. The question map in appendix E indicates that the limiting criteria are the questions Sample Type (under questionG1 ) and G5 ( BMP Responsibility Assigned ). You need to select these questions from the BMP_Protocol_Field_Data table and move them to the empty table at the bottom of the screen. There are three ways to accomplish this (choose one):

• Double-click on each question ( Sample Typeand G5 ) inside the BMP_Protocol_Field_Data table on the top the screen,

• Select the question inside the BMP_Protocol_Field_Data table on the top of the screen and drag it to an empty column below, or

• Select the empty Fieldcell in the empty table. A drop-down menu will appear with the field names from

BMP_Protocol_Field_Data . Select the appropriate question ( Sample Typeand G5 ) from the drop-down list.

56Chapter 5—Custom Queries and Data Summaries Use whichever method you prefer to move the two questions to the blank table, as shown below.

You have defined the fields to search, but not the criteria that will be used to filter those fields. That information will be entered in the Criteria row. The criteria must be defined exactly the same way they appear in the database. In order to view all new sample units where a logger is responsible for BMP implementation by written contract, the query must return all sample units that meet bothof the selection criteria. For Sample Type , all entries with the answer N(for new sample unit) meet the criteria of being new sample units. Enter Nin the criteria cell in the column for Sample Type . It is important that the criteria not include sample units with the answer Q (for quality control sample), as these would be duplicates of new sample units already included in the selection criteria.

57BMP Manual—Desk Reference Question G5 asks if responsibility for BMP implementation is assigned to a specific individual. Answer choice 4 indicates that responsibility for BMP implementation is assigned to the logger by written contract. Enter 4in the criteria cell in the column for G5 . The screen should appear as shown below.

Important : S ince the cr iter ia are entered in the same row in the table , the sample units retur ned will meet both co nditio ns. If y ou are searc hing o n cr iter ia that ma y meet either co nditio n, enter the cr iter ia o n diff erent rows. F

or example , the table below will retur n sample units that are either new or have a logging co ntr a ctor respo nsible f or BMP implementatio n by wr itten co ntr a ct as par t of the sale agreement. The results of this query are very diff erent.

58Chapter 5—Custom Queries and Data Summaries There are many ways to define criteria in Access. In the BMP MIS, the exact response to the question in the BMP protocol may often be entered as the limiting criteria. Some additional examples of ways to limit criteria appear in appendix F. It is also helpful to open the queries provided and switch to the Design View to see how the criteria were defined. It is best to keep Access queries as simple as possible and to use Excel to answer the more sophisticated questions that are based on the query. You may view the results of your new query by changing from the Design View to the Datasheet View . Select View on the main toolbar and Datasheet View from the drop-down menu.

59BMP Manual—Desk Reference An alternate method is to select the View button on the toolbar to toggle between the Design View and the Datasheet View

.

View button

Access will return only sample units that meet the criteria you defined in the Design View table. The Datasheet View returned by this query appears as follows.

60Chapter 5—Custom Queries and Data Summaries Switch back and forth between the Datasheet View and the Design View until you are comfortable with changing views. Make adjustments to the criteria in the Design View , if necessary, until the query returns the subset of data you need. Note that in the Datasheet View , the fields used to define the criteria for the query (Sample Type and G5) have been moved to the first two columns of the datasheet. While it is useful to have those fields in this position to verify that the new query is sorting on the desired criteria, you MUST move the columns back into their appropriate position—the order (column position) in which the question appears in the BMP protocol. Keeping the columns in the same order as they appear in the BMP protocol data file is critical to analyzing the data in Excel later in the process

. To move the fields back to their appropriate column positions once you are satisfied with the new query, return to the Design View and uncheck the boxes for each field in the Show row in the table.

Uncheck these boxes

Return to the Datasheet View . The criteria fields no longer appear in the first two columns; they are back in their original column positions in the query. Save the new query by selecting Filefrom the main toolbar and Save Asfrom the drop-down menu. In the Save Aswindow, enter the name of the new query. Whenever possible, use the abbreviations suggested in appendix A. The query name cannot exceed 31 characters, including spaces. This query will be saved as Example Loggr Cntrct to differentiate it from the existing query (N Loggr Cntrct).

Select OK to save the query. Close the query file by selecting Filefrom the main toolbar and Closefrom the drop-down menu. You will be returned to the query screen. The new query will be listed in alphabetical order along with the other queries. 61BMP Manual—Desk Reference 

## Step 5.5. Define the Contents of the New Query in Properties Because the BMP MIS contains many Access queries, you should define the contents of the query in the description box in its Properties window. You may refer to the Properties window at any time to recall the query description. In the Queries screen, right-click on the name of the new query and select Properties from the drop-down menu.

62Chapter 5—Custom Queries and Data Summaries When the Properties window opens, enter a description for the query in the description box. Select OK to close the Properties window. The description will be saved. You may now export the new query to the Excel analysis file and work further on analyzing the BMP protocol field data (step 5.6).

Enter the query description here.

63BMP Manual—Desk Reference 

## Exporting the Query to Excel Once the query is defined in Access, you have a subset of field data available to work with. To further analyze the data in the new query, the data must be exported to the Excel analysis file. 

## Step 5.6. Select the Query to Be Exported In the Queries screen, right-click on the new query Example Loggr Cntrct and select Exportfrom the dropdown menu.

64Chapter 5—Custom Queries and Data Summaries The Export Query window will open. Navigate to the Sample BMP MIS folder if you are not already there.

65BMP Manual—Desk Reference 

## Step 5.7. Select the File Where the Query Will Be Exported Because the query file is currently in Access, the files that appear in the Export Query window are Access files. However, you want to export the query to the Excel analysis file. To select that file, first choose Excelas the file type by clicking on the drop-down menu next to the Save as typebox. There may be several versions of Excel listed; choose the latest version available on your computer. After selecting Excel as the file type, the Excel files in the current folder will appear in the window. There will be two files in the Sample BMP MIS folder: one will contain field data and the other will be the BMP protocol data analysis file. Select the Excel analysis file xx new sample unit analysis.xls . The file name will appear in the File name box at the bottom of the window.

Select the Excel analysis file. The file name will appear in the File name box.

66Chapter 5—Custom Queries and Data Summaries 

## Step 5.8. Export the Query Select Export . The query will be copied into the Excel analysis file.
Note : You may need to change the parameters of the custom query that you designed in Access to correct an error or change the criteria after it has been exported to Excel. If the query name has not changed (because the intention of the query is the same), repeat steps 5.6–5.8 to export the modified query to the Excel analysis file. It is neither necessary nor recommended that the previously exported data be deleted from the worksheet file first. At step 5.8, after selecting Export , the following dialog box will open.

Select Yes to continue to export the query. The modified query will replace the old query in the Excel analysis file.

The query has been exported to the Excel analysis file onto its own worksheet titled Example_Loggr_Cntrct

. Excel adds the underscores to the title to fill in the spaces in the query name. Close the Access file BMP MIS Field Data.mdb

. 

## Step 5.9. Verify That the New Query Is in the Excel Analysis File Open the Excel analysis file xx new sample unit analysis.xls . Along the bottom of the screen you will see the named worksheet tabs. The new worksheet Example_Loggr_Cntrct will be added as the last worksheet on the right. Scroll to the end of the list of worksheet tabs using the bottom scroll bar or click on the scroll to right end button.

67BMP Manual—Desk Reference Once the new query is in the Excel file, you may analyze the results of the query using Excel formulas and functions.

Scroll to right end button



## Analyzing Queried Data in the Excel Analysis File The Excel analysis file will appear as shown above when opened. As described in chapter 3, it contains several worksheets, with color-coded tabs at the bottom left of the screen, presented in the following order.
• Blue-tabbed worksheets analyze field data.
• Yellow-tabbed worksheets contain charts and graphs created from information in the field data analysis

worksheets.
• White- or gray-tabbed worksheets are copies of the Access queries. The analysis and charts worksheets in the Excel analysis file are protected so that they cannot be modified easily. This prevents the accidental loss of data links to the standard data summaries. There are, however, unprotected worksheets in the Excel analysis file created specifically for your use in analyzing custom queries: Analysis Custom Query1 , Analysis Custom Query2 , Charts Custom Query1 , and Charts Custom Query2 . These worksheets are not protected, so you may add to and modify them as you create custom queries. You will create and save your new custom analyses and charts in these worksheets. Select the worksheet tab Analysis Custom Query1 , which contains a blank worksheet. You may begin analyzing the data resulting from your new query here. Excel has many functions, operators, and tools to use for this purpose. A list of the most useful general functions and operators used in Excel appears in appendix G. 68Chapter 5—Custom Queries and Data Summaries For example, since the BMP protocol consists of primarily multiple-choice questions, the COUNT and COUNTIF functions are useful in counting the number of times a particular answer was chosen for a question. For this example, you will use the new query Example_Loggr_Cntrct to find out the following information regarding the approaches to surface water crossings:
• The number of new sample units where a logger is responsible for BMP implementation by written contract
• The number of these new sample units with a surface water crossing
• The number of opportunities to observe soil conditions at the approaches to surface water crossings on these

sample units
• The number of observations for each of the following soil conditions: stable soil, soil movement, and

sedimentation originating from the approaches to a surface water crossing on these new sample units
• The average volume of measurable sediment in the water body Finally, you will create a chart illustrating the observations of each of these soil conditions on new sample units at the approaches to surface water crossings, and store the chart in the Charts Custom Query1 worksheet. 

## Step 5.10. Set Up the Worksheet Before beginning to analyze the data and write formulas, set up the Analysis Custom Query 1 worksheet. The example below illustrates a worksheet that has been set up to present the information you will retrieve from the data. It includes a title to the section, as well as labels for the information you will isolate from the data. Other tabs in the Excel analysis file provide examples of completed worksheets. Set up your worksheet by inserting labels in cells and deciding which cells will be used to hold the formulas that will show the results of the calculations.

Setting up the worksheet first keeps the information organized and will make it easy to locate information when you are linking it to a new data summary. 69BMP Manual—Desk Reference 

## Step 5.11. Perform the Calculations The results that will appear in your custom worksheet are calculated from the field data using Excel functions and operators. 

### 5.11a. Count the Number of Sample Units in the Query First, you will count the number of new sample units where the logger is responsible for BMP implementation by written contract. Since the query Example_Loggr_Cntrct has isolated all of the sample units that meet the criteria, you simply need to count the number of sample units in this query. The data description is entered in cell A4 in this example. Enter the following formula into cell B4, using the COUNT function: =COUNT(Example_Loggr_Cntrct!A:A) The formula directs Excel to count each cell in column A in the worksheet Example_Loggr_Cntrct

. The formula can be broken down into its individual parts: the equals sign tells Excel that a formula or calculation will follow; COUNT is the function, which counts all occurrences of the criteria specified; the information in parentheses is the criteria. The location of the data (i.e., the worksheet where the data are located) is followed by an exclamation point. You will always need to enter the name of the worksheet where the data are located. Finally, A:A directs Excel to count each row in the first column of the worksheet, column A. Each row corresponds to one sample unit that meets the criteria in the query Example_Loggr_Cntrct

. Press Enter after entering the formula into the cell. In this example, there are 91 sample units that meet the criteria.

70Chapter 5—Custom Queries and Data Summaries 

### 5.11b. Calculate the Number of Sample Units With a Surface Water Crossing Next, you will calculate the number of these new sample units with a surface water crossing. Refer to the question map in appendix E. Question X12 asks for the stream order or water body type at the crossing site. The question map indicates that the answers to question X12 are found in column Q in the query.
Question X12 reads as follows: X12	 Enter the answer code for the stream order or water body type at the crossing site. Although notordinarily considered, if States wish to include ephemeral streams in the protocol, they will be coded zeroorder (answer code 8). 1.

First-order stream 2.

Second-order stream 3. Third-order stream 4.

Fourth-order stream or larger 5.

Drainage ditch systems degrading and/or draining to natural surface waters 6.

Pond or lake of Federal or State significance 7. Wetland or similar area of Federal or State significance 8.

Zero-order (ephemeral stream) 9.

No stream crossing in this sample unit (go to question HB137) 10. Stream crossing avoided by planning (go to question HB137) To find out how many of the new sample units have a surface water crossing, you must find out how many responses to X12 in the query Example_Loggr_Cntrct contain an answer code less than 9 . Total the number of responses that are less than 9 in column Q using the following formula: =SUM(COUNTIF(Example_Loggr_Cntrct!Q:Q,1),COUNTIF(Example_Loggr_Cntrct!Q:Q,2), COUNTIF(Example_Loggr_Cntrct!Q:Q,3),COUNTIF(Example_Loggr_Cntrct!Q:Q,4), COUNTIF(Example_Loggr_Cntrct!Q:Q,5),COUNTIF(Example_Loggr_Cntrct!Q:Q,6), COUNTIF(Example_Loggr_Cntrct!Q:Q,7),COUNTIF(Example_Loggr_Cntrct!Q:Q,8)) 

### Order of Operations Refresher According to the rules of mathematics, mathematical syntax and operators must be placed in the following order: 1. Parentheses 2.

Exponents 3.

Multiplication and division 4.

Addition and subtraction

71BMP Manual—Desk Reference This long formula simply directs Excel to sum the results of each of the COUNTIF statements in the parentheses. Excel follows the order of operations when calculating formulas and will calculate the results of each statement within parentheses before finally summing all of the answers together. The COUNTIF function is used because it counts the number of times a number or text meets a specific condition . In the above formula, it counts the number of times answer choices 1 through 8 occur in the Example_Loggr_Cntrct query. By summing the results of each COUNTIF statement, you have the answer to our question. There are 73 new sample units that meet these criteria.

Important : Zeros are automatically inserted in the spreadsheet in cells where protocol questions have not been answered. Use of the following formula, which counts all responses in column Q that are less than 9, will include any zeros for unanswered questions, producing in an incorrect result

. =COUNTIF(Example_Loggr_Cntrct!Q:Q,′′ <9 ′′ ) 

### 5.11c. Calculate the Number of Opportunities to Observe Soil Conditions Next, you will calculate the number of opportunities to observe soil conditions on the new sample units with a surface water crossing. This number will be important when calculating proportions of observations of soil conditions and causes of sedimentation in this subset of data. The question map in appendix E identifies the questions that assess soil movement or sedimentation for focus areas of the BMP protocol. Since this custom query is intended to deal only with sedimentation from approaches to surface water crossings , it can be determined by reading only those questions that address that issue.

72Chapter 5—Custom Queries and Data Summaries Questions X22, X47, X91, and X116 address soil movement at surface water crossings: X22 Approach Area A—Outside the Buffer/Filter Strip X47 Approach Area A—Inside the Buffer/Filter Strip X91 Approach Area B—Outside the Buffer/Filter Strip X116 Approach Area B—Inside the Buffer/Filter Strip Therefore, there are only four opportunities to observe soil conditions at the approaches to surface water crossings in the BMP protocol. To calculate the total number of opportunities to observe soil conditions at the approaches to the surface water crossing, multiply the total number of new sample units in the Example_Loggr_Cntrct query by 4 using the formula below.

= $ B $ 4*4 Enter this formula in cell E4, immediately to the right of the description you entered in cell D4. This formula refers to cell B4, which contains the number of sample units in the query Example_Loggr_Cntrct

. The $ symbol is an absolute reference. When preceding a column or row reference with a $ symbol, you are instructing Excel to always look for the contents of that column or row. In this case, Excel will always multiply the contents of column B4 by 4 to calculate the total number of opportunities to observe soil conditions at the approaches to the surface water crossing. There are 364 opportunities.

73BMP Manual—Desk Reference 

### 5.11d. Calculate the Number of Observations of Each Soil Condition Questions X22, X47, X91, and X116 are also used to calculate the number of observations of stable soil, soil movement, soil sedimentation (trace and measurable), and the number of sample units without a surface water crossing. As indicated in your worksheet, you will first determine the number of samples in which soil is stable in Approach Areas A and B– Outsidethe Buffer/Filter Strip (questions X22 and X91). Answer code 5 indicates that soil is stabilized: X22	 Enter the answer code that best describes any soil movement in Approach Area A–Outside the Buffer/ Filter Strip. 1. Trace amounts such as films or suspended sediments deposited in the water body or within the bankfull width of the channel (go to question X23) 2.

Measurable amounts of sediment deposited in the water body or within the bankfull width of the channel (go to question X23) 3.

Soil deposited inside the buffer/filter strip but did not reach the water body or within the bankfull width of the channel (go to question X31) 4.

Soil moved in Approach Area A–Outside the Buffer/Filter Strip but did not reach the buffer/filter strip (go to question X37) 5. Soil is stabilized for Approach Area A–Outside the Buffer/Filter Strip (go to question X43)
X91	 Enter the answer code that best describes any soil movement on Approach Area B–Outside the Buffer/ Filter Strip 1. Trace amounts such as films or suspended sediments deposited in the water body or within the bankfull width of the channel (go to question X92) 2.

Measurable amounts of sediment deposited in the water body or within the bankfull width of the channel (go to question X92) 3.

Soil deposited inside the buffer/filter strip but did not reach the water body or within the bankfull width of the channel (go to question X100) 4.

Soil moved in Approach Area B–Outside the Buffer/Filter Strip but did not reach the buffer/filter strip (go to question X106) 5. Soil is stabilized for Approach Area B–Outside the Buffer/Filter Strip (go to question X112) 6.

Soil movement occurs in Approach Area B–Outside the Buffer/Filter Strip but has been recorded elsewhere in the protocol (go to question X113)
Therefore, you are interested in knowing how many times answer code 5 was recorded for questions X22 and X91 in the query Example_Loggr_Cntrct . You must add together the number of times this answer choice occurred for each question to get the total number of times stable soil was observed on Approach Areas A and B–Outside the Buffer/Filter Strip. According to the question map in appendix E, the answers to questions X22 and X91 appear in columns AA and CT in the query worksheet, respectively.

74Chapter 5—Custom Queries and Data Summaries The following formula counts the number of times answer code 5 was chosen for each question and adds them together: =SUM(COUNTIF(Example_Loggr_Cntrct!AA:AA,5), COUNTIF(Example_Loggr_Cntrct!CT:CT,5)) Enter this formula in cell B14, to the right of the cell containing the description. To calculate the proportionof the total number of observations that stable soil conditions were observed at Approach Areas A and B–Outside the Buffer/Filter Strip, simply divide the results of the formula by the total number of observations possible, using the formula below. =B14/ $ B $ 6 B14 is the cell where the number of observations of the condition is calculated and B6 is the cell where the total number of possible observations is calculated. Enter this formula in cell C14.

75BMP Manual—Desk Reference N ote : Cells that c alculate propor tio ns must be f or matted as a percentage . Clic k o n the cell to highlight it, then select F ormat o n the main toolbar and Cellsf ro m the drop-down menu.

The F ormat Cells window will open. On the Number tab , select Percentagein the Category window . S elect z ero (0) as the number of decimal pla ces. S elect OK to apply the f or matting to the selected cell.

76Chapter 5—Custom Queries and Data Summaries The same process should be followed for each of the other soil conditions. The formulas to calculate the number of observations of each condition and its proportion of the total possible number of observations appear in table 5.1. Table 5.1 . Formulas for calculating number of observations of each condition and its proportion of the total. Condition Number of occurrences Proportion of total possible occurrences No surface water crossing* =$B$6-PRODUCT(B5,4) =N14/$B$6 Approaches inside the buffer/filter strip Soil is stable =SUM(COUNTIF(Example_Loggr_Cntrct!AZ:AZ,4), COUNTIF(Example_Loggr_Cntrct!DS:DS,4)) =B15/$B$6

Soil moves =SUM(COUNTIF(Example_Loggr_Cntrct!AZ:AZ,3), COUNTIF(Example_Loggr_Cntrct!AZ:AZ,5), COUNTIF(Example_Loggr_Cntrct!DS:DS,3), COUNTIF(Example_Loggr_Cntrct!DS:DS,5)) =E15/$B$6
Sedimentation (trace) =SUM(COUNTIF(Example_Loggr_Cntrct!AZ:AZ,1), COUNTIF(Example_Loggr_Cntrct!DS:DS,1)) =H15/$B$6 Sedimentation (measurable) =SUM(COUNTIF(Example_Loggr_Cntrct!AZ:AZ,2), COUNTIF(Example_Loggr_Cntrct!DS:DS,2)) =K15/$B$6 Approaches outside the buffer/filter strip Soil is stable =SUM(COUNTIF(Example_Loggr_Cntrct!AA:AA,5), COUNTIF(Example_Loggr_Cntrct!CT:CT,5)) =B14/$B$6

Soil moves =SUM(COUNTIF(Example_Loggr_Cntrct!AA:AA,3), COUNTIF(Example_Loggr_Cntrct!AA:AA,4), COUNTIF(Example_Loggr_Cntrct!AA:AA,6), COUNTIF(Example_Loggr_Cntrct!CT:CT,3), COUNTIF(Example_Loggr_Cntrct!CT:CT,4), COUNTIF(Example_Loggr_Cntrct!CT:CT,6)) =E14/$B$6

Sedimentation (trace) =SUM(COUNTIF(Example_Loggr_Cntrct!AA:AA,1), COUNTIF(Example_Loggr_Cntrct!CT:CT,1)) =H14/$B$6 Sedimentation (measurable) =SUM(COUNTIF(Example_Loggr_Cntrct!AA:AA,2), COUNTIF(Example_Loggr_Cntrct!CT:CT,2)) =K14/$B$6 *The category “no surface water crossing” is required to account for opportunities for observations that will not be observed on sample units without a water crossing so that proportions equal 100 percent. It is a null value. See chapter 4 for a description of null values. 

### 5.11e. Calculate the Average Volume of Measurable Sediment in the Water Body The average volume of measurable sediment in the water body originating from outside and inside the buffer/filter strip can be calculated using an array function. An array functionworks on a range of cells (such as the range between HD2 and HE2000 expressed as HD2:HE2000) and is entered into an Excel worksheet with a Ctrl + Shift + Enter keystroke combination. Brackets {} are automatically placed around the formula after it is entered using this keystroke combination. According to the question map in appendix E, evident sediment volumes originating from the approach areas outside the buffer/filter strip are recorded in questions X26 (column AE in the query) and X95 (column CX). Sediment volumes originating from the approach areas inside the buffer/filter strip are recorded in questions X51 (column BD) and X120 (column DW). Sediment volume entries are duplicated in the database at the end of the protocol questions. Sediment volumes recorded in the BMP protocol are automatically copied to a contiguous block of columns at the end of the protocol in order to make volume calculations easier to write

. 77BMP Manual—Desk Reference According to the question map, columns HD and HE also contain the sediment volumes for the approach areas outside the buffer/filter strip, and columns HG and HH also contain the sediment volumes for the approach areas inside the buffer/filter strip. To calculate the average of sediment evident in the water body originating from the approach area outside the buffer/filter strip, use the AVERAGE function. You need to ignore all zero entries . To do this, use a nested IF THEN statement within the formula to instruct Excel to ignore cells containing zero as an entry. The formula will look like this: =AVERAGE((IF(Example_Loggr_Cntrct!HD2:HE2000>0,Example_Loggr_Cntrct!HD2:HE2000, ′′′′ ))) The formula above contains an array (highlighted in yellow). Enter it in cell B9 using a Ctrl + Shift + Enter keystroke combination. Brackets will automatically be added at the beginning and the end of the formula. While this formula may appear complicated, it makes more sense when you look at the IF THEN statement first: (IF(Example_Loggr_Cntrct!HD2:HE2000>0,Example_Loggr_Cntrct!HD2:HE2000, ′′′′ ) The syntax for an IF THEN statement is IF(condition,[value if true],[value if false]). The statement selects all of the cells on the worksheet Example_Loggr_Cntrct between cells HD2 and HE2000 that are greater than zero. The AVERAGE function is applied to those cells that are greater than zero. The twin quotes ′′′′ instruct Excel to return a blank cell if there are no entries in the range that are greater than zero. ( Note : Since it is not possible to know how many sample units will be sampled by any individual project participant, the cell reference to row 2000 is an arbitrary number, which assumes that any project participant will not sample more than 2,000 units during any one sampling year.)

78Chapter 5—Custom Queries and Data Summaries Remember : Y ou must enter an arr a y f or mula with a Ctrl + S hif t + Enter keystro ke co mbinatio n. N ote the diff erence bet ween the f ollowing t wo examples. In the first screen, the f or mula is entered with o nly an Enter keystro ke , resulting in a #V ALUE error.

In the seco nd screen, the f or mula is entered with a Ctrl + S hif t + Enter keystro ke co mbinatio n. The f or mula c alculates the results a ccur ately .

79BMP Manual—Desk Reference The same syntax can be applied to the formula calculating sediment volumes for the approach areas insidethe buffer/filter strip. The only difference is the cell reference to the appropriate columns. Enter the following formula in cell B10 using a Ctrl + Shift + Enter keystroke: =AVERAGE((IF(Example_Loggr_Cntrct!HG2:HH2000>0,Example_Loggr_Cntrct!HG2:HH2000, ′′′′ ))) 

### 5.11f. Calculate the Soil Conditions Summary Finally, you will use the information about the number of observations of soil conditions to summarize the conditions and create a chart, which you will later use in a custom SDS. The following summary categories appear under the worksheet heading Soil Conditions Summary: Soil Is Stable, Soil Moves (does not reach water body), Sedimentation (trace), Sedimentation (measurable), and No Surface Water Crossing. You have already calculated the results for each condition inside and outside of the buffer/filter strip. Simply total the two results together in the Soil Conditions Summary section. For example, for the Soil Is Stable condition, sum cells B14 and B15 together using the formula =SUM(B14:B15). Place the formula in cell B19.

Sum of stable soil conditions

Repeat this process for each soil condition listed. For No Surface Water Crossing, simply enter the cell location preceded by an equal sign (=N14) since there is only one value.

80Chapter 5—Custom Queries and Data Summaries Calculate the proportion of total observations by dividing each sum by the total number of opportunities to observe soil conditions, which is stored in cell B6. For example, to calculate the proportion of observations in which soil is stable, insert the formula =B19/ $ B6 $ into cell C19. The figures in bold (cells B24 and C24) are totals of the summary columns. Enter =SUM(B19:B23) in cell B24 and =B24/ $ B6 $ in cell C24.

Inexperienced field personnel may inadvertently miss an entry or enter unnecessary additional data, resulting in calculations that do not equal 100 percent. The protocol leader should normally catch errors such as these during the quality control or data cleanup procedures that should be conducted as data are submitted. Small errors may result due to rounding, but larger errors should be resolved by reviewing the field data. In this worksheet, the value in cell B24 should match the value in cell B6, and the value in cell C24 should equal 100 percent. The errors shown occur because zeros were automatically inserted in the data file for two sample units on which question X12 was not answered (see step 5.11b). This is an example of the type of error that can be detected and resolved as described above. Further graphics use the corrected data.

81BMP Manual—Desk Reference 

## Step 5.12. Create a Chart Finally, you will make a simple graph using the Excel Chart Wizard to illustrate the results and save it on the Charts Custom Query1 worksheet. First, select Insert from the main toolbar and Chart from the drop-down menu.

82Chapter 5—Custom Queries and Data Summaries The Chart Wizard will open and guide you through four steps to create a chart. The first screen asks you to choose the type of chart you want. For this example, you will create a column chart. Select Columnunder Standard Types and select Next

.

The next screen will ask for source data. Select the Series tab. Select Add to add a data series.

83BMP Manual—Desk Reference You then need to enter information on the data series. As you enter the information, the image of the chart at the top of the window will change.

First enter the name of the data series. This one will be called Proportion of Soil Conditions at the ApproachesLogger Responsible by Written Contract . Type the name into the Namebox.

T ype the chart name into the Name box.

84Chapter 5—Custom Queries and Data Summaries Next, enter the values in the data series. Click on the icon at the right of the Values box. It will collapse the Source Data window and display the worksheet you just created. You can simply highlight the data series to be included in the chart. In this case, you are choosing the proportions of soil conditions observed at the approach areas to the surface water crossing. When you highlight the data series, the address of the cells chosen are written into the small Chart Source Data – Values box.

Cell references appear in the Chart Sour ce Data – V alues box.

Select these data by highlighting the cells.

85BMP Manual—Desk Reference After highlighting the data series, click on the icon at the right of the Chart Source Data – Values box. It will expand the Source Data window, insert the selected data series in the Valuesbox, and display the data series in the chart.

86Chapter 5—Custom Queries and Data Summaries You must also select the Category (X) axis labels for the bars in the chart. Click on the icon at the right of the Category (X) axis labels box in the Source Data window. Select and highlight the X axis labels in the same way you selected the values for the data series.

Cell references appear in the Source Data box.

Highlighted cells for the X axis labels.

Click the icon again to return to the Source Data box, which will now contain a fairly complete image of the chart.

87BMP Manual—Desk Reference Select Next to advance to the third step in the Chart Wizard, Chart Options . Here you can customize the chart according to your preferences. Select the Titles tab. The chart name you entered in the previous step will appear in the Chart titlebox. You may change the chart title and label the X and Y axes if you so choose.

Select the Gridlines tab. The default setting is to show major gridlines on the Value (Y) axis . If you wish to remove the gridlines, uncheck the box. The change will be reflected in the sample table.

88Chapter 5—Custom Queries and Data Summaries Select the Legend tab. Since there is only one data series on the chart, the legend is not necessary. To remove the legend, uncheck theShow legend box.

Select the Data Labels tab if you would like to label the data series on the chart. Under the Label Contains heading, check the Valuebox. The values of the data series will appear above the columns.

89BMP Manual—Desk Reference Select Next to proceed to the last step—selecting the location of the chart. The first option you will be given is to save the chart as an object in the current worksheet, Analysis Custom Query1

.

This chart should be saved as an object on the worksheet Charts Custom Query1 . Click on the down arrow and select the worksheet name from the list.

90Chapter 5—Custom Queries and Data Summaries Select Finish . The chart will open in the Charts Custom Query1 worksheet.

The completed chart can easily be formatted to make it consistent with other charts in the standard data summaries. For example, all the charts in BMP MIS are formatted to a scale of 100 percent on the Y axis. Change the scale on the new chart by right-clicking on the Y axis. Choose Format Axisfrom the drop-down menu.

91BMP Manual—Desk Reference The Format Axis window will open. To adjust the scale of the axis, select the Scale tab.

Change the Maximumsetting to 1.0 and the Major unit setting to 0.2. When the new values are entered, the boxes to the left of those settings will uncheck, indicating that the auto setting has been overridden.

92Chapter 5—Custom Queries and Data Summaries Select OK to see the chart adjusted to the new scale.

The title of the chart may look better in boldface. To select the chart title for formatting, click on the title to reveal the text box it is in, and right-click in the text box to open a drop-down menu.

93BMP Manual—Desk Reference Select Format Chart Titleto open the Format Chart Titlewindow.

Select the Font tab to select a different font, font style, size, or effect. In this case, select Boldunder the Font style menu.

94Chapter 5—Custom Queries and Data Summaries Select OK to apply the style.

Finally, you may want to highlight conditions of concern (e.g., trace and measurable amounts of sediment observed in the water body). You can easily change the color of individual data points for emphasis. Place your cursor over the data point you wish to change and right-click once. The entire data series becomes highlighted with a small square on each point.

95BMP Manual—Desk Reference Left-click on the data point you wish to format to highlight it. In this example, the column for Sedimentation (trace) is highlighted.

When the individual data point is highlighted, right-click and select Format Data Point from the drop-down menu.

96Chapter 5—Custom Queries and Data Summaries Select the Patterns tab in the Format Data Point window. In the Area section, choose the color for the data point. The color you choose will appear in the Sample box in the lower left-hand corner of the window.

Select OK to apply the color choice to the data point.

97BMP Manual—Desk Reference When finished, save and close the Excel file. There are many ways to format charts in Excel. Formatting choices should make the information presented clear and easy to read. This section presents one way to format the chart, but there are many more options than can be addressed here. In the beginning, trial and error is the best way to see the many options Excel offers for presenting information. 

## Creating a Custom SDS in Word So far you have created a new query in Access, exported it to the Excel analysis file, analyzed data from the new query, and created a chart from the results. If you wish to present your work in new data summary or add it to an existing standard data summary, you need to know how to pull linked data from the Excel analysis file into a Word document. In this section, you will create a simple data summary, which will demonstrate how to link numbers and charts to a Word document. Open the Excel analysis file before working on the new data summary. You need to have access to the information in the Excel file in order to create links in the Word document. 

## Step 5.13. Open and Name the New Word Document Open a blank Word document and select Save asto name the file according to the contents of the report. In this example, the file is named Example Approaches Loggr Cntrct SDS.doc . Save the document in the Sample BMP MIS folder.

When working with actual field data, remember to save the document in the BMP MIS (name) folder where the Excel analysis file is stored.

98Chapter 5—Custom Queries and Data Summaries 

## Step 5.14. Write the Report Write the report as you would any other Word document, keeping in mind where you will locate the linked data. Highlighted areas were inserted in this example report to give some idea of how linked information from the Excel file might be used in a custom SDS. Although the format of the example follows other existing SDSs, you may format your report any way you choose.

99BMP Manual—Desk Reference 

## Step 5.15. Paste a Data Link Into the Word Document Return to the worksheet Analysis Custom Query1 in the Excel analysis file. Right-click on the cell containing the data you wish to copy into the Word document. A drop-down menu will open. In the following example, cell B4, containing the total number of sample units, is selected. Select Copy from the drop-down menu. The cell border will be highlighted with a dashed line.

100Chapter 5—Custom Queries and Data Summaries Return to the Word document and place your cursor in the location where you want the data to appear. Select Edit from the main toolbar and Paste Specialfrom the drop-down menu.

The Paste Special window will open. Word defaults to the Pasteoption. Select the Paste link option on the left side of the window. In the As: box, select Unformatted Text

.

Select Unformatted T

ext Select Paste link

101BMP Manual—Desk Reference By selecting Unformatted Text , only the data in the cell will be pasted into the Word document. You will be able to format the font, font size, and font style to match the rest of the document later. Select OK . The data from cell B4 is inserted into the Word document.

Repeat this process (step 5.15) for each data point you wish to include in the report.

102Chapter 5—Custom Queries and Data Summaries 

## Step 5.16. Paste a Chart Link Into the Word Document Navigate to the worksheet in the Excel analysis file containing the chart you wish to import into the Word document (in this example, it is Charts Custom Query1 ). Right-click on the chart and select Copy from the drop-down menu.

The chart border will be highlighted with a dashed line. Return to the Word document and place your cursor in the location where you want the chart to appear. Select Edit from the main toolbar and Paste Specialfrom the drop-down menu.

103BMP Manual—Desk Reference The Paste Special window will open. Word defaults to the Pasteoption. Select the Paste link option. In the As: box, select Microsoft Office Excel Chart Object

.

Select Paste link

Select Microsoft Office Excel Chart Object

Select OK . The chart will be pasted into the Word document. Repeat this process (step 5.16) for each chart you wish to include in the report.

104Chapter 5—Custom Queries and Data Summaries The report now appears as follows; the yellow highlighted areas represent live links with the Excel analysis file. Save and close the file when finished.

Important : Once you have established links in a Word document, you cannot move data in the source Excel worksheet. If you move data in the Excel worksheet, including adding or deleting rows or columns, the links in the Word document will be lost or different data will be pulled into the Word document inadvertently. Word will only look to the original locationof the data when the link is established. Avoid rearranging the Custom Query worksheets in the Excel file after your data summary is complete. If links are broken or incorrect data is imported to the Word document unintentionally, you must reestablish the links in the Word document, following the instructions included in steps 5.15 and 5.16. 105BMP Manual—Desk Reference 

## Step 5.17. Update the Links You have the option of having the links updated automatically when the Word document is opened, or you may update the links manually. 

### 5.17a. Automatic Update To set your links to update automatically when the Word document is opened, select Toolsfrom the main toolbar and Options from the drop-down menu.

106Chapter 5—Custom Queries and Data Summaries The Options window will open. Select the General tab. Check the box Update automatic links at Open (fifth item down) and select OK

.

Check this box to update links when the file is opened.

Word will prompt you to update the links each time you open the document with the following dialog box. Select Yes ONLY if data have been changed or updated in the Excel analysis file ; otherwise, select No

.

Important : It is not necessary to update links every time the document is opened. Update the links only when data have been changed or updated in the Excel analysis file

.

107BMP Manual—Desk Reference 

### 5.17b. Manual Update If you wish to leave the update automatic links option off, you may still update links manually when working in the Word document. There are two ways to do this. You may edit individual links by right-clicking on the linked data or chart, and selecting the Update Link option from the drop-down menu. When you choose this option, Word simply retrieves the data and updates the selected link. An alternate method is to highlight the linked information and press the F9key.

108Chapter 5—Custom Queries and Data Summaries You may update a single link or multiple links by selecting Edit from the main toolbar and Linksfrom the dropdown menu.

109BMP Manual—Desk Reference The Links window will open displaying a list of all the links in the document and the path to the linked file where the data are stored (the Excel analysis file). The location of the linked data appears in the Item in fileentry. The worksheet name is followed by the cell address (“R” equal row and “C” equals column). To update a single link, highlight it and select the Update Now button.

To update several links at the same time, select the first link and hold down the Shift key and the down arrow key to highlight multiple links. Then select Update Now

. In the example below, the three links are selected for updating.

110Chapter 5—Custom Queries and Data Summaries After updating is complete, select OK to close the Linkswindow and continue working in the Word document. You now have the basic information necessary to create and manage custom queries and data summaries. There are many possibilities for querying BMP protocol data.

Remember : Although you are not required to experiment with the practice files provided in the Sample BMP MIS folder, we recommend you do so to become confident before working with actual field data.


## Removing Worksheet Protections From the Excel Analysis File Because information in the Excel analysis file is linked directly to the SDS templates, the analysis and charts worksheets in the file are protected , meaning you cannot edit or change the worksheet contents or layouts without removing the worksheet protections. The protections are intended to prevent losing the electronic links in the SDS template files or pulling the incorrect information into an SDS file. This section includes instructions for removing the protections in the event that a change must be made on a protected worksheet. However, it is important to understand that deleting cells, rows, or columns, or moving information in a worksheet in the Excel analysis file will result in problems with linked data in other locations. Links in the SDS files can be reestablished following the instructions in steps 5.15 and 5.16. Although it is possible to reestablish links in the SDS file, this process can be time consuming and confusing; therefore, it is not recommended.

Important : It is strongly recommended that you do not remove the worksheet protections. 

## Step 5.18. Remove the Worksheet Protections If you attempt to change information in an analysis or charts worksheet in the Excel analysis file, you will see the following error message.

Select OK if it is necessary to make a change to the worksheet.

111BMP Manual—Desk Reference Select Toolsfrom the main toolbar, and select Protectionand Unprotect Sheet from the drop-down menus.

Note that although the worksheet is protected, it is not password protected . You will not be prompted for a password. Any necessary changes may be made at this time.

112Chapter 5—Custom Queries and Data Summaries 

## Step 5.19. Replace the Worksheet Protections When edits are complete, it is important to replace the worksheet protections. Select Toolsfrom the main toolbar, and select Protectionand Protect Sheet from the drop-down menus.

113BMP Manual—Desk Reference The Protect Sheet window will open. Leave the selections at the default settings and select OK

.

If you choose to add a password in order to unprotect the worksheet, you need to enter that password when attempting to unprotect that worksheet in the future. It is strongly recommended that you do NOT enter a password to protect the worksheet. When changes are made in the Excel analysis file that may affect the links in the SDS templates, it is important to open the appropriate SDS file, update the links, and check the file carefully to be certain all links are correctly maintained. If the links are incorrect, follow the instructions in steps 5.15. and 5.16 to reestablish the links.

114Chapter 6—Performing Spatial Queries 

# Chapter 6—Performing Spatial Queries
When evaluating BMP use and effectiveness, it is sometimes desirable to separate data based on various geographical units. For instance, you may want to evaluate only the BMP data occurring within a specific State, county, or watershed. You may do so by selecting BMP data samples, based on their GPS-derived coordinates. This procedure is known as a spatial query. A Geographic Information System (GIS) may be used to perform custom spatial queries based on the relationships among BMP sample units and the features in other spatial datasets. The resulting data file may then be used with the BMP MIS in creating standard data summaries and a Comprehensive Standard Data Summary for the geographical area selected. The screenbelow shows a sample data file and map for the Chicopee watershed in Massachusetts.

This chapter provides instructions for using the BMP Feature Generator, a query tool created for use in conjunction with ESRI (Environmental Systems Research Institute) ArcMap GIS software. The BMP Feature Generator can be used to create a limited field data table, called a point shapefile, containing the data for sample units within a given geographic area from the records in your Excel field data file, which you created when you uploaded your field data to your desktop computer. For the purposes of this chapter, this file will be referred to as BMP Field Data.xls ; you most likely will have assigned it a different name during the uploading process. A copy of the BMP Feature Generator is included on the CD provided with the BMP desk reference. It must 115BMP Manual—Desk Reference be copied onto your computer’s C: drive (with the path C:\BMP\GIS) in order for it to work correctly. (If you followed the instructions in chapter 3, it should already be filed in the proper location.) Data files created using the BMP Feature Generator should be saved in a project folder (e.g., BMP MIS (name) ) and used to generate SDSs for that project.

Your Excel field data file is used to generate the standard data summaries and is password protected so that it will continue to work properly with the SDS generating files. To use the BMP Feature Generator, you must make an unprotected copy of your Excel field data file ( BMP Field Data.xlsin this example) and save it in the C:\BMP\GIS folder. This chapter demonstrates the procedure for using the BMP Feature Generator to create a spatial query. Data for the Chicopee watershed in Massachusetts was used for instructional purposes, but no practice data file is included in the BMP MIS. You must use your own data to create spatial queries. 

## Step 6.1. Create a .dbf File From the .xls File Navigate to the unprotected copy of your BMP Field Data.xlsfile in the C:\BMP\GIS folder and double-click on the file name to open it. Click on cell A1 in the upper left-hand corner of the worksheet. Hold down the shift key and click on the lowest cell in the last column that contains data, at the extreme lower right of the worksheet (not shown on the screen capture). Check to see that the highlighted portion includes all columns and rows that contain data. Then select the Copy icon and the New icon on the toolbar at the top of the worksheet to open a new worksheet.

New Copy

116Chapter 6—Performing Spatial Queries In the new worksheet, click on cell A1 . Click the down arrow to the right of the Pasteicon on the toolbar at the top of the worksheet. Select Paste Specialfrom the drop-down menu.

117BMP Manual—Desk Reference The Paste Specialwindow will open. Check Column widths and select OK

.

118Chapter 6—Performing Spatial Queries Click the down arrow to the right of the Pasteicon on the toolbar again. Select Paste Specialfrom the dropdown menu. Check Values and number formatsand select OK

.

119BMP Manual—Desk Reference The data from your BMP Field Data.xlswill be copied into the new worksheet in the correct format. Select File from the main toolbar and Save As from the drop-down menu.

120Chapter 6—Performing Spatial Queries In the Save As window, save this interim file to the folder C:\BMP . In the File namebox, enter a file name that describes the data (in this example, it is NA Test_States_Field_Data). In the Save as typebox, select the most current .dbf format from the drop-down menu (in this example, it is DBF 4 (dBASE IV )(*.dbf ) ). Select Save

. Later in the process, you will be instructed to move the final version of this file to a BMP MIS (name) folder, where it can be used to create standard data summaries.

121BMP Manual—Desk Reference Select OK in the window that opens to save the new worksheet as a .dbf file.

122Chapter 6—Performing Spatial Queries Select Yes in the next window that opens. Close the newly created .dbf file and the original Excel (.xls) file by selecting the red X in the upper right-hand corner of each file screen.

Note : The locations of the point features (sample units) in the output shapefile you are creating are based on the first GPS latitude-longitude coordinate pair encountered when reading from left to right in each row in the BMP Field Data.xls file. Latitude-longitude coordinate pairs may occur in columns X14 and X15 (water body crossing Approach Area A), HB138 and HB139 (haul road, log landing, or rutted, mineral soil skid trail inside the buffer/filter strip), CP171 and CP172 (chemical pollutants), or B177 and B178 (buffer/filter strip).

123BMP Manual—Desk Reference 

## Step 6.2. Initiate the BMP Feature Generator Tool Navigate to the GIS folder in C:\BMPand open it. Double-click on the ArcMap project file BMP.mxd(C:\ BMP\GIS\BMP.mxd) to open it. The ArcMap-ArcInfowindow that opens should show base layers for counties, States, and watersheds. The ArcToolbox directory tree on the left-hand side of the screen should display the BMP Feature Generator under BMP Tools

.

Base layers for counties, States, and watersheds

124Chapter 6—Performing Spatial Queries Double-click on the BMP Feature Generator in the ArcToolbox to open the BMP Feature Generator window and click the icon to the right of the top box.

125BMP Manual—Desk Reference In the window that opens, navigate to the file name of the .dbf file that you just created from your BMP Field Data.xls file (in this example, it is NA Test_States_Field_Data.dbf ). Highlight the file name and select Add

.

126Chapter 6—Performing Spatial Queries In the lower box in the BMP Feature Generator window, replace the default file name with the name you wish to assign to the output shapefile. The .shp extension will be added automatically. (In this example, it has been named C:\BMP\BMP Features .) Select OK

.

127BMP Manual—Desk Reference A window will open to indicate progress. When the tool executes successfully, a point shapefile depicting the locations of your BMP sample points will be displayed. The name of the shapefile you wish to create will be added to the ArcMap Layers window with the file name you assigned ( BMP Features ).

128Chapter 6—Performing Spatial Queries 

## Step 6.3. View the Result and the Types of Information Available Right-click on BMP Features under Layers and select Zoom To Layer from the drop-down menu. The map shown will zoom to only the States containing BMP sample points contained in the source point shapefile (in this example, NA Test_States_Field_Data.dbf ).

Alternatively, you may choose to zoom in manually using the Zoomicon on the toolbar.

You may also find the Identify tool useful for selecting and viewing the attributes of features from any of the datasets.

129BMP Manual—Desk Reference You can control which of the layers’ features are selected for identification by choosing from the drop-down list at the top of the Identify Results window, which opens when you click on a sample point using the Identify tool. <Top-most layer> is the default; it shows the BMP sample data for the point selected. The map of sample units can be captured by pressing the Print Screenkey. It can then be pasted into other products, such as PowerPoint presentations, BMP reports, or other publications.

130Chapter 6—Performing Spatial Queries You can view information on the watershed in which a specific data point occurs by clicking on the sample point. Then click on the down arrow to the right of the Layers box in the Identify Resultswindow and select Watersheds . After viewing the information specific to the data point, return the Layers box to the default setting, <Top-most layer>

.



## Step 6.4. Select Sample Data From Specific Geographic Areas You can perform spatial queries based on the relationships among the BMP sample units and the features in other spatial datasets by using the Select By Locationutility in ArcMap. GIS datasets depicting shapes of the various watersheds are contained in files already programmed into the BMP Feature Generator and correspond to the U.S. Geological Survey eight-digit hydrologic unit codes (HUC). Standard GIS datasets for county and State boundaries are also incorporated into the tool. These datasets are automatically added to the ArcMap view when the BMP.mxdproject file is opened.

131BMP Manual—Desk Reference Select the Selectionicon on the main toolbar and Select By Attributes from the drop-down menu.

132Chapter 6—Performing Spatial Queries The Select By Attributes window will open. Click the drop-down arrow to the right of the Layer box and select the name of the layer you wish to use, in this example Watersheds . In the Methodbox, select Create a new selectionfrom the drop-down list. In the third box, scroll down and click on HUC_NAME . Click on the “ = ” button and the Get Unique Valuesbutton. In the fourth box, scroll down and then double-click on Chicopee

. The selection statement in the last box should now read ′′ HUC_NAME ′′ = ′ Chicopee ′ . Select Apply at the bottom of the window, and then select OK

.

133BMP Manual—Desk Reference The Select By Attributes window will close and the selected watershed will now be highlighted in light blue.

Note that several counties or watersheds could have been included in the data file being created by using the “OR” button in the selection statement in the Select By Attributes window. For example, the selection statement

′′ HUC_NAME ′′ = ′ Chicopee ′ OR ′′ HUC_NAME ′′ = ′ Blackstone ′ would yield a file containing sample data from both the Chicopee and Blackstone watersheds.

134Chapter 6—Performing Spatial Queries Select Selectionfrom the main toolbar and Select By Locationfrom the drop-down menu. The Select By Locationwindow will open. In the I want tobox, choose select features fromfrom the drop-down menu. In the the following layers box, click on the check box next to the layer you created (in this example, BMP Features ). In the that box, choose are completely withinfrom the drop-down menu. In the the features in this layer box, select the Watersheds from the drop-down menu. Check the box next to Use selected features . Select Apply and then Close . The BMP features that fall within the Chicopee watershed will now be selected and highlighted in light blue.

135BMP Manual—Desk Reference 

## Step 6.5. Copy the Selected Data to a .dbf File To save the selected records to a .dbf file that can then be opened in Access, where additional queries can be made, open the attribute table of the BMP data layer by right-clicking on the layer name under Layers(in this example, BMP Features ), and selecting Open Attribute Tablefrom the drop-down menu. The Selected Attributes window will open. Click the Selectedbutton at the bottom of the window so that only those records that are selected are shown. Then select the Options button and select Export from the drop-down menu.

136Chapter 6—Performing Spatial Queries The Export Data window will open. In the Export box, select Selected recordsfrom the drop-down menu. In the Output tablebox, type the name you wish to assign to the data file or use the icon to the right of the box to navigate to the file location and type the name. (In this example it has been named C:\BMP\Chicopee_ Watershed_Sample_Data.dbf because it contains only the BMP samples from the Chicopee watershed.) Select OK

.

137BMP Manual—Desk Reference In the ArcMapwindow that opens, select No

.

The file will be saved in the folder you designated. 

## Step 6.6. Convert the .dbf File to an .xls File Navigate to the folder containing the newly created .dbf file (in this example, C:\BMP\Chicopee_Watershed_ Sample_Data.dbf ), double-click the file name to open it, and, using the Save as function, save it as an Excel (.xls) file in a separate BMP MIS (name) folder (for example, BMP MIS 07 Chicopee ). It may then be used to create standard data summaries and a Comprehensive Standard Data Summary for the Chicopee watershed using the BMP MIS procedure described in chapter 3.

138Chapter 7—Sampling Design 

# Chapter 7—Sampling Design This chapter provides guidance on sampling design for program managers and field foresters using the BMP protocol. State forestry agencies and other forest management organizations often have statisticians on staff and are encouraged to develop their own sampling strategy. Therefore, this chapter will be limited to suggestions intended to help overcome some of the sample design problems encountered during the testing phase of the protocol. The authors give special thanks to Jeffrey Gove and Harry Valentine of the U.S. Forest Service, Northern Research Station for their assistance in the preparation of this chapter. 

## Timber Sales and Sample Units Personnel, legal, and resource constraints render a complete assessment of all timber sale operations beyond the capabilities of most State forestry agencies and forest management organizations. As a result, it is highly unlikely that all timber sales can be visited; the assessment of BMP effectiveness must rely on a sample of timber sales. Although the BMP protocol records conditions found on discrete spatial components of timber sales called sample units (figure 7.1), the sample unit boundaries can be determined only by onsite inspection. The cost of onsite inspections makes it unlikely that the total number of potential sample units will be determined or that a full statistical analysis will be undertaken. Therefore, it is important that a representative and unbiased sample of logging sites be drawn to ensure that the results of the BMP monitoring protocol are conservative and provide accurate information to forest managers and regulating agencies, and to an increasingly concerned public, many of whom are unfamiliar with the principles of sound forest resource management. 

## Sample Unit Identification Each timber sale is considered a sample site, containing a number of potential sample units. The sampling process begins with the compilation of a list of timber sales occurring within a given time period, usually 1 year. A random selection of timber sales, or sample sites, will be drawn from all of the timber sales in a State or other administrative unit, and one or more randomly chosen sample units within the selected timber sales will be measured based on the sample design. Detailed instructions on the identification of sample unit boundaries are provided in the BMP field guide.

139BMP Manual—Desk Reference

>

> > > > > >

S ample unit 1 S ample unit 2 S ample unit 7 S ample unit 3 S ample unit 4 S ample unit 5 S ample unit 6 Haul r oad cr ossing S k id tr ail cr ossing Haul r oad in buff er/filt er str ip B uff er/filt er str ip R idgeline Highw a y Haul r oad S k id tr ail S tr eam B r idge C ulv er t Landing S ample unit b oundar y Wetland >

Figure 7.1. Sample units within a timber sale (sample site), as delineated by timber cutting boundaries, ownership boundaries, and water body crossing, are available for random selection as potential sample units. 

## Sample Design Using Probability Sampling Probability sampling includes random sampling methods in which the probability or chance of selecting a given sample site is known in advance. If a given sample site can only be chosen once, the method is described as sampling without replacement. If a given sample site is returned to the list and thus has the potential to be chosen for sampling more than once, the method is known as sampling with replacement. 

## Random Sampling Without Replacement Random sampling without replacement, also know as simple random sampling, is one of the simplest forms of probability sampling. It is appropriate in situations where the timber sales from which the sample is to be drawn have relatively similar characteristics. Every potential sampling site has an equal opportunity to be chosen once for measurement. As a result, it is a simple, reliable, and efficient method of choosing an unbiased sample.

140Chapter 7—Sampling Design In this method, a comprehensive list of all of the timber sales occurring within the area and timeframe to be sampled is developed from whatever data or information is available. The timber sales to be sampled are then selected at random from the list. One way to select the sites to be sampled is to list the timber sales in any order and number them sequentially. Multiply the total number of timber sales by a randomly generated decimal and select the sale whose number is generated. Repeat the procedure until the desired number of sample sites has been selected. The random number generator feature in Excel can be used for this process. The screen below shows the procedure for selecting 10 sample sites from a list of 129 timber sales. Open a blank Excel worksheet. Enter the function =RAND() in cell A2. Enter the number 129 , the total number of timber sales, in cell B2. Enter the formula =A2*B2in cell C2. Copy each entry into rows 3 through 11 of the same column. Format column C as a number with zero decimal places. The numbers in column C are generated by the formula and represent the timber sale numbers selected to be sampled. In this method, each timber sale is sampled only once. If a selected sample site is drawn again, the subsequent draw is discarded and an additional random draw is made until the desired total number of sample sites is drawn.

Another advantage of this method is that it represents a single random sample of the entire population of timber sales. Statistically sound subsets of the data can then be created to assess trends and other information. 

## Random Sampling With Replacement and Varying Probability In random sampling with replacement and varying probability, also known as list sampling, the probability of a sample being selected increases with certain chosen characteristics (in this case, the acreage of the timber sale). Replacement means that a given site, once selected as a sample, remains in the list of potential sample sites and may be sampled more than once. Therefore, the larger the timber sale area, the more likely it is to be sampled and to be sampled more than once.

141BMP Manual—Desk Reference While only slightly more complicated than random sampling without replacement, random sampling with replacement and varying probability can be used to draw a single random sample while balancing the ratio of sample acreage to total harvest acreage where timber sales vary widely in total acreage. Statistically sound subsets of the data can then be created to assess trends and other information. In a situation where the potential sample sites include a small number of large timber sales and a large number of smaller timber sales, this method can be used to obtain a more representative sample of conditions over the combined area of all the timber sales without resorting to stratification. The process for selecting sample sites using this method involves listing the timber sales and calculating a cumulative total of each successive timber sale acreage. An acreage range is calculated for each timber sale based on the cumulative total and the size of the individual timber sale. A random acreage is generated between 1 and the total cumulative number of acres. This number will fall between the upper and lower acreage limit of one of the timber sales, designating it as a sale to be sampled. Thus the probability of being chosen for sampling increases with the size of the individual sale, and an individual sale may be chosen more than once.

142Chapter 7—Sampling Design The following screens demonstrate the process of selecting 7 sample sites from a list of 22 timber sales. Open a blank Excel worksheet. In column A, list the timber sale numbers. In column B, list the acreage of each timber sale. In column C, you will calculate the cumulative total of the acreage of the timber sales, resulting in a total acreage of all timber sales. In cell C2, enter the formula =B2 . In cell C3, enter the formula =C2+B3 . Copy this formula into the remaining cells in column C. In column D, you will calculate the lower limit of the acreage range of the timber sale in the same row. In cell D2, enter the number 1 . In cell D3, enter the formula =C2+1 . Copy this formula into the remaining cells in column D.

143BMP Manual—Desk Reference The random acreages are calculated using the Excel random number generator. Open a second Excel worksheet. Enter the function =RAND() in cell A2. Enter the number 2770 , the total timber sale acreage (calculated in the previous worksheet), in cell B2. Enter the formula =A2*B2in cell C2. Copy each entry into rows 3 through 8 of the same column. Format column C as a number with zero decimal places. The numbers in column C are generated by the formula.

144Chapter 7—Sampling Design A timber sale is chosen and sampled as often as one of the random acreages calculated in column C of the previous worksheet falls within the cumulative acreage range shown in columns C and D of the following worksheet. Enter the randomly generated acreages into the appropriate row in column E of the following worksheet. In this method, a given timber sale, large or small, may be selected for sampling any number of times.

As a result of this method, two samples are drawn in sale area 13 and several samples are selected from smaller sales. Thus the balance in the ratio of sample acreage to timber sale acreage between the many small timber sales and the fewer larger timber sales is improved without introducing bias and without stratifying the sample. As in random sampling without replacement, an advantage of this method is that it represents a single random sample of the entire population of timber sales. Statistically sound subsets of the data can then be created to assess trends and other information. 

## Random Sampling With Stratification Sampling with stratification is somewhat more complicated and generally requires a greater number of samples. As a result, it is usually somewhat less efficient in terms of travel and staff time.

145BMP Manual—Desk Reference The decision to stratify a population, that is, dividing it into relatively homogeneous groups before samples are drawn, should only be done if a large variation in timber sale characteristics is clearly evident and would obscure important patterns and trends if ignored. For example, if a State contains a sector with very rough, mountainous terrain and another sector with very flat, wet terrain, two strata are more likely to provide an accurate picture of conditions than would a simple random sample drawn from across the entire State. Stratification, however, will likely require a larger sample size than other methods since an adequate number of samples (> 30) must be measured in each stratum, or group. If, however, the variation in timber sales was substantial but could not be reliably isolated by stratification, then stratifying the population could be arbitrary and subjective, and lead to bias. Under these circumstances, random sampling without replacement or random sampling with replacement and varying probability might be more appropriate and yield more defensible results. 

## Sample Size Maximizing the number of sample units is recommended since the predictive power of statistical analyses is directly proportional to sample size. Regardless of the sampling method chosen, a sufficient number of samples (≥ 30) must be obtained to avoid the statistical accuracy limitations inherent in a small sample. It is logical to assume that sample size should be increased as the total number of timber sales increases. Otherwise, the proportion or percentage of the population represented by the sample will be inadequate. The variability of timber sales is another consideration. If early samples exhibit a wide variation in BMP implementation and performance, you may want to increase the sample size in order to accurately describe conditions statewide. Unfortunately, there are no rules of thumb for these adjustments in sample size. In most cases, sample size is limited by the resources that can be devoted to the project and, as a result, sample size usually falls short of the optimal number. Do your best with the resources available. 

## Examples From Protocol Testing Very few States have a single database of all sites where timber sales occurred on an annual basis. As a result, the States that tested the BMP protocol used a variety of methods to identify an adequate number of timber sales as sample sites (table 7.1). Tax records, cutting plans or permits, soil and erosion control plans, highway entry permits, and forest management plans were all used as sources of timber sale data from which to draw a sample. In one case where no records existed, aerial reconnaissance was used to locate timber sales. Even after initial selection, some sample sites were removed from consideration because private landowners were unwilling to permit access. This introduces the possibility that landowners with concerns about the conduct of harvesting operations on their lands will deny access for monitoring, while confident landowners would permit monitoring, thus producing a favorable bias in the statewide results. One way to overcome this problem might be to grant landowners immunity from prosecution and fines for violations discovered during the sampling process if the problems are corrected within a reasonable timeframe. In light of practical realities, some of the sampling and management strategies summarized in table 7.1 would need to be adjusted in order to be consistent with rigorous statistical theory and methods of avoiding bias.

146Chapter 7—Sampling Design Table 7.1. Summary of sampling and management strategies used by States and organizations in the BMP protocol pilot test. State or organization Method Pros Cons

DE Samples were drawn by taking every fifth site from a list of erosion and sediment control permits. Sampling was assigned to State forestry personnel closest to the sites. Comprehensive statewide list focused on nonpoint source pollution.

IN Five counties were drawn at random. Timber harvesting operations within the counties were located by aerial reconnaissance. Samples were collected on sites where permission to enter was granted. Random sample of counties; census of timber harvesting operations. Access denied by some private landowners.

MA Sample included the first 80 sites in a random sort of the chapter 132 database for a 1-year period. Comprehensive statewide database.

MD Samples were drawn for each physiographic provinces (shore, Piedmont, mountains) from a 3-year list of harvest permit notifications. First drawn were all sites with crossings. Stratification to account for substantial physiographic and operational differences. Focusing on sites with crossings makes it less likely that stable sites will be sampled.

ME The State was divided into three regions with three landowner size categories in each. A random sample was drawn from Forest Operations Notification forms. Where landowner permission was granted, one sample was recorded on each of the small and medium landowner category operations and two on each of the large landowner category operations. Sampling was assigned to State forestry personnel closest to the sites. Stratification to account for substantial physiographic and operational differences. Perhaps too complex and stratified, with an arbitrary number of sample units on each site. Selection or omission of some samples to distribute workload.

NH A total of 300 sites were drawn at random from intent to cut forms. Additional samples were drawn at random from Wetland Notification forms that included a stream crossing. All sites for which permission to enter was granted were sampled. Sampling was assigned to extension forestry personnel closest to the sites. A large random sample from reliable statewide lists. Access denied by some landowners.

New York City

watershed area Sites with crossings were solicited by letter from consulting foresters, State service foresters, and loggers. Additional sites were solicited from landowners attending forestry workshops. All sites for which permission was received were sampled. Willingness to test protocol without any possibility of a convenient list from which to sample. Less likely that poor sites will arise fromsolicitation of aboveaverage participants.

VT A sample unit was recorded on each Use Value Program parcel scheduled for review that included a timber sale, as well as on each State land timber sale. Sampling was assigned to State forestry personnel closest to the sites. Subset of the population of timber harvesting operations statewide; allows evaluation of Use Value Program parcels and State lands in detail. Sample and subsequent data analysis limited to Use Value Program parcels and State lands.

WV A random sample was drawn from notification of intent to cut forms for a 1-year period. Sampling was assigned to State forestry personnel closest to the sites. A large random sample from reliable statewide lists.

147BMP Manual—Desk Reference 

## Importance of Monitoring Over Time One of the many reasons for consistency in sampling over time is the ability to detect and assess change. The importance of consistency in sampling and attention to detail can be seen in valuable, well-documented GIS and forest inventory databases. To realize the full value of the BMP protocol, States and organizations should repeat measurements at regular intervals, generally 1 to 5 years. Regular and consistent monitoring is important for two reasons. First, if the sampling design is changed in subsequent years, the ability to compare the datasets, detect patterns and trends, and guide management efforts may be compromised. Second, if resources are allocated piecemeal with only a few samples taken every year versus, for example, 100 samples at 3-year intervals, it will probably be more difficult to gauge or detect changes associated with performance, training, or regulation. Consider, for example, the range of variation in weather conditions during a 5-year period and its potential influence on BMP performance. Add changes in such things as equipment type, stumpage prices, and fuel prices, and the merits of a larger sample in one year on a regular interval become more apparent.


## Recommendations If the BMP protocol is to be successful in replacing older, subjective compliance measures as well as generating long-term support and credibility with the EPA, State regulatory agencies, nongovernmental organizations, and the public, it will need to produce straightforward and defensible results. To that end, conscientious sample design must precede the collection of field data. Adequate time and effort must be devoted to the task of balancing statistical principles with the practical realities of field and analytical work to ensure this goal is achieved. In addition to meaningful and useful assessments of State or organizational BMP programs, the ability to generate a regional overview to guide program development, information exchange, and technology transfer will be a valuable outcome. Through it all, learn from experience, seek improvement, and be conservative when interpreting and applying monitoring results. Keep the following recommendations in mind: 1.

Clearly and accurately list all of the timber sales to be considered. 2.

Draw a sample from the list using a random process, such as one of the methods described here or one

designed by statisticians from your organization. 3.

Draw a sample of sufficient size (≥ 30), making it as large as possible given personnel and resource limitations. In summary, a sample of timber harvesting operations for the BMP protocol should be drawn using sound sampling procedures from the most accurate and comprehensive list of timber sales that can be assembled to avoid the risk of bias.

148Chapter 8—Quality Control 

# Chapter 8—Quality Control
Quality control, in addition to sample design, is a significant factor in determining the value of data collected through a BMP monitoring program. Accuracy, precision, and verifiability of field data depend upon the care and consistency with which the field data are collected. Developing and maintaining high standards of quality requires
• Comprehensive training of field monitors, and
• Rigorous adherence to field procedures. Regardless of sample design, errors in measurement or interpretation made in the collection of field data can inject bias, making it difficult to draw valid conclusions from the data. These errors have a direct bearing on the confidence that can be placed in the results of the BMP monitoring program and on the usefulness of the data to managers and regulators. Quality control, therefore, is a valuable and essential investment rather than a task that may be considered optional or unnecessary. 

## BMP Protocol Quality Control Principles Responsibility for the quality control program must be assigned to an individual, preferably the State BMP Protocol Project Coordinator or project leader, early in the BMP monitoring program. The assigned individual should then be provided with the authority and resources necessary to organize and conduct the quality control program according to the principles outlined below.
• Ensure that the State coordinator or monitoring project leader has a thorough knowledge of the BMP

monitoring protocol field procedures and BMP MIS.
• Establish required standards of accuracy and review them with field crews, emphasizing and reinforcing the

importance accuracy and precision.
• Provide comprehensive training to all who will be involved with field data collection. Resist the temptation to

provide just enough training to get people started, assuming that they will “figure it out as they go along” in the field. This approach fails to validate the effectiveness of the training and incurs the risk of changing standards and interpretation during the sampling process.
• Have field crews work in teams to increase the accuracy and consistency of BMP protocol application.
• Make a random selection of not less than 10 percent of the new sample plots early in the field data collection

phaseand remeasure them as quality control samples to detect and correct discrepancies in a timely manner.
• Repeat or refresh training on an annual basis or another appropriate interval based on sampling frequency.
• Verify or delete suspect data from the sample rather than risking contamination of the overall project results. Quality control data collection and analysis should be completed as a part of each round of BMP monitoring in order to systematically compare data from new sample units with data from their respective quality control samples. This analysis helps determine how well different individuals independently evaluate the same site and can help to clearly identify the specific protocol questions that may require clarification or additional training for field personnel. The results of this quality control analysis should be documented and reported as part of the monitoring results.

149BMP Manual—Desk Reference 

## Quality Control Results During Pilot Testing Data quality is considered good when data recorded during a quality control review matches the data originally recorded by the field crew. Results for the pilot testing of the BMP protocol (2002–2006) showed that the quality control data directly matched the original sample data approximately 80 percent of the time when sample sites were visited independently within a short timeframe (table 8.1). This average of 80 percent accuracy is expected to improve as familiarity, training, and experience with the protocol increases.

Table 8.1. Number of quality control samples and percentage of questions with matching answers for States and organizations participating in the BMP protocol pilot test.

State or organization Number of quality control plots Percentage of questions with matching answers Indiana 0 N/A Maine 11 68 Maryland 6 78 Massachusetts 11 82 New Hampshire 4 81 New York City Watershed 9 78 Vermont 7 75 West Virginia 9 95 

## Quality Control Analysis Reviewing quality control data against original field data by checking the answers to all 197 protocol questions in each sample is a time-consuming task. Some of the answers to protocol questions are very similar and call for a degree of judgment on the part of the reviewer to determine if the original and quality control data match. A significant number of protocol answers, however, may be verified by simple comparison of numerical data. An Excel worksheet has been developed to facilitate this process (see next section for detailed instructions on using the worksheet). For most of the questions evaluated in the quality control analysis worksheet, the original field data and quality control data must match exactly. But for few of the questions, a tolerance level has been set to allow very minor differences to be recorded as a match. For example, if field personnel reported 125 feet as the total length of a ditch in answer to question X24 and a quality control reviewer recorded 132 feet, the answers would be considered a match because the difference is less than the 10 percent tolerance level set for that question. Because erosion can lengthen rills and ditches over time, a tolerance level of 10 percent was used in the quality control analysis worksheet for linear measurements recorded in questions X24, X49, X93, X118, HB148, B186, B187, B188. Since some movement is often required for satellite reception, a tolerance level of .003 degrees was used for the GPS coordinates recorded in questions X14, X15, HB138, HB139, CP171, CP172, B177, and B178.

150Chapter 8—Quality Control The following questions are included in the quality control analysis worksheet. The first appearance of a question number is in bold; subsequent appearances of the same question follow in normal type. X12 X32 , X57, X101, X126, HB156 CP169 X13 , HB140, B173 X38 , X107 B179 X14 , HB138, CP171, B177 X71 B181 X15 , HB139, CP172, B178 X73 B186 X22 , X47, X78, X91, X116, HB146 X74 B187 X23 , X48, X80, X92, X117, HB147 X75 B188 X24 , X49, X93, X118, HB148 X76 B190 X28 , X34, X40, X53, X59, X83, X97, X103, X109, X122, X128, HB152, HB158 X77 HB137 B191 W192 HB168 W196 

## Quality Control Analysis Worksheet The quality control analysis worksheet file, quality_control_worksheet.xls , has been included on the CD accompanying this desk reference. This file is designed to work in conjunction with your original field data file and your quality control data file. Navigate to the quality_control_worksheet.xlsfile and open it in Excel. The file has been password protected to prevent accidental alteration of the cell formulas. To unprotect the worksheet, select Toolsfrom the main toolbar, and Protection and Unprotect Sheet from the drop-down menus. In the window that opens, type in the password “quality control” (in lower case). Make a copy of the worksheet under a different file name by selecting Filefrom the main toolbar and Save As from the drop-down menu. Give the newly created file a name that identifies the project and date of the data (e.g., qc (project name and date).xls ). Return to the quality_control_worksheet.xlsfile and restore the password protection by selecting Tools , Protection , and Protect Sheet . Type in a password you can remember. Close the quality_control_worksheet.xls file. You will copy rows of data from your original field data file and your quality control data file into the quality control analysis worksheet in pairs of rows, for example, placing original field data in row 2 and the corresponding quality control data in row 3. Original field data will be placed in rows 2, 8, 14, 20, 26, 32, 38, 44, and 50 and the respective quality control data in rows 3, 9, 15, 21, 27, 33, 39, 45, and 51. It is important to place the original field data and the quality control data in the correct rows in the quality control analysis worksheet in order for the formulas to work correctly. The worksheet will accommodate nine rows of original data along with nine rows of corresponding quality control data. Navigate to and open your original field data file and the corresponding quality control data file. Open your newly created qc (project name and date).xls file. In your original field data file, click on the row number at the left end of the row containing the data to copy (e.g., row 2). Select the Copy icon on the main toolbar. Go to the qc (project name and date).xls file. Click on the row number at the left end of the row in which you wish to place the data and select the Pasteicon. Continue filling the worksheet in this manner, first with the original field data file and then the quality control data file. Make sure to place the original field data in the correct rows with the respective quality control data in the rows immediately below.

151BMP Manual—Desk Reference The worksheet will place a TRUE or FALSE in the row below the data in the column of each question checked, indicating whether or not the data matched. The worksheet will then make the necessary calculations to indicate the percentage of questions that have matching answers. The State coordinator or project leader can then use the results to assess the quality of data collection and the question areas in which additional training or clarification is needed.

152Chapter 9—Recordkeeping and Data Security 

# Chapter 9—Recordkeeping and Data Security
Data gathered using the BMP protocol is used to evaluate the effectiveness of BMP implementation for your State or organization. Careful recordkeeping and the security of field data are essential to the integrity of any such project. The following are suggestions for managing records and securing data. 

## Recordkeeping

• Create a separate copy of the BMP MIS folder for each monitoring project undertaken. Give the folder a

unique name that represents the geographic area and the year in which the data were collected (e.g., BMP MIS Allagash 2007). Save all field data and analysis files in this folder. It is important to keep all related data and reports for each project in the same unique folder for two reasons: (1) The BMP MIS will assign all field data files the same file name in order for the Access queries to work properly (see chapter 3). Keeping the field data files in their appropriate folder will ensure that the data remains accurate. (2) All files must be in the same folder in order to maintain the links between the source data and the data summaries.
• Ensure that the original BMP MIS folder and any subsequent BMP MIS project folders you create are part of

a regular backup of your computer system. If you use a tape backup system in your office, be sure that all BMP MIS project folders are part of that scheduled backup.
• Copy the original BMP MIS folder onto a CD and store it in a safe place offsite. Information Technology

professionals suggest keeping a copy of all important data offsite to would allow for data recovery in the event of a fire, flood, or other destructive event at the office.
• When you complete a Comprehensive Standard Data Summary, open the document file and break the links

with the Excel analysis file. This will reduce the size of the file, making it easier to e-mail the file and requiring less memory to store it. Opening the Comprehensive Standard Data Summary and breaking the links from within it does not affect the links associated with other documents. 

## Data Security Data security is essential to the reliability of the BMP protocol field data and data summaries. The integrity and defensibility of field data is the most important reason to take precautions and protect the security of your data.
• Allow only one person to correct errors. The fewer the people with the authority to make changes to protocol

data, the more secure the data.
• Install and run the BMP MIS on a password-protected computer system. Require users to log into Windows

with a password to gain access to the drive where the BMP MIS directories and files are located.
• Leave the default BMP MIS protections in place. The Access database as well as the Excel analysis file and Excel

charts worksheets are password protected. Do not remove these protections. They guard against unauthorized access to data, and, in the case of Excel, protect the user from inadvertently losing data links.

153Appendix A—Abbreviations 

# Appendix A—Abbreviations The following abbreviations are used in naming queries and standard data summaries in the BMP MIS.

Abbreviation Definition 3mo three months 3Pty third party Agrmnt agreement Ain Approach Area A–Inside the Buffer/Filter Strip Aout Approach Area A–Outside the Buffer/Filter Strip Appr approach(es) Assnd assigned BCCT bridge culvert closed top BCOT bridge culvert open top Bin Approach Area B–Inside the Buffer/Filter Strip BMP best management practices Bnkfl bankfull Bout Approach Area B–Outside the Buffer/Filter Strip Buff buffer/filter strip Cntrct contract CP chemical pollutants CP Spill chemical spill Ephmrl ephemeral Equ equal Extr Wthr extreme weather Frstr forester Grtr greater Grtr Equ greater than or equal to HR haul road IF improved ford Indst industrial Intrmtnt intermittent LL log landing Loggr logger MC multiple culverts Mvt movement N new NIPF non-industrial private forest A-1BMP Manual—Desk Reference Opn open PBF pole/brush ford Prnl perennial Rip Bffr riparian buffer SC single culvert Scour scouring Sed sediment or sedimentation Skd Trl skid trail Specs specifications SR (crossing) structure removed Stbl stable Ste Req state requirement Str Rmvd opening spec undetermined due to structure removal SU sample unit Surf surface UF unimproved ford UO unknown/other WB water body Wdth width WL wetland WL X wetland crossing Wthr weather Wtr water XStr crossing structure

A-2Appendix B—Queries 

# Appendix B—Queries The following queries have been defined in the BMP MIS.

Query name Description N 3Pty New sample units where forestry activity is certified by third-party agency N 3Pty Mvt Appr New sample units with surface water body concerns and observations of soil movement at the approaches; forest activity regulated by third-party certifying agency N 3Pty Mvt XStr New sample units with observation of soil movement from the crossing structure; forestry activity is certified by third-party agency N 3Pty Sed WB Appr New sample units with observations of sediment delivery to awater body from approaches; forestry activity is certified by a thirdparty agency N 3Pty Sed WB XStr New sample units with evidence of sediment delivery to the water body from the crossing structure; forestry activity is certified by third-party agency N 3Pty Stbl Appr New sample units with a surface water crossing and soil is stabilized at the approaches; third-party certifier is responsible for forest activity N 3Pty Stbl XStr New sample units with observations of stable soil at the crossing structure; forestry activity is certified by third-party agency N BMP Not Assnd New sample units where responsibility for BMP implementation has not been assigned N Buff Bet 25 and 50 New sample units with a recommended buffer/filter strip width between 25 and 49 feet N Buff Grtr Equ 50 New sample units with a recommended buffer/filter strip width greater than or equal to 50 feet N CP Refuse New sample units with observations of logging refuse N CP Spill New sample units with observations of chemical spill N CP Spill Refuse New sample units with evidence of both chemical spillage and pollutant containers N CP WB New sample units where chemical pollutants reached a water body N Ephmrl WB New sample units; crossing is ephemeral stream N Extr Wthr New sample units where an extreme weather-related event influenced results of evaluation N Extr Wthr Mvt Appr New sample units with observations of soil movement from the approaches and extreme weather occurrence likely to influence results of the sample
B-1BMP Manual—Desk Reference N Extr Wthr Mvt XStr New sample units with observations of soil movement from the crossing structure and extreme weather likely to influence results of the sample N Extr Wthr Sed WB Appr New sample units with observations of sediment delivery to a water body from approaches and extreme weather likely to influence results of the sample N Extr Wthr Sed WB XStr New sample units with observations of sediment delivery to the water body from the crossing structure and extreme weather likely to influence results of the sample N Extr Wthr Stbl Appr New sample units where soil is stabilized at the approaches and extreme weather likely to influence results of the sample N Extr Wthr Stbl XStr New samples where soil is stable at the crossing structure and extreme weather likely to influence results of the sample N Frstr Cntrct New sample units where the forester is responsible for BMP implementation by written contract N Frstr Cntrct Mvt Appr New sample units with observations of soil movement from the approaches; forester is responsible for BMP implementation by written contract N Frstr Cntrct Mvt XStr New sample units with observations of soil movement from the crossing structure; forester is responsible for BMP implementation by written contract N Frstr Cntrct Sed WB Appr New sample units with observations of sediment delivered to a water body from approaches; forester is responsible for BMP implementation by written contract N Frstr Cntrct Sed WB XStr New sample units with observations of sediment delivery to a water body from the crossing structure; forester is responsible for BMP implementation by written contract N Frstr Cntrct Stbl Appr New sample units with soil stabilized at the approaches; forester is responsible for BMP implementation by written contract N Frstr Cntrct Stbl XStr New sample units with soil stabilized at the crossing structure; forester is responsible for BMP implementation by written contract N HR New sample units with a haul road associated with the crossing structure N HR Buff New sample units with a haul road in the buffer/filter strip; the haul road is NOT associated with another water body crossing N HRLL Buff New sample units with a haul road or log landing in the buffer/ filter strip; the haul road or log landing is NOT associated with another water body crossing N HRLL Buff Grtr Equ 50 New sample units with a haul road or log landing in the buffer/ filter strip; the recommended buffer/filter strip width is greater than or equal to 50 feet N Indst New sample units belonging to an industrial forest landowner
B-2Appendix B—Queries N Indst Mvt Appr New sample units with observations of soil movement at the approaches; industrial ownerships N Indst Mvt XStr New sample units with observations of soil movement from the crossing structure; industrial ownerships N Indst Sed WB Appr New sample units with observations of sediment delivered to the water body from the approaches; industrial ownerships N Indst Sed WB XStr New sample units with observations of sediment delivery to a water body from the crossing structure; industrial ownerships N Indst Stbl Appr New sample units where soil is stabilized at the approaches; industrial ownerships N Indst Stbl XStr New sample units where soil is stabilized at the crossing structure; industrial ownerships N Intrmtnt WB New sample units; crossing is intermittent stream or vernal pool N LL Buff New sample units with a log landing in the buffer/filter strip; the log landing is NOT associated with another water body crossing N Loggr Agrmnt New sample units where a logger is responsible for BMP implementation by oral agreement N Loggr Cntrct New sample units where a logger is responsible for BMP implementation by written contract N Mvt New sample units with observations of soil movement from the approaches, the crossing structure, a haul road or log landing in the buffer/filter strip, OR a wetland crossing N Mvt Appr New sample units with observations of soil movement from the approaches N Mvt XStr New sample units with observations of soil movement from the crossing structure N NIPF New sample units that are non-industrial private forest ownerships N NIPF Mvt Appr New sample units with observations of soil movement at the approaches; non-industrial private ownerships N NIPF Mvt XStr New sample units with observations of soil movement at the crossing structure; non-industrial private ownerships N NIPF Sed WB Appr New sample units with observations of sediment delivered to the water body from the approaches; non-industrial private ownerships N NIPF Sed WB XStr New sample units with observations of sediment delivery to a water body from the crossing structure; non-industrial private ownerships N NIPF Stbl Appr New sample units where soil is stabilized at the approaches; nonindustrial private ownerships N NIPF Stbl XStr New sample units where soil is stabilized at the crossing structure; non-industrial private ownerships

B-3BMP Manual—Desk Reference N No HR Buff New sample units with surface water crossings and no haul road or log landing in the buffer/filter strip N No Mvt Appr New samples with NO observations of soil movement or sediment delivered from the approaches; soil is stable at the approaches N No Mvt Sed New sample units with NO observations of soil movement or sediment delivered to a water body from the approaches, crossing structure, haul road or log landing in the buffer/filter strip, OR wetland crossing; soil is stable for all protocol questions N No Surf Wtr New sample units with no surface water crossings N No Surf Wtr HRLL Buff New sample units with no surface water crossing and a haul road or log landing in the buffer/filter strip N No Surf Wtr No HR Buff New sample units with no surface water concerns and no haul road in the buffer/filter strip N Prnl WB New sample units; crossing is perennial stream, lake, or wetland N Sed CP WB/WL New sample units with observations of sediment or chemical pollutants reaching a water body or wetland N Sed WB New sample units with observations of sediment delivered to a water body from the approaches, the crossing structure, a haul road or log landing in the buffer/filter strip, OR a wetland N Sed WB Ain New sample units with observations of sediment delivered to a water body from Approach Area A–Inside the Buffer/Filter Strip N Sed WB Aout New sample units with observations of sediment delivered to a water body from Approach Area A–Outside the Buffer/Filter Strip N Sed WB Aout/BCCT New sample units with observations of sediment delivered to a water body from Approach Area A–Outside the Buffer/Filter Strip; crossing structure is a bridge or box culvert with a closed top N Sed WB Aout/BCOT New sample units with observations of sediment delivered to a water body from Approach Area A–Outside the Buffer/Filter Strip; crossing structure is a bridge or box culvert with an open top N Sed WB Aout/IF New sample units with observations of sediment delivered to a water body from Approach Area A–Outside the Buffer/Filter Strip; crossing structure is an improved ford N Sed WB Aout/MC New sample units with observations of sediment delivered to a water body from Approach Area A–Outside the Buffer/Filter Strip; crossing structure is multiple culverts N Sed WB Aout/PBF New sample units with observations of sediment delivered to a water body from Approach Area A–Outside the Buffer/Filter Strip; crossing structure is a pole/brush ford N Sed WB Aout/SC New sample units with observations of sediment delivered to a water body from Approach Area A–Outside the Buffer/Filter Strip; crossing structure is a single culvert

B-4Appendix B—Queries N Sed WB Aout/SR New sample units with observations of sediment delivered to a water body from Approach Area A–Outside the Buffer/Filter Strip; crossing structure removed N Sed WB Aout/UF New sample units with observations of sediment delivered to a water body from Approach Area A–Outside the Buffer/Filter Strip; crossing structure is an unimproved ford N Sed WB Aout/UO New sample units with observations of sediment delivered to a water body from Approach Area A–Outside the Buffer/Filter Strip; crossing structure is unknown/other N Sed WB Appr New sample units with observations of sediment delivery to a water body from the approaches to the surface water crossing N Sed WB Appr Xstr New sample units with observations of sediment delivered to a water body from the approaches or from the crossing structure N Sed WB Bin New sample units with observations of sediment delivered to a water body from Approach Area B–Inside the Buffer/Filter Strip N Sed WB Bout New sample units with observations of sediment delivered to a water body from Approach Area B–Outside the Buffer/Filter Strip N Sed WB Bout/BCCT New sample units with observations of sediment delivered to a water body from Approach Area B–Outside the Buffer/Filter Strip; crossing structure is a bridge or box culvert with a closed top N Sed WB Bout/BCOT New sample units with observations of sediment delivered to a water body from Approach Area B–Outside the Buffer/Filter Strip; crossing structure is a bridge or box culvert with an open top N Sed WB Bout/IF New sample units with observations of sediment delivered to a water body from Approach Area B–Outside the Buffer/Filter Strip; crossing structure is an improved ford N Sed WB Bout/MC New sample units with observations of sediment delivered to a water body from Approach Area B–Outside the Buffer/Filter Strip; crossing structure is multiple culverts N Sed WB Bout/PBF New sample units with observations of sediment delivered to a water body from Approach Area B–Outside the Buffer/Filter Strip; crossing structure is a pole/brush ford N Sed WB Bout/SC New sample units with observations of sediment delivered to a water body from Approach Area B–Outside the Buffer/Filter Strip; crossing structure is a single culvert N Sed WB Bout/SR New sample units with observations of sediment delivered to a water body from Approach Area B–Outside the Buffer/Filter Strip; crossing structure removed N Sed WB Bout/UF New sample units with observations of sediment delivered to a water body from Approach Area B–Outside the Buffer/Filter Strip; crossing structure is an unimproved ford

B-5BMP Manual—Desk Reference N Sed WB Bout/UO New sample units with observations of sediment delivered to a water body from Approach Area B–Outside the Buffer/Filter Strip; crossing structure is unknown/other N Sed WB In New sample units with observations of sediment delivered to a water body from the approaches inside the buffer/filter strip N Sed WB Out New sample units with observations of sediment delivered to a water body from approaches outside the buffer/filter strip N Sed WB XStr New samples with evidence of sediment delivery to a water body from the crossing structure N Skd Trl New sample units with skid trails N Stbl New sample units with observations of stabilized soil at the approaches, the crossing structure, the haul road or log landing in the buffer/filter strip, OR the wetland crossing N Stbl Appr New sample units with observations of soil stabilized at the approaches N Stbl Appr XStr New sample units with observations of stable soil at the approaches or the crossing structure N Stbl XStr New sample units with observations of stable soil at the crossing structure N Surf Wtr New sample units with surface water crossings N WB Rip Bffr New sample units with perennial stream or lake, intermittent stream or vernal pool, or ephemeral stream adjacent to the buffer/ filter strip N WL X New sample units with a wetland crossing N XStr BCCT New sample units with a bridge or box culvert with a closed top as crossing structure N XStr BCOT New sample units with a bridge or box culvert with an open top as crossing structure N Xstr Grtr 3mo New sample units with crossing structure to be in place greater than 3 months; fish or macroinvertebrates are present N XStr IF New sample units with an improved ford as crossing structure N Xstr Less 3mo New sample units with crossing structure to be in place less than 3 months; fish or macroinvertebrates are present N XStr MC New sample units with multiple culverts as crossing structure N Xstr No Scour New sample units with no evidence of stream down cutting or scouring within 100 feet of the outlet end of the crossing structure N Xstr No Ste Req New sample units with no State requirements for size of crossing structure opening N Xstr Opn Grtr Bnkfl Wdth New sample units with a crossing structure opening equal to or greater than pre-structure bankfull channel width
B-6Appendix B—Queries N Xstr Opn Less Bnkfl Wdth New sample units with a crossing structure opening less than prestructure bankfull channel width N Xstr Opn Ste Req no New sample units; size of crossing structure does not meet State requirements N Xstr Opn Ste Req yes New sample units; size of crossing structure meets State requirements N XStr PBF New sample units, crossing structure is a pole/brush ford N XStr SC New sample units, crossing structure is a single culvert N Xstr Scour New sample units with evidence of stream down cutting or scouring within 100 feet of the outlet end of the structure N XStr SR New sample units and crossing structure has been removed N Xstr Str Rmvd New sample units; unable to determine if crossing structure opening meets State requirements because structure removed N XStr UF New sample units; crossing structure is unimproved ford N XStr UO New sample units and crossing structure is unknown or other New Samples New sample units

B-7Appendix C—Standard Data Summaries 

# Appendix C—Standard Data Summaries Standard data summaries (SDSs) can be used alone or combined into a Comprehensive Standard Data Summary. Each Comprehensive Standard Data Summary should begin with the document Comprehensive_Report_ Cover_and_Introduction.doc . The following is the recommended sequence for arranging the SDSs in the Comprehensive Standard Data Summary. A description of each SDS is also provided. 

## Overview Information Name Description New SU General Information SDS.doc Introductory information on the BMP protocol and summary data on the general information feature New Sample Unit SDS.doc Summary data on new sample units New SU Soil Mvt Sed Stabl SDS.doc Summary data on soil movement, sedimentation, and stabilization on new sample units 

## Data Summaries

Name Description New SU Approaches SDS.doc Summary data on the approaches to surface water crossings on new sample units New SU Crossing Structure SDS.doc Summary data on the crossing structure feature on new sample units New SU Crossing Structure Specs SDS.doc Summary data on crossing structure specifications on new sample units New SU Fish Passage SDS.doc Summary data on fish passage questions on new sample units New SU Soil Mvt Buffer SDS.doc Summary data on soil movement within the buffer/filter strip on new sample units New SU HRLL in Buffer SDS.doc Summary data on haul roads and/or log landings in the buffer/ filter strip on new sample units New SU Riparian Area SDS.doc Summary data on the riparian area feature on new sample units New SU Chemical Pollution SDS.doc Summary data on chemical pollution on new sample units New SU Wetland SDS.doc Summary data on wetland crossings on new sample units New SU Approaches BMP Not Assnd SDS.doc Summary data on the approaches where responsibility for BMP implementation has not been assigned as part of the timber sale agreement on new sample units New SU Approaches Frstr Cntrct SDS.doc Summary data on the approaches where a forester has been assigned responsibility for BMP implementation as part of the timber sale agreement on new sample units New SU Approaches Loggr Cntrct SDS.doc Summary data on the approaches where a logger has been assigned responsibility for BMP implementation as part of the timber sale agreement on new sample units C-1Appendix D—Example Comprehensive Standard Data Summary 

# Appendix D—Example Comprehensive Standard Data Summary See chapter 4 for the steps to follow in creating a Comprehensive Standard Data Summary using your data. 

# Best Management Practices (BMP) Monitoring Protocol—Data Summary <Insert report title, including the area investigated, the timeframe, and the name of the person compiling the report> 

# Example

D-1BMP Manual—Desk Reference 

# Example The data in this document were generated using the procedures outlined in the two volumes of the Best Management Practices (BMP) Monitoring Manual: Implementation and Effectiveness for Protection of Water Resources : Field Guide(NA–FR–02–06) Desk Reference(NA–FR–02–07)
Both documents were published by: United States Department of Agriculture Forest Service Northeastern Area State and Private Forestry 11 Campus Boulevard, Suite 200 Newtown Square, PA 19073

D-2Appendix D—Example Comprehensive Standard Data Summary 

# Example Introduction The best management practices (BMP) protocol provides an efficient, economical, standardized, and repeatable BMP monitoring process that is automated from data gathering through the generation of a standard data summary. It uses commonly available software and inexpensive field data recording devices. It is compatible with existing State BMP programs and is available for use by forestry agencies, forest industry, and green certification organizations. Further information, manuals, software programs, and training in the protocol procedures and report generation can be obtained from David Welsch or Albert Todd, U.S. Forest Service, Northeastern Area State and Private Forestry, Watershed Team. 

# Background The BMP protocol project is a cooperative effort of the Forest Service, U.S. Department of Agriculture, and the Northeastern Area Association of State Foresters–Water Resources Committee (NAASF–WRC), Steven Koehn, Maryland State Forester, chairperson. The project has been funded by grants from the U.S. Forest Service and the U.S. Environmental Protection Agency (EPA). The original concept and question sequence was developed by Roger Ryder and Tim Post of the Maine Forest Service in collaboration with David Welsch and Albert Todd of the U.S. Forest Service, Northeastern Area State and Private Forestry (NA S&PF). The NA S&PF proposed the method to the NAASF–WRC and the EPA for development as a potential regional protocol. After the withdrawal of the Maine Forest Service, David Welsch served as the project coordinator through the development, testing, and implementation of the project. State forestry agencies from Delaware, Indiana, Maine, Maryland, Massachusetts, New Hampshire, New York, Ohio, Pennsylvania, Vermont, Virginia, West Virginia, and Wisconsin; the New York City Watershed Agricultural Council Forestry Program; and the U.S. Forest Service Northern Research Station and NA S&PF have collaborated in the development and testing of the BMP protocol.

1
D-3BMP Manual—Desk Reference 

# Example Data Summary The information in this data summary was compiled from a sample data set using measurements from 309 sample units. The data summary is a computer-generated set of graphs and charts summarizing the sample unit data in a standardized format to facilitate comparison with data collected from other times and differing geographical areas. Each sample unit contains the potential for approximately 200 observations and includes a number of observations of some types of data. Proportions presented in the charts and graphs in the standard data summaries are based on the total number of possibilities for a condition to occur. Null observations are included in the calculations to ensure that the proportions total 100 percent and the frequency of problems is accurately reported. The data collection procedure is described in the U.S. Forest Service publication Best Management Practices (BMP) Monitoring Manual—Field Guide: Implementation and Effectiveness for Protection of Water Resources (NA–FR–02–06), which includes the question set and instructions for making and recording the observations. Diagrams and definitions are also included. Data summary generation, quality control, risk analysis, and statistical sample design information are described in Best Management Practices (BMP) Monitoring Manual— Desk Reference: Implementation and Effectiveness for Protection of Water Resources (NA–FR–02–07).

2
D-4Appendix D—Example Comprehensive Standard Data Summary 

# Example Contents

General Information Feature

............................................................................................ 1 Overview of Sample Units

................................................................................................ 3 Soil Movement, Sedimentation, and Stabilization

............................................................ 5 Approaches to the Water Crossing

................................................................................... 7 Crossing Structure

............................................................................................................ 9 Crossing Structure Specifications

................................................................................... 11 Fish Passage

.................................................................................................................. 13 Soil Movement Through the Buffer/Filter Strip

............................................................... 15 Haul Road or Log Landing in the Buffer/Filter Strip

........................................................ 17 Riparian Area Analysis

.................................................................................................... 19 Chemical Pollutants

........................................................................................................ 23 Wetland Crossings

......................................................................................................... 25 Responsibility for BMP Implementation Not Assigned

.................................................... 27 Forester Is Responsible for BMP Implementation by Written Contract

.......................... 29 Logger Is Responsible for BMP Implementation by Written Contract

............................ 31

3 D-5BMP Manual—Desk Reference 

# Example General Information Feature This report presents the results of data gathered for the BMP protocol project on new sample units for the State of XX

.  A total of 309 new sample units were sampled. Number of Samples Taken by Year Year of Sample Number of Samples 2003 0 2004 177 2005 132 2006 0 2007 0 2008 0 2009 0 Map of Monitored Area With Sample Units <OPTION: User may insert figure showing monitored area and sample unit locations.>

4
D-6Appendix D—Example Comprehensive Standard Data Summary



### Ownership Category

Figure 1. (n=309) Pr oportion of Sample Units by Ownership Category Non-industrial private forest Industrial ownership State or other govt. forest Land trust or similar ownership Unknown Nonforest developer owned Investor owned 0% 0% 0% 0% 1% 22% 9% 67%

20% 40% 60% 80% 100%



# Example

5
D-7BMP Manual—Desk Reference 

# Example Overview of Sample Units 309 new sample units were sampled. Number and Proportion of Sample Units by Feature A sample unit is likely to have more than one of activity or condition recorded; therefore, the total of activities and conditions will exceed the number of sample units (e.g., 493 activities and conditions in 309 sample units).


## Number of New Sample Units by Feature

Wetland Chemical spill No surface crossing=72

Riparian Haul road area=156 crossing=66

Haul road in the buffer=10 crossing=157 cr ossing=28 or p ollutan t c on tainers=4 w a t er

S k idder

Figure 2. (n=309)

6
D-80% 22% Appendix D—Example Comprehensive Standard Data Summary

Figure 3. Sample units may qualify as more than one type; therefore, percentages may total more than 100 (n=309)

.


### Discussion <OPTION: User may insert analysis, interpretation, and photographs here.> Pr oportion of New Sample Units by Featur e
Wetland crossing Chemical spill or pollutant containers Riparian area Haul road in the buffer/filter strip Skidder crossings Haul road crossings No surface water crossing 0% 23% 21% 1%

3% 9%

50%

51%

20% 40% 60% 80% 100%



# Example

7
D-9BMP Manual—Desk Reference 

# Example Soil Movement, Sedimentation, and Stabilization There are 5 opportunities to observe the occurrence of soil movement, sedimentation, or stabilization for each sample unit. They are at Approach Area A–Outside the Buffer/ Filter Strip, Approach Area A–Inside the Buffer/Filter Strip, the crossing structure, Approach Area B–Inside the Buffer/Filter Strip, and Approach Area B–Outside the Buffer/Filter Strip. Proportions in this section are based on the total number of opportunities to make observations about soil conditions. For the 309 new sample units, there are 1545 opportunities to observe soil conditions.

100%

80%
60%
40% 43%
16% 7% 10% 27% 20%

0% Soil stable Sedimentation (trace) No surface water crossing Soil moves (does not

reach water) Sedimentation (measurable) Observations of Soil Stabilization, Movement, and Sedimentation

Figure 4. Proportions are based on the total number of opportunities to observe soil conditions in the protocol (n=1545)

.

8
D-10Appendix D—Example Comprehensive Standard Data Summary 

# Example Table 1. Quantities of Measurable Sedimentation by Area of Origin (cubic feet) Average Median Maximum All areas Rill or gully 101 16 1485 Sediment evident in the water body 35 5 606
Approaches Outside the Buffer/Filter Strip Rill or gully 183 60 1485 Sediment evident in the water body 11 5 40
Approaches Inside the Buffer/Filter Strip Rill or gully 72 7 1250 Sediment evident in the water body 25 6 270
Crossing structures Rill or gully N/A N/A N/A Sediment evident in the water body 49 5 606 Nonnumeric values indicate that no volume measurements were recorded. Note: Rill and gully volumes are measurements of the volume displaced from the rill or gully and may be larger than the volume actually entering the bankfull channel. Sediment evident in the water body is a measure of the sediment attributable to the logging activity and present in the channel when the observation is made; it cannot account for sediment washed away prior to observation. Thus, there is a high probability that the actual volume of sediment reaching the bankfull channel of the water body is between these two estimates. 

### Discussion <OPTION: User may insert analysis, interpretation, and photographs here.>

9
D-11BMP Manual—Desk Reference 

# Example Approaches to the Water Crossing
There are 4 opportunities to observe the occurrence of soil movement, sedimentation, or stabilization from the approaches to a surface water crossing. They are at Approach Area A–Outside the Buffer/Filter Strip, Approach Area A–Inside the Buffer/Filter Strip, Approach Area B–Inside the Buffer/Filter Strip, and Approach Area B–Outside the Buffer/ Filter Strip. Proportions are based on the total number of opportunities to make observations about soil conditions at the approaches. For the 309 new sample units, there are 1236 opportunities to observe soil conditions.

100%

80%
60%
40% 43%
19% 7% 6% 27% 20%

0% Soil stable Sedimentation (trace) No surface water crossing Soil moves (does not

reach water) Sedimentation (measurable) Observations of Soil Stabilization, Movement, and Sedimentation at the Appr oaches

Figure 5. Proportions are based on the total number of opportunities to observe soil conditions at the approaches (n=1236)

.


### Discussion <OPTION: User may insert analysis, interpretation, and photographs here.>

10
D-12Appendix D—Example Comprehensive Standard Data Summary 

# Example Sediment From the Approaches There are 83 observations of trace amounts of sediment reaching the surface water body or deposited within bankfull channel width of the water feature. There are 77observations of measurable amounts of sediment reaching the surface water body or deposited within bankfull channel width of the water feature. The following table compares volumes of measurable amounts of sediment. Table 2. Volume of Sedimentation (cubic feet) 

### Approaches Outside the Buffer/Filter Strip Approaches Inside the Buffer/ Filter Strip Rill or gully Sediment evident in water body Rill or gully Sediment evident in water body Average 183 11 72 25 Median 60 5 7 6 Maximum 1485 40 1250 270 Table reflects the average, median, and maximum of sediment volumes 1 cubic foot or greater. Nonnumeric values indicate that no volume measurements were recorded. Note: Rill and gully volumes are estimates of the volume displaced and may be larger than the volume actually entering the bankfull channel. Sediment evident in the water body is a measure of the sediment attributable to the logging activity and present in the channel at the point in time at which the observation is made; it cannot account for sediment washed away prior to observation. Thus, there is a high probability that the actual volume of sediment reaching the bankfull channel of the water body is between these two estimates.

11
D-13BMP Manual—Desk Reference 

# Example Crossing Structure There is 1 opportunity to observe the occurrence of soil movement, sedimentation, or stabilization from the crossing structure. Proportions are based on the total number of opportunities to make observations about soil conditions at the crossing structure. For the 309 new sample units, there are 309 opportunities to observe soil conditions at the crossing structure.

100%

80%
60%
40% 42%

2% 6% 26% 27% 20%

0% Soil stable Sedimentation (trace) No surface water crossing Soil moves (does not

reach water) Sedimentation (measurable) Observations of Soil Stabilization, Movement, and Sedimentation fr om the Cr ossing Structure

Figure 6. Proportions are based on the total number of opportunities to observe soil conditions at the crossing structure (n=309)

. 

### Discussion <OPTION: User may insert analysis, interpretation, photographs here.> Sedimentation From the Crossing Structure There are 19 observations of trace amounts of sediment reaching the surface water body or deposited within bankfull channel width of the water feature. There are 80observations of measurable amounts of sediment reaching the surface water body or deposited within bankfull channel width of the water feature. 12
D-14Appendix D—Example Comprehensive Standard Data Summary 

# Example
Table 3: Volume of Measurable Sediment Observed in the Water and Attributable to the Crossing Structure (cubic feet) 

### Sediment evident in water body Average 

### 49 Median 5 Maximum 606 Nonnumeric values indicate that no volume measurements were recorded.

Structure T ype Associated with Sedimentation

Pole or brush ford Improved or constructed ford Unimproved ford

Multiple culverts Single culvert
Bridge or box culvert with closed top Bridge or box culvert with open planked top Crossing structure removed

Soil stable Unknown/other
Soil moves (does not reach water body) No surface water crossing 0% 1%

3%

5% 6%

20% 40% 60% 80% 100% 27% 2% 3%

7% 6%

42% 2% 1%

Figure 7. (n=309)

13 D-15BMP Manual—Desk Reference 

# Example Crossing Structure Specifications
A total of 309 new sample units were sampled.  226 sample units have surface water crossings.

Crossing Structur e T ypes

Pole or brush ford Improved or constructed ford Unimproved ford

Multiple culverts Single culvert
Bridge or box culvert with closed top Bridge or box culvert with open planked top Crossing structure removed Unknown/other No surface water crossing 0% 13% 12% 20%

20% 40% 60% 80% 100% 27% 3% 7% 5%

9%

2% 2%

Figure 8. Proportions are based on the total possible number of crossing structures (n=309)

. 

### Structure Type by Road Type  There are 157sample units with a skid trail at the water crossing.  There are 66 sample units with a haul road at the water crossing.

14
D-16Appendix D—Example Comprehensive Standard Data Summary



### The following charts compare crossing structure types by road type at the water crossing.

Figure 9. Proportions are based on the total possible number of crossing structures (n=309)

.

Figure 10. Proportions are based on the total possible number of crossing structures (n=309)

. Structur e T ype Associated with Skidder Cr ossing

Pole or brush ford Improved or constructed ford Unimproved ford

Multiple culverts Single culvert
Bridge or box culvert with closed top Bridge or box culvert with open planked top Crossing structure removed Unknown/other
No surface water crossing Haul road crossing

0% 11% 12% 7%

20% 40% 60% 80% 100% 27% 20% 1%

3% 4%

7%

2% 0%

Structur e T ype Associated with Haul Road Cr ossing

Pole or brush ford Improved or constructed ford Unimproved ford

Multiple culverts Single culvert
Bridge or box culvert with closed top Bridge or box culvert with open planked top Crossing structure removed Unknown/other
No surface water crossing Skid trail crossing

0% 1% 0% 11%

20% 40% 60% 80% 100% 27% 49% 2%

4% 0% 0%

0% 1% 

# Example

15
D-17BMP Manual—Desk Reference 

# Example Fish Passage Presence of Fish and Macro-Invertebrates

Presence of Fish and Macro-Invertebrates
100%

80%
60%
40% 16% 36%

7% 7% 34%
20%

0% N e i t h e r fi s h o r m a c r o i n ve rt e b r a t e s F i s h a n d m a c r o i n v e rt e b r a t e s p r e s e n t a n d N o s u rf a c e w a t e r c r o s s i n g F i s h a n d m a c r o i n ve rt e b r a t e s p r e s e n t a n d U n k n o w n present structure structure in place in place more than less than 3 months 3 months
Figure 11. (n=309)

16
D-18Appendix D—Example Comprehensive Standard Data Summary 

# Example
Streambed Conditions When Fish and Macro-Invertebrates Are Present and Crossing Structure Is in Place More Than 3 Months
Crossing Structure Bottom and Stream Substrate

streambed substrate is substrate is not perched outlet present and continuous continuous in bottom of in bottom of structure

structure 100%

80%
60%
40% 39%

4% 22% 0% 35%
20%

0% O p e n b ott o m ; o p e n t o n a t u r a l C l o s e d b ott o m ; n a t u r a l N o s u rf a c e w a t e r c r o s s i n g C l o s e d b ott o m ; n a t u r a l C l o s e d b ott o m st r u ct u r e ;

Figure 12. (n=309)


### Discussion <OPTION: User may insert analysis, interpretation, and photographs here.>

17
D-19BMP Manual—Desk Reference 

# Example Soil Movement Through the Buffer/Filter Strip (Soil Did Not Reach Surface Water Body) Buffer/Filter Strip Width Is Between 25 and 49 Feet Sample units in this section have a buffer/filter strip width between 25 and 49 feet.  16 sample units have a buffer/filter strip width between 25 and 49 feet. There are 2 opportunities to observe the occurrence of soil movement through the buffer/filter strip. They are at Approach Area A–Inside the Buffer/Filter Strip and Approach Area B–Inside the Buffer/Filter Strip. Proportions are based on the total number of opportunities to make observations about soil conditions at the approaches inside the buffer/filter strip. For the 16 new sample units, there are 32 opportunities to observe soil movement through the buffer/filter strip.  There are 6 observations of soil movement through the buffer/filter strip that did not reach the surface water body.

Pr oportion of Occurr ences of Soil Movement Thr ough the Buffer/Filter Strip T owar d the Water Body (Buffer/filter strip width is between 25 and 49 feet)
Soil moves inside the buffer/filter strip (does not reach water) Trace and measurable soil in water Soil moves (did not reach the buffer/filter strip) Soil stable No surface water crossing 0% 0% 44% 34%
0% 19%

20% 40% 60% 80% 100%

Figure 13. (n=32) 18
D-20Appendix D—Example Comprehensive Standard Data Summary 

# Example Buffer/Filter Strip Width Is Greater Than or Equal to 50 Feet Sample units in this section have a recommended buffer/filter strip width greater than or equal to 50 feet.  207sample units have a recommended buffer/filter strip width greater than or equal to 50 feet. There are 2 opportunities to observe the occurrence of soil movement through the buffer/filter strip. They are at Approach Area A–Inside the Buffer/Filter Strip and Approach Area B–Inside the Buffer/Filter Strip. Proportions are based on the total number of opportunities to make observations about soil conditions at the approaches inside the buffer/filter strip. For the 207new sample units, there are 414 opportunities to observe soil movement through the buffer/filter strip.  There are 90 observations of soil movement through the buffer/filter strip that did not reach the surface water body.

Pr oportion of Occurrences of Soil Movement Thr ough the Buffer/Filter Strip T owar d the Water Body (Buffer/filter strip width is greater than or equal to 50 feet)
Soil moves inside the buffer/filter strip (does not reach water) Trace and measurable soil in water Soil moves (did not reach the buffer/filter strip)
Soil stable No surface water crossing 0% 0% 28% 41%

3% 19%

20% 40% 60% 80% 100%

Figure 14. (n=414) 19
D-21BMP Manual—Desk Reference 

# Example Haul Road or Log Landing in the Buffer/Filter Strip There is 1 opportunity to observe the occurrence of soil movement, sedimentation, or stabilization from the haul road or log landing inside the buffer/filter strip. Proportions are based on the total number of opportunities to make observations about soil conditions at the haul road or log landing inside the buffer/filter strip. For the 309 new sample units, there are 309 opportunities to observe soil conditions at the haul road or log landing inside the buffer/filter strip.  10 sample units have a haul road or log landing located within the buffer/filter strip. Soil Stabilization, Movement, and Sedimentation

100%

80%
60%
40%

0% 0% 0% 3% 20%

0% S e d i m e n t a t i o n ( t r a c e ) S e d i m e n t m ov e s ( d o e s n ot

r e a c h w a t e r b o d y) N o h a u l r o a d o r l o g l a n d i n g i n s i d e t h e b u f f e r / fi l t e r st r i p S e d i m e n t a t i o n ( m e a s u r a b l e ) S o i l st a b l e Observations of Soil Stabilization, Movement, and Sedimentation Fr om the Haul Road or Log Landing Inside the Buffer/Filter Strip 95%

Figure 15. Proportions are based on the total number of opportunities to make observations about soil conditions at haul roads or log landings inside the buffer/filter strip (n=309)

. 

### Discussion <OPTION: User may insert analysis, interpretation, photographs here.> 20
D-22Appendix D—Example Comprehensive Standard Data Summary 

# Example Sedimentation From the Haul Road or Log Landing in the Buffer/Filter Strip There are 1 observations of trace amounts of sediment reaching the surface water body or deposited within bankfull width of the channel. There are 0 observations of measurable amounts of sediment reaching the surface water body or deposited within bankfull width of the channel. Soil Type in the Buffer/Filter Strip Where Sedimentation Was Observed
Pr eponderant Soil T ype of Sedimentation Deliver ed to Within Bankfull Channel Width

Silt or loam Clay Organic material

Gravel Sandy

Cobble and larger

Soil moves (does not reach water body) Soil stable
No haul road or log landing in the buffer/filter strip 0% 20% 40% 60% 80% 100% 3% 0% 0% 0% 0% 0% 0% 0%

95%

Figure 16. (n=309) 

### Table 4. Sediment Volumes (cubic feet) Calculated volume of soil removed from rill or gully terminating within bankfull channel width Sediment evident in water body or within bankfull channel width Average 6 36 Median 6 36 Maximum 6 36 Nonnumeric values indicate that no volume measurements were recorded. 21
D-23BMP Manual—Desk Reference 

# Example Riparian Area Analysis A total of 156 sample units have a water body adjacent to the buffer/filter strip.  126 sample units have a recommended buffer/filter strip width greater than or equal to 50 feet.
Stream Order
Str eam Or der Adjacent to the Buffer/filter Strip

Second order First order Zero order (ephemeral stream)

Fourth order or larger Third order

Drainage ditch systems draining to natural surface waters Pond or lake of State or local significance Wetland or similar area of State or local significance 0% 20% 40% 60% 80% 100% 1%

3%
65%
15%
3%
3%
2%

5%

Figure 17. (n=156)

22
D-24Appendix D—Example Comprehensive Standard Data Summary 

# Example Evaluation  Total length of buffer/filter strip monitored (feet): 95,073 Sediment Delivery Total number of locations where sediment delivered to within bankfull width of the channel as a result of harvest operation 20 Number of locations per 1000 feet of buffer/filter strip monitored 0.210 Sediment Volume (cubic feet) Total volume of sediment currently evident within bankfull width of the channel resulting from harvest operations 199 Volume per 1000 feet of buffer/filter strip monitored 2.088 Rills, Gullies, Sediment Trails Total number of times rills, gullies, or sediment trails resulting from the harvest operation reach more than halfway across the buffer/filter strip 46 Rills, gullies, and sediment trails per 1000 feet of buffer/filter strip monitored 0.484 Naturally Occurring Large Woody Debris (LWD) Number of pieces of naturally occurring LWD in the water body 1582 Number of pieces of LWD per 1000 feet of buffer/filter strip monitored 16.640 Large Woody Debris (LWD) – Harvest Related Number of pieces of LWD occurring in the water body as a result of the harvest 970 Number of pieces of LWD per 1000 feet of buffer/filter strip monitored 10.203 Potential Erosion Channel Number of times a potential erosion channel has been gouged into the bank as a result of harvesting activities 15 Number of times per 1000 feet of buffer/filter strip monitored 0.158 Slash Volume (cubic feet) Less than 100 cubic feet per 1000 feet of buffer/filter strip monitored 0.810 Between 100 and 200 cubic feet per 1000 feet of buffer/filter strip monitored 0.126 More than 200 cubic feet per 1000 feet of buffer/filter strip monitored 0.095 Large woody debris (LWD) is defined as debris found within the bankfull width of the channel that is greater than 4 inches in diameter at the small end and either longer than the stream width or anchored to the bank by roots or other means. Slash is defined as limbs, brush, tree tops, or similar relatively small woody logging debris that is left in the channel below bankfull elevation as a direct result of the current harvest.

23 D-25BMP Manual—Desk Reference 

# Example Shade Reduction/Basal Area Evaluation

Crown Closure (percent) Average 77 Minimum 1 Maximum 100 Basal Area (square feet) Average 95 Minimum 1 Maximum 212 Diameter of Largest Riparian Leave Tree Average 19 Median 18



### Discussion <OPTION: User may insert analysis, interpretation, photographs here.>

24
D-26Appendix D—Example Comprehensive Standard Data Summary



# Chemical Pollutants 309 new sample units were sampled. Evidence of Potential Pollutants  9 sample units had evidence of lubricant, fuel, hydraulic fluid, and/or antifreeze spillage resulting from harvest operations.  13 sample units had evidence of discarded batteries and/or other potential pollutant containers present.  4 sample units had evidence of chemical spills as well as discarded batteries and/or other potential pollutant containers present.

Figure 18. (n=309) Spills Related to Harvest Operations

Minor dripping Stains <10 sq ft Stains 10-100 sq ft

Stains >100 sq ft No evidence of spills 0%

0% 1%

20% 40% 60% 80% 100% 1%
1%

95% 

# Example

25
D-27BMP Manual—Desk Reference 

# Example

Discar ded Batteries or Potential Pollutants

Batteries Discarded batteries and containers Containers totaling > 5 gal Containers totaling < 5 gal

Trash unrelated to logging activity No evidence of batteries or containers No batteries or containers

0% 4%

0%

0% 0%

20% 40% 60% 80% 100%

2% 1%

89%

Figure 19. (n=309)

Evidence of Pollutants Reaching Water Body

Pollutants have reached water body
Pollutants have not reached water body

Unknown

No evidence of pollutants 0% 1%

20% 40% 60% 80% 100% 6% 1%

93%

Figure 20. (n=309) 26
D-28Appendix D—Example Comprehensive Standard Data Summary 

# Example Wetland Crossings 309 new sample units were sampled.  28 sample units have a wetland crossing.

Figure 21. (n=309)

T

able 5. Wetland Crossing Length From Upland to Upland Stabilization T echniques

Corduroy of slash and tops Dry condition operation Frozen condition operation

Bridge or mats Poles average diameter > 10 in

Multiple methods Other No wetland crossing
0% 20% 40% 60% 80% 100% 2% 1%
1%

3%
1%
0%
1%

91%

27 Length (feet) A

verage 217 Median 99 Maximum 999

D-29BMP Manual—Desk Reference 

# Example

100%

80%
60%
40%

4% 4% 1% 0% 91%

20%

0% N o s e d i m e n t r e a c h e d w et l a n d M e a s u r a b l e a m o u n t s d e p o s i t e d i n w et l a n d N o w et l a n d c r o s s i n g T

r a c e a m o u n t s d e p o s i t e d i n w et l a n d S o i l m o ve m e n t o c c u r s b u t h a s b e e n

r e c o r d e d e l s e w h e r e Evidence of Sediment Reaching Wetland

Figure 22. (n=309) 

### Table 6. Wetland Sedimentation Volume (cubic feet) Sediment Volume (cubic feet) Average 21 Median 19 Maximum 45 Nonnumeric values indicate that no volume measurements were recorded.



### Discussion <OPTION: User may insert analysis, interpretation, and photographs here.>

28
D-30Appendix D—Example Comprehensive Standard Data Summary 

# Example Responsibility for BMP Implementation Not Assigned Soil Conditions Observed at the Approaches A total of 29 new sample units were sampled where responsibility for BMP implementation was not assigned.  19 of these sample units have a surface water crossing. There are 4 opportunities to observe the occurrence of soil movement, sedimentation, or stabilization from the approaches to a surface water crossing. They are at Approach Area A–Outside the Buffer/Filter Strip, Approach Area A–Inside the Buffer/Filter Strip, Approach Area B–Inside the Buffer/Filter Strip, and Approach Area B–Outside the Buffer/ Filter Strip. Proportions are based on the total number of opportunities to make observations about soil conditions at the approaches.  For the 29 new sample units, there are 116 opportunities to observe soil conditions.

100%

80%
60%
40% 29% 23% 13% 5% 34%
20%

0% S o i l st a b l e S e d i m e n t a t i o n ( t r a c e ) N o s u rf a c e w a t e r c r o s s i n g S o i l m o v e s ( d o e s n ot

r e a c h w a t e r b o d y) S e d i m e n t a t i o n ( m e a s u r a b l e ) Pr oportion of Observations of Soil Conditions at the Appr oaches: No Responsibility for BMP Implementation Assigned

Figure 23. (n=116)

29
D-31BMP Manual—Desk Reference 

# Example Forester Is Responsible for BMP Implementation by Written Contract Soil Conditions Observed at the Approaches A total of 92 new sample units were sampled where a forester was responsible for BMP implementation by written contract.  65 of these sample units have a surface water crossing. There are 4 opportunities to observe the occurrence of soil movement, sedimentation, or stabilization from the approaches to a surface water crossing. They are at Approach Area A–Outside the Buffer/Filter Strip, Approach Area A–Inside the Buffer/Filter Strip, Approach Area B–Inside the Buffer/Filter Strip, and Approach Area B–Outside the Buffer/ Filter Strip. Proportions are based on the total number of opportunities to make observations about soil conditions at the approaches.  For the 92 new sample units, there are 368 opportunities to observe soil conditions.

100%

80%
60%
40% 46%

16% 6% 2% 29% 20%

0% S o i l st a b l e S e d i m e n t a t i o n ( t r a c e ) N o s u rf a c e w a t e r c r o s s i n g S o i l m o ve s ( d o e s n ot

r e a c h w a t e r b o d y) S e d i m e n t a t i o n ( m e a s u r a b l e ) Pr oportion of Observations of Soil Conditions at the Appr oaches: For ester Responsible for BMP Implementation by W ritten Contract

Figure 24. (n=368) 30
D-32Appendix D—Example Comprehensive Standard Data Summary 

# Example Logger Is Responsible for BMP Implementation by Written Contract Soil Conditions Observed at the Approaches A total of 91 new sample units were sampled where a logger was responsible for BMP implementation by written contract.  73 of these sample units have a surface water crossing. There are 4 opportunities to observe the occurrence of soil movement, sedimentation, or stabilization from the approaches to a surface water crossing. They are at Approach Area A–Outside the Buffer/Filter Strip, Approach Area A–Inside the Buffer/Filter Strip, Approach Area B–Inside the Buffer/Filter Strip, and Approach Area B–Outside the Buffer/ Filter Strip. Proportions are based on the total number of opportunities to make observations about soil conditions at the approaches.  For the 91 new sample units, there are 364opportunities to observe soil conditions.

100%

80%
60%
40% 44% 25% 6% 7% 20% 20%

0% S o i l st a b l e S e d i m e n t a t i o n ( t r a c e ) N o s u rf a c e w a t e r c r o s s i n g S o i l m ov e s ( d o e s n ot

r e a c h w a t e r b o d y) S e d i m e n t a t i o n ( m e a s u r a b l e ) Proportion of Observations of Soil Conditions at the Approaches: Logger Responsible for BMP Implementation by W ritten Contract

Figure 25. (n=364) 31
D-33Appendix E—Question Map 

# Appendix E—Question Map
In the Excel file containing data from the field, each column contains the answers to a specific BMP protocol question. The following table lists protocol questions by column location. The color coding by category matches that used in the Excel file. Columns GY through HN contain the results of calculations made on the protocol data. A Indexing key (inserted by Access) General B G1State C CurrentYear D SampleType E Resample? F SampleNumber G G2StatewideForestOwnership H G3LandownerCategory I G4OwnerProgramParticipant J G5BMPResponsibilityAssigned K G6LoggerProgramParticipant L G7HarvestStatus M G8AcresInSampleUnit N G9TypeOfHarvestSystem O G10InfluencingWeatherEvent P G11BMPsVolMandatory Water Body Crossing Approach Area A Q X12StreamOrderAtCrossingSite R X13WaterBodyTypeInNormalYr S X14GPSLatN999000000 T X15GPSLongW999000000 U X16StateBMPBufferWidthFt V X17ContractBufferWidthFt Approach Area A–Outside the Buffer/Filter Strip W X18RoadOrTrail X X19RoadTrailDescription Y X20BearingCapacityImprovments Z X21PrimaryAdjacentLandUse AA X22SoilMovementOutsideBuffer AB X23EvidenceSedimentReachedWater AC X24LengthFtOfRillGullyDitchOrRut AD X25MidPointXSectionSqIn E-1BMP Manual—Desk Reference AE X26SedimentInWaterCuFt AF X27PreponerantSedimentType AG X28SedimentAgainNextStorm AH X29SpecificSedimentationCause AI X30PrinciplesPracticesApplication AJ X31PercentSoilMovedThruBuffer AK X32EvidenceSedimentReachedBuffer AL X33PreponerantSedimentType AM X34SedimentAgainNextStorm AN X35SpecificSedimentationCause AO X36PrinciplesPracticesApplication AP X37DistanceTheSoilMovedFromPointOfOriginTowardBuffer AQ X38HowSedimentReachedWaterBody AR X39PreponerantSedimentType AS X40SedimentAgainNextStorm AT X41SpecificSedimentationCause AU X42PrinciplesPracticesApplication AV X43ApplicationOfBMPPrinciplesAndPractices Approach Area A–Inside the Buffer/Filter Strip AW X44HaulRoadOrSkidTrail AX X45BearingCapacityImprovement AY X46RoadTrailDescription AZ X47DescribeSoilMovement BA X48EvidenceSedimentReachedWater BB X49LengthFtOfRillGullyDitchOrRut BC X50MidPointXSectionSqIn BD X51SedimentInWaterCuFt BE X52PreponerantSedimentType BF X53SedimentAgainNextStorm BG X54SpecificSedimentationCause BH X55PrinciplesPracticesApplication BI X56PercentSoilMovedThruBuffer BJ X57HowSedimentReachedWaterBody BK X58PreponerantSedimentType BL X59SedimentAgainNextStorm BM X60SpecificSedimentationCause BN X61PrinciplesPracticesApplication BO X62ApplicationOfBMPPrinciplesAndPractices BP X63DominantHydrologicSoilType BQ X64SoilErodibilityFactorKNNN E-2Appendix E—Question Map BR X65INSIDERoadGradient BS X66OUTSIDERoadGradient BT X67SlopeLengthFt Crossing Structure BU X68TrafficUseAtTheCrossing BV X69PrimaryPurposeOfCrossing BW X70RoadwayOwnership BX X71CrossingStructureDescription BY X72CrossingStructureVintage BZ X73InPlace3MoFishery CA X74BottomAndStreamSubstrate CB X75StuctureOpeningWidthFt CC X76OpeningMeetsBMPSpec CD X77Scouring100FtDownstream CE X78SoilOrFillMaterialMovement CF X79ActivityResultingInSedimentDeliveryFromCrossingStructure CG X80EvidenceSedimentReachedWater CH X81SedimentVolumeCuFt CI X82PreponerantSedimentType CJ X83SedimentAgainNextStorm CK X84PrinciplesPracticesApplication CL X85PrinciplesPracticesApplication CM X86BMPPrinciplesAndPracticesToTheCrossing Water Body Crossing Approach Area B CN X86.1StateBMPBufferWidthFt CO X86.2ContractBufferWidthFt CP X87HaulRoadSkidTrail CQ X88RoadTrailDescription CR X89BearingCapacityImprovement CS X90PrimaryAdjacentLandUse CT X91DescribeSoilMovement Approach Area B–Outside the Buffer/Filter Strip CU X92EvidenceSedimentReachedWater CV X93LengthOfRillGullyDitchOrRutFt CW X94MidPointXSectionSqIn CX X95SedimentInWaterCuFt CY X96PreponerantSedimentType CZ X97SedimentAgainNextStorm DA X98SpecificSedimentationCause E-3BMP Manual—Desk Reference DB X99PrinciplesPracticesApplication DC X100PercentSoilMovedThruBuffer DD X101HowSedimentReachedWaterBody DE X102PreponerantSedimentType DF X103SedimentAgainNextStorm DG X104SpecificSedimentationCause DH X105PrinciplesPracticesApplication DI X106FtSoilMovedTowardBuffer DJ X107HowSedimentReachedWaterBody DK X108PreponerantSedimentType DL X109SedimentAgainNextStorm DM X110SpecificSedimentationCause DN X111PrinciplesPracticesApplication DO X112ApplicationOfBMP Approach Area B–Inside the Buffer/Filter Strip DP X113HaulRoadOrSkidTrail DQ X114BearingCapacityImprovements DR X115RoadTrailDescription DS X116DescribleSoilMovement DT X117EvidenceSedimentReachedWater DU X118TotalLengthOfRillGullyDitchOrRutFt DV X119MidPointXSectionSqIn DW X120VolumeOfSedimentCuFt DX X121PreponerantSedimentType DY X122SedimentAgainNextStorm DZ X123SpecificSedimentationCause EA X124PrinciplesPracticesApplication EB X125PercentSoilMovedThruBuffer EC X126EvidenceSoilMovement ED X127PreponerantSedimentType EE X128SedimentAgainNextStorm EF X129SpecificSedimentationCause EG X130PrinciplesPracticesApplication EH X131BMPApplication EI X132DominantHydrologicSoil EJ X133SoilErodibilityFactorK EK X134INSIDERoadGradient EL X135OUTSIDERoadGradient EM X136SlopeLengthFt
E-4Appendix E—Question Map Haul Road, Log Landing, or Rutted, Mineral Soil Skid Trail Inside the Buffer/Filter Strip EN HB137HaulRdLogLndgInBuffer EO HB138GPSLatN999000000 EP HB139GPSLongW999000000 EQ HB140WaterBodyType ER HB141WaterBodyDescription ES HB142RecmndStripWitdthFt ET HB143BufferFilterStripWidthFt EU HB144BearingCapacityImprovements EV HB145RoadLandingDescription EW HB146DescribeSoilMovement EX HB147EvidenceSedimentReachedWater EY HB148LengthOfDitchGullyRillFt EZ HB149MidPointXSectionSqIn FA HB150VolumeOfSedimentCuFt FB HB151PreponerantSedimentType FC HB152SedimentAgainNextStorm FD HB153SpecificSedimentationCause FE HB154PrinciplesPracticesApplication FF HB155PercentSoilMovedTowardBuffer FG HB156HowSedimentReachedWaterBody FH HB157PreponderantSedimentType FI HB158SedimentAgainNextStorm FJ HB159SpecificSedimentationCause FK HB160PrinciplesPracticesApplication FL HB161BMPApplication FM HB162DominantHydrologicType FN HB163SoilErodibilityFactorKNNN FO HB164INSIDERdGradient FP HB165OUTSIDERdGradient FQ HB166SlopeLengthFt Chemical Pollutants FR CP167LoggingSpillage FS CP168DiscardedContainers FT CP169EvidenceReachWaterBody FU CP170Preponderant SoilType FV CP171GPSLatOfObservationsN999000000 FW CP172GPSLongOfObservationsW999000000
E-5BMP Manual—Desk Reference Buffer/Filter Strip FX B173WaterBodyType FY B174StreamOrder FZ B175StateRegulatedWaterBody GA B176WidthOfBufferFtRec GB B177GPSLatN999000000 GC B178GPSLongW999000000 GD B179TimesSedimentDelivered GE B180CumVolSedimentCuFt GF B181TimesReached12Way GG B182NaturalWoodyDebris GH B183HarvestWoodyDebris GI B184TimesBankGouged GJ B185VolumeOfSlash GK B186LengthOfBufferFt GL B187AvgShade GM B188AvgBasalArea GN B189AvgDiameterLargestTree GO B190ReductionInShade GP B191ShadeBAMeetsStateBMP Wetland Crossing GQ W192CrossingStabilizationTechnique GR W193WetlandCrossingLengthFt GS W194AverageRuttingDepth GT W195RoadCrossDrainMethod GU W196SedimentReachesWetland GV W197DischargeVolumeCf GW AreYouFinishedWithThisEntry GX blank column Columns Used for Calculations GY Calculated volume of rill/gully Aout GZ Calculated volume of rill/gully Bout HA Calculated volume of rill/gully Ain HB Calculated volume of rill/gilly Bin HC Calculated volume of rill/gully HRLL HD Sediment volume evident in stream Aout HE Sediment volume evident in stream Bout HF Sediment volume evident in stream crossing structure HG Sediment volume evident in stream Ain E-6Appendix E—Question Map HH Sediment volume evident in stream Bin HI Sediment volume evident in stream HRLL HJ Percent soil movement through buffer Aout HK Percent soil movement through buffer Bout HL Percent soil movement through buffer Ain HM Percent soil movement through buffer Bin HN Percent soil movement through buffer HRLL State- or User-Defined Entries HO S1 State- or user-defined entry HP S2 State- or user-defined entry HQ S3 State- or user-defined entry HR S4 State- or user-defined entry HS S5 State- or user-defined entry HT S6 State- or user-defined entry HU S7 State- or user-defined entry HV S8 State- or user-defined entry HW S9 State- or user-defined entry HX S10 State- or user-defined entry HY S11 State- or user-defined entry HZ S12 State- or user-defined entry IA S13 State- or user-defined entry IB S14 State- or user-defined entry IC S15 State- or user-defined entry ID S16 State- or user-defined entry IE S17 State- or user-defined entry IF S18 State- or user-defined entry IG S19 State- or user-defined entry IH S20 State- or user-defined entry

E-7Appendix F—Access Query Expressions 

# Appendix F—Access Query Expressions Criteria Expressions When entering text, dates, times, and numbers into criteria expressions, use the appropriate characters to indicate whether the criteria is text, a date, time, or numerical expression. Data type Expression Text “text” Date #16-Oct-04# Time #10:30am# Number 5 

## Operators for Building Queries The following operators are useful when building queries in Access.

Operator Function Example

= Finds values equal toa number, text, or date and time (note: “equal to” is understood if criteria is entered without the “=” operator). The expression =4will select all cells with the number 4.

<> Finds values not equal toa number, text, or date and time. The expression <>5 will select all cells that are not equal to 5.
< Finds values less thanthe given value. The expression <5 will select all cells that have a value less than 5.
<= Finds values less than or equal tothe given value. The expression <=5 will select all cells that have a value less than or equal to 5.
> Finds values greater thanthe given value. The expression >5 will select all cells that have a value greater than 5.
>= Finds values less than or equal tothe given value. The expression >=5 will select all cells that have a value greater than or equal to 5. BETWEEN Finds values between or equal totwo values. The expression BETWEEN 1 AND 5 returns values between 1 and 5. (The expression >=1and<=5 returns the same results).

F-1BMP Manual—Desk Reference 

## Specifying Multiple Criteria for a Single Field You may use multiple criteria for a single field by using AND or OR in the criteria expression for that field. For example, the query shown below is designed to return all sample units that meet either of the two following criteria: (1) contain Nin the Sample Type column and have either 1 OR 2as the answer to question X47, (2) contain Nin the Sample Type column and have either a 1 OR 2as answer to question X116. This query is searching for new sample units that have evidence of trace or measurable sedimentation from the approaches to the surface water crossing inside the buffer/filter strip.

F-2Appendix F—Access Query Expressions The query shown below, however, is designed to return all sample units that meet either of the following criteria: (1) contain N in the Sample Type column and have both 1 AND 2 as the answer to question X47, (2) contain N in the Sample Type column and have both 1 AND 2 as the answer to question X116. Since there cannot be two answers to any question in the BMP protocol, no sample units will meet the criteria in this query.

Source: Simpson, Alan; Young, Margaret Levine; Barrows, Alison. 2003. Access 2003 all-in-one desk reference for dummies. Indianapolis, IN: Wiley Publishing, Inc. 840 p.

F-3Appendix G—Excel Functions and Operators 

# Appendix G—Excel Functions and Operators The following functions and operators are useful for creating formulas in Excel.

Function Description Syntax AVERAGE Calculates the mean of a group of values =AVERAGE(number1,number2,...)
COUNT Counts the number of cells in a range that contains numbers =COUNT(number1,number2,...) COUNTIF Counts the number of cells in a range that match a criteria =COUNTIF(number1,number2,...)
IF Tests for a true or false condition and returns one value or another value =IF(logical_test,value_if_true,value_if_false) MAX Returns the largest value in a set of values =MAX(number1,number2,...) MEDIAN Returns the median number in a set of numbers =MEDIAN(number1,number2,...)
MIN Returns the minimum value in a set of values =MIN(number1,number2,...) SUM Calculates the sum of a group of values =SUM(number1,number2,...) SUMIF Calculates a sum from a group of values, including only the values that meet a certain condition =SUMIF(range,criteria,sum_range)

Logic operator Description

= Equals
> Greater than
< Less than
<> Not equal to
>= Greater than or equal to
<= Less than or equal to

Source: Bluttman, Ken; Aitken, Peter G. 2005. Excel formulas and functions for dummies. Indianapolis, IN: Wiley Publishing, Inc. 402 p.

G-1The use of trade or firm names in this publication is for reader information and does not imply endorsement by the U.S. Department of Agriculture of any product or service. The U.S. Department of Agriculture (USDA) prohibits discrimination in all its programs and activities on the basis of race, color, national origin, age, disability, and where applicable, sex, marital status, familial status, parental status, religion, sexual orientation, genetic information, political beliefs, reprisal, or because all or part of an individual’s income is derived from any public assistance program. (Not all prohibited bases apply to all programs.) Persons with disabilities who require alternative means for communication of program information (Braille, large print, audiotape, etc.) should contact USDA’s TARGET Center at (202) 720-2600 (voice and TDD). To file a complaint of discrimination, write to USDA, Director, Office of Civil Rights, 1400 Independence Avenue, SW, Washington, D.C. 20250-9410, or call (800) 795-3272 (voice) or (202) 720-6382 (TDD). USDA is an equal opportunity provider and employer.