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 Resources

Cover 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 Barten1 

Published 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 ......................................................40

BMP 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...................................................131

Table 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-1


Chapter 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, 12th central 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. 

1 
BMP 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. 

2 
Chapter 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 computer 

by opening My Computer on your desktop and rightclicking anywhere in that window. Select Properties 

from the menu. The System Properties box will open. 

The General tab 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 

Download field data: 

Excel 

Sort and filter data: 

Access 

Query data: 

Excel 

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. 

3 
BMP 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. 

5 
BMP 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)

6 
Chapter 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 BMP and 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. 

7 
BMP Manual—Desk Reference 

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

Navigate to the BMP folder 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. 

8 
Chapter 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. 

9 
BMP 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 File on the main toolbar, then select Get 

External Data and Import from the drop-down menus. 

10 
Chapter 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 type box 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. 

11 
BMP 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. 

12 
Chapter 3—Installing and Using the BMP MIS 

The Import Spreadsheet Wizard window 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. 

13 
BMP 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. 

14 
Chapter 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. 

15 
BMP 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 Wizard will 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. 

16 
Chapter 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. 

17 
BMP 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. 

18 
Chapter 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 ImportErrors table 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 Conversion error 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. 

19 
BMP 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. 

20 
Chapter 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.mdb is 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 Queries under Objects. Each file is a separate query—a set of 

filtered and sorted field data. 

21 
BMP 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. 

22 
Chapter 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 Properties window 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. Each query 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. 

23 
BMP 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 Samples and select Export from the drop-down 

menu. 

24 
Chapter 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 type box 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. 

25 
BMP 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 name box 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. 

26 
Chapter 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 each of 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 each query file 

individually. The export process must be repeated for every one of the Access queries. 

27 
BMP 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). 

28 
Chapter 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. 

29 
BMP 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.doc to open it. The following dialog box will appear. 

30 
Chapter 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 No simply 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., 0 activities and conditions in 0 

sample units). 

Number of New Sample Units by Feature 

Haul road in the 

buffer/filter strip, Haul road Chemical spill or No surface water Wetland crossing, Skidder 

10 pollutant crossings, 66 crossing, 72 crossings, 157 28 Riparian area, 156 containers, 4

Figure X. (n=0) 

31 
BMP 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 

crossing=28 or pollutant 

containers=4 water 

Skidder 

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. 

32 
Chapter 3—Installing and Using the BMP MIS 

Step 3.12. Repeat the Process With the Remaining SDS Files 

Open and update the links in each of 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. 

33 

Chapter 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.doc presents 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 Comments field. An example of the Properties window for New SU 

Approaches SDS.doc appears below. A list and description of all the SDS template files available also appears in 

appendix C. 

35 
BMP 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.doc is 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.> 

36 
Chapter 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. 

37 
BMP 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 File from the main toolbar again and Save As from 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. 

38 
Chapter 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. 

39 
BMP Manual—Desk Reference 

The Insert File window will open. Select the file New SU General Information SDS.doc to 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. 

40 
Chapter 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 Links from the drop-down menu. 

41 
BMP 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 the down-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 

42 
Chapter 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. 

43 
BMP 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 Picture and From File 

from the drop-down menus. 

44 
Chapter 4—SDSs and Comprehensive Standard Data Summaries 

When the Insert Picture window opens, navigate to the location of the file to be inserted. Select the file name 

in the Insert Picture window 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 Picture from 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. 

45 
BMP 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 File from 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. 

46 
Chapter 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). 

47 

Chapter 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. 

49 
BMP 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 Queries to view the entire list of available queries. 

50 
Chapter 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 all of 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 Options from the drop-down 

menu. 

51 
BMP Manual—Desk Reference 

The Options window will open. Select the Tables/Queries tab. In the left-hand side of the window is the 

heading Query design. Make sure the Output all fields box is checked. Select OK to close the Options window. 

This box must 

be checked 

52 
Chapter 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. 

53 
BMP Manual—Desk Reference 

The Design View of the new query will open, along with the Show Table window. 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. 

54 
Chapter 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. 

55 
BMP 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 question G1) 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 Type and 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 Field cell in the empty table. A drop-down menu will appear with the field names from 

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

56 
Chapter 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 both of 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 N in 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. 

57 
BMP 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 4 in the 

criteria cell in the column for G5. The screen should appear as shown below. 

Important: Since the criteria are entered in the same row in the table, the sample units returned will meet 

both conditions. If you are searching on criteria that may meet either condition, enter the criteria on different 

rows. For example, the table below will return sample units that are either new or have a logging contractor 

responsible for BMP implementation by written contract as part of the sale agreement. The results of this 

query are very different. 

58 
Chapter 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. 

59 
BMP 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. 

60 
Chapter 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 File from the main toolbar and Save As from the drop-down menu. In the Save 

As window, 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 File from the main toolbar and Close from 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. 

61 
BMP 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. 

62 
Chapter 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. 

63 
BMP 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 Export from the dropdown menu. 

64 
Chapter 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. 

65 
BMP 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 Excel as the file 

type by clicking on the drop-down menu next to the Save as type box. 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. 

66 
Chapter 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. 

67 
BMP 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. 

68 
Chapter 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. 

69 
BMP 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. 

70 
Chapter 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 not 

ordinarily 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 

71 
BMP 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. 

72 
Chapter 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. 

73 
BMP 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–Outside the 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. 

74 
Chapter 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 proportion of 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. 

75 
BMP Manual—Desk Reference 

Note: Cells that calculate proportions must be formatted as a percentage. Click on the cell to highlight it, then 

select Format on the main toolbar and Cells from the drop-down menu. 

The Format Cells window will open. On the Number tab, select Percentage in the Category window. Select 

zero (0) as the number of decimal places. Select OK to apply the formatting to the selected cell. 

76 
Chapter 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 function works 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. 

77 
BMP 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.) 

78 
Chapter 5—Custom Queries and Data Summaries 

Remember: You must enter an array formula with a Ctrl + Shift + Enter keystroke combination. 

Note the difference between the following two examples. In the first screen, the formula is entered with only 

an Enter keystroke, resulting in a #VALUE error. 

In the second screen, the formula is entered with a Ctrl + Shift + Enter keystroke combination. The formula 

calculates the results accurately. 

79 
BMP Manual—Desk Reference 

The same syntax can be applied to the formula calculating sediment volumes for the approach areas inside the 

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. 

80 
Chapter 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. 

81 
BMP 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. 

82 
Chapter 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 Column under Standard 

Types and select Next. 

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

83 
BMP 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 Name box. 

Type the chart 

name into the 

Name box. 

84 
Chapter 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 Source 

Data – Values 

box. 

Select these 

data by 

highlighting 

the cells. 

85 
BMP 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 Values box, and display the data series in 

the chart. 

86 
Chapter 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. 

87 
BMP 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 title box. 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. 

88 
Chapter 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 the Show 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 Value box. The values of the data series will appear above the columns. 

89 
BMP 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. 

90 
Chapter 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 Axis from the drop-down menu. 

91 
BMP Manual—Desk Reference 

The Format Axis window will open. To adjust the scale of the axis, select the Scale tab. 

Change the Maximum setting 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. 

92 
Chapter 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. 

93 
BMP Manual—Desk Reference 

Select Format Chart Title to open the Format Chart Title window. 

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

menu. 

94 
Chapter 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. 

95 
BMP 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. 

96 
Chapter 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. 

97 
BMP 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 as to 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. 

98 
Chapter 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. 

99 
BMP 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. 

100 
Chapter 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 Special from the drop-down menu. 

The Paste Special window will open. Word defaults to the Paste option. Select the Paste link option on the left 

side of the window. In the As: box, select Unformatted Text. 

Select 

Unformatted 

Text 

Select 

Paste 

link 

101 
BMP 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. 

102 
Chapter 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 Special from the 

drop-down menu. 

103 
BMP Manual—Desk Reference 

The Paste Special window will open. Word defaults to the Paste option. 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.


104 
Chapter 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 location of 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. 

105 
BMP 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 Tools from the main toolbar 

and Options from the drop-down menu. 

106 
Chapter 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. 

107 
BMP 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 F9 key. 

108 
Chapter 5—Custom Queries and Data Summaries 

You may update a single link or multiple links by selecting Edit from the main toolbar and Links from the dropdown menu. 

109 
BMP 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 file entry. 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. 

110 
Chapter 5—Custom Queries and Data Summaries 

After updating is complete, select OK to close the Links window 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. 

111 
BMP Manual—Desk Reference 

Select Tools from the main toolbar, and select Protection and 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. 

112 
Chapter 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 Tools from the main toolbar, 

and select Protection and Protect Sheet from the drop-down menus. 

113 
BMP 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. 

114 
Chapter 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 screen below 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 

115 
BMP 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.xls in 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.xls file 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 

116 
Chapter 6—Performing Spatial Queries 

In the new worksheet, click on cell A1. Click the down arrow to the right of the Paste icon on the toolbar at the 

top of the worksheet. Select Paste Special from the drop-down menu. 

117 
BMP Manual—Desk Reference 

The Paste Special window will open. Check Column widths and select OK. 

118 
Chapter 6—Performing Spatial Queries 

Click the down arrow to the right of the Paste icon on the toolbar again. Select Paste Special from the dropdown menu. Check Values and number formats and select OK. 

119 
BMP Manual—Desk Reference 

The data from your BMP Field Data.xls will be copied into the new worksheet in the correct format. Select File 

from the main toolbar and Save As from the drop-down menu. 

120 
Chapter 6—Performing Spatial Queries 

In the Save As window, save this interim file to the folder C:\BMP. In the File name box, enter a file name that 

describes the data (in this example, it is NA Test_States_Field_Data). In the Save as type box, 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. 

121 
BMP Manual—Desk Reference 

Select OK in the window that opens to save the new worksheet as a .dbf file. 

122 
Chapter 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). 

123 
BMP Manual—Desk Reference 

Step 6.2. Initiate the BMP Feature Generator Tool 

Navigate to the GIS folder in C:\BMP and open it. Double-click on the ArcMap project file BMP.mxd (C:\

BMP\GIS\BMP.mxd) to open it. The ArcMap-ArcInfo window 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 

124 
Chapter 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. 

125 
BMP 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. 

126 
Chapter 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. 

127 
BMP 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). 

128 
Chapter 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 Zoom icon on the toolbar. 

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

datasets. 

129 
BMP 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 Screen key. It can then be pasted into other 

products, such as PowerPoint presentations, BMP reports, or other publications. 

130 
Chapter 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 Results window 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 Location utility 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.mxd project file is opened. 

131 
BMP Manual—Desk Reference 

Select the Selection icon on the main toolbar and Select By Attributes from the drop-down menu. 

132 
Chapter 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 Method box, select Create a new 

selection from the drop-down list. In the third box, scroll down and click on HUC_NAME. Click on the “ = ” 

button and the Get Unique Values button. 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. 

133 
BMP 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. 

134 
Chapter 6—Performing Spatial Queries 

Select Selection from the main toolbar and Select By Location from the drop-down menu. The Select By 

Location window will open. In the I want to box, choose select features from from 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 within from 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. 

135 
BMP 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 Table from the drop-down menu. The Selected Attributes 

window will open. Click the Selected button 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. 

136 
Chapter 6—Performing Spatial Queries 

The Export Data window will open. In the Export box, select Selected records from the drop-down menu. In 

the Output table box, 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. 

137 
BMP Manual—Desk Reference 

In the ArcMap window 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. 

138 
Chapter 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. 

139 
BMP Manual—Desk Reference 

>

>

>> >

> >

Sample 

unit 1 

Sample 

unit 2 

Sample 

unit 7 

Sample 

unit 3 

Sample 

unit 4 

Sample 

unit 5 

Sample 

Haul road crossing unit 6

Skid trail crossing 

Haul road in buffer/filter strip 

Buffer/filter strip 

Ridgeline 

Highway 

Haul road 

Skid trail 

Stream 

Bridge 

Culvert 

Landing 

Sample unit boundary 

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. 

140 
Chapter 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*B2 in 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. 

141 
BMP 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. 

142 
Chapter 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. 

143 
BMP 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*B2 in 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. 

144 
Chapter 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. 

145 
BMP 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. 

146 
Chapter 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 from 

solicitation 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. 

147 
BMP 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. 

148 
Chapter 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 

phase and 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. 

149 
BMP 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. 

150 
Chapter 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.xls file and open it in Excel. The file has been password protected 

to prevent accidental alteration of the cell formulas. To unprotect the worksheet, select Tools from 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 

File from 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.xls file 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 Paste icon. 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. 

151 
BMP 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. 

152 
Chapter 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. 

153 

Appendix 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-1 
BMP 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-2 
Appendix 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 a 

water 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-1 
BMP 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-2 
Appendix 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-3 
BMP 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-4 
Appendix 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-5 
BMP 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-6 
Appendix 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-7 

Appendix 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-1 

Appendix 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-1 
BMP 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-2 
Appendix 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-3 
BMP 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-4 
Appendix 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-5 
BMP 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-6 
Appendix D—Example Comprehensive Standard Data Summary 

Ownership Category 

Figure 1. (n=309) 

Proportion 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-7 
BMP 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 

crossing=28 or pollutant 

containers=4 water 

Skidder 

Figure 2. (n=309) 

6


D-8 
0%

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.> 

Proportion of New Sample Units by Feature 

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-9 
BMP 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-10 
Appendix 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-11 
BMP 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 Approaches 

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-12 
Appendix 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 77 observations 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-13 
BMP 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 

from the Crossing 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 80 observations of measurable amounts of sediment reaching the surface 

water body or deposited within bankfull channel width of the water feature. 

12 

D-14 
Appendix 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 Type 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-15 
BMP Manual—Desk Reference 

Example 

Crossing Structure Specifications 

A total of 309 new sample units were sampled. 

	226 sample units have surface water crossings. 

Crossing Structure Types 

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 157 sample units with a skid trail at the water crossing.

	There are 66 sample units with a haul road at the water crossing.

14


D-16 
Appendix 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). 

Structure Type Associated with Skidder Crossing 

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% 

Structure Type Associated with Haul Road Crossing 

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-17 
BMP 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% 

Neither fish or 

macroinvertebrates 

Fish and macroinvertebrates 

present and 

No surface 

water crossing 

Fish and macroinvertebrates 

present and 

Unknown 

present structure structure 

in place in place 

more than less than 

3 months 3 months 

Figure 11. (n=309) 

16


D-18 
Appendix 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% 

Open bottom; 

open to natural 

Closed bottom; 

natural 

No surface 

water crossing 

Closed bottom; 

natural 

Closed bottom 

structure; 

Figure 12. (n=309) 

Discussion 

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

17


D-19 
BMP 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. 

Proportion of Occurrences of Soil Movement Through the 

Buffer/Filter Strip Toward 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-20 
Appendix 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. 

	207 sample 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 207 new 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. 

Proportion of Occurrences of Soil Movement Through the 

Buffer/Filter Strip Toward 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-21 
BMP 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% 

Sedimentation 

(trace) 

Sediment 

moves 

(does not 

reach 

water body) 

No haul road or 

log landing 

inside the 

buffer/filter 

strip 

Sedimentation 

(measurable) 

Soil stable 

Observations of Soil Stabilization, Movement, and Sedimentation 

From 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-22 
Appendix 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 

Preponderant Soil Type of Sedimentation Delivered 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-23 
BMP 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 

Stream Order 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-24 
Appendix 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-25 
BMP 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-26 
Appendix 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-27 
BMP Manual—Desk Reference 

Example 

Discarded 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-28 
Appendix 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) 

Table 5. Wetland Crossing Length From Upland to Upland 

Stabilization Techniques 

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) 

Average 217 

Median 99 

Maximum 999 

D-29 
BMP Manual—Desk Reference 

Example


100% 

80% 

60% 

40% 

4% 4% 1% 0% 

91% 

20% 

0% 

No sediment 

reached 

wetland 

Measurable 

amounts 

deposited 

in wetland 

No wetland 

crossing 

Trace 

amounts 

deposited 

in wetland 

Soil movement 

occurs 

but has been 

recorded 

elsewhere 

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-30 
Appendix 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% 

Soil stable Sedimentation 

(trace) 

No surface 

water 

crossing 

Soil moves 

(does not 

reach 

water body) 

Sedimentation 

(measurable) 

Proportion of Observations of Soil Conditions at the Approaches: 

No Responsibility for BMP Implementation Assigned 

Figure 23. (n=116) 

29


D-31 
BMP 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% 

Soil stable Sedimentation 

(trace) 

No surface 

water 

crossing 

Soil moves 

(does not 

reach 

water body) 

Sedimentation 

(measurable) 

Proportion of Observations of Soil Conditions at the Approaches: 

Forester Responsible for BMP Implementation by Written Contract 

Figure 24. (n=368) 

30


D-32 
Appendix 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 364 opportunities to observe soil 

conditions. 

100% 

80% 

60% 

40% 

44% 

25% 

6% 7% 

20% 

20% 

0% 

Soil stable Sedimentation 

(trace) 

No surface 

water 

crossing 

Soil moves 

(does not 

reach 

water body) 

Sedimentation 

(measurable) 

Proportion of Observations of Soil Conditions at the Approaches: 

Logger Responsible for BMP Implementation by Written Contract 

Figure 25. (n=364) 

31


D-33 

Appendix 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-1 
BMP 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-2 
Appendix 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-3 
BMP 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-4 
Appendix 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-5 
BMP 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-6 
Appendix 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-7 

Appendix 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 to a number, text, or date 

and time (note: “equal to” is understood if 

criteria is entered without the “=” operator). 

The expression =4 will select all cells with the 

number 4. 

<> Finds values not equal to a number, text, or 

date and time. 

The expression <>5 will select all cells that 

are not equal to 5. 

< Finds values less than the given value. The expression <5 will select all cells that 

have a value less than 5. 

<= Finds values less than or equal to the given 

value. 

The expression <=5 will select all cells that 

have a value less than or equal to 5. 

> Finds values greater than the given value. The expression >5 will select all cells that 

have a value greater than 5. 

>= Finds values less than or equal to the 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 to two values. The expression BETWEEN 1 AND 

5 returns values between 1 and 5. (The 

expression >=1and<=5 returns the same 

results). 

F-1 
BMP 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 N in the Sample Type column and have either 1 OR 2 as the answer to question X47, (2) 

contain N in the Sample Type column and have either a 1 OR 2 as 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-2 
Appendix 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-3 

Appendix 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-1 
The 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.