EDD Quick Reference Guide
This document was developed to act as a quick reference guide to filling out the Maine DEP EDD and outlines special requirements and considerations.
INTRODUCTION
EDD as the Required Standard Template
USING THE EDD DATA ELEMENT DICTIONARY
Field Name
Field Format
Definition
Valid Values/LUPs-Refer to the following Look-Up Tables
Data Types
Field Requirement Definitions
Default Value if Null
SPECIAL CONSIDERATIONS WHEN COMPLETING THE EDD
Artesian Well Conditions
Non-Detects and Zero Values
QA/QC Data
Surrogates
Matrix Spikes & Lab Control Samples
Blanks
Field Duplicates
Lab Sample IDs & DEP Sample IDs
Dilutions
Reporting Limits, MDLs, and IDLs
Data Validation
Non-Sample Data
Sample Taken but Not Analyzed Data
Samples Taken at Different Depths
Test Methods & Prep Methods
Soil, Sediment, & Tissue Samples
Sample Point ID Name Changes
Historical Data
LUP Values & Tables
SUBMITTAL OF THE EDD
Format
Identifying
Sending to Maine DEP
Resubmitting
TIPS ON PREPARING THE EDD
Common Problems
Using the Macros
CONTACTING EGAD DATA MANAGER
INTRODUCTION
The Maine Environmental and Groundwater Analysis Database (EGAD) started out as a utility to store and access comprehensive up-to-date ground water quality data for the Bureau of Remediation and Waste Management. EGAD has now grown to include a broad range of environmental data including physical, chemical, biological and spatial data. Recently, data from the Bureau of Land & Water Quality became part of EGAD and EGAD was renamed to the Environmental and Geographic Analysis Database. In order to streamline the entry and submittal of data into EGAD, the Maine DEP Electronic Data Deliverable (EDD) was developed and is the required standard template for laboratory and field data submittals to EGAD. Authority for this requirement is granted under Maine 's Uniform Electronic Transaction Act, 10 M.R.S.A. §9418 (2) (A).
The ME DEP EDD version 5.0 (ME DEP EDD v5.0) is the newest EDD version, released on October 1, 2007. The worksheet titled “EDD” contains the ME DEP Electronic Data Deliverable (EDD) template for all chemical, field, and physical measurement data (including water level and flow data). The worksheet titled “Data Element Dictionary” contains pertinent information about each of the fields in the EDD including field definitions and which appropriate LUP Tables and Values pertain to the field, if any.
USING THE EDD DATA ELEMENT DICTIONARY
The Data Element Dictionary worksheet of the EDD contains information about each of the fields in the EDD, including field definitions. The Data Element Dictionary also identifies which fields are required, required if applicable, optional, or not applicable based on data type. For each field in the EDD, the Data Element Dictionary identifies the format, definition, the LUP table where appropriate values for the field can be found (if applicable), the requirements for the field based on data type, and the default value of the field if left blank.
Field Name
This column corresponds to the fields in the EDD.
Field Format
This column identifies what the format of the corresponding field in the EDD should be. Such formats include Text, Time, Date, and Number.
Definition
This column provides a description of the EDD fields and identifies special considerations when populating the field. For example, it is noted in definitions for the IDL and MDL fields that the reported values must be in the same units as the reported concentration.
Valid Values/LUPs-Refer to the following Look-Up Tables
This column identifies whether a field must be populated with a look-up value and in which look-up table all possible values can be found. All fields that must be populated by a look-up value have the corresponding look-up table listed where the value can be found. Fields that are not subject to a value in a look-up table have a grey box.
Data Types
Because more than just laboratory data is being submitted on the EDD, “data types” or categories were developed to help distinguish and identify which EDD fields were required depending on the type of data being submitted. The categories or “data types” are defined below:
Laboratory Data: Data sent into and analyzed by a laboratory.
Field Measurement Data: Applies to Data measured in the field but not sent to the lab. Field data can be obtained by a number of methods using field instruments and test kits. (ex. Temperature, conductivity, pH, etc.)
Water Level & Flow Data: Static Water Level, Water Level Elevation, Ground Surface Elevation, Measuring Point Elevation, and Flow measurements.
Non-Sample Data: Applies to sample events where the sample(s) or physical measurement(s) are attempted to be collected or taken but are not for a variety of reasons including the presence of Free Product, Discontinued Well, Dry Well, Frozen Well, Broken Glassware, or Instrument Malfunction.
Providing Non-Sample data is optional.
Sample Taken but Not Analyzed: Applies to when a sample is collected in the field, but not analyzed in the laboratory due to the sample being lost because of broken glassware or an instrument malfunction. A "lost" sample can also result in partial analysis. For example, a sample with the same sample ID can be collected for DRO and GRO analysis, but the GRO sample bottle is broken in transit, and only the DRO sample is analyzed. Providing Sample Taken but Not Analyzed data is optional.
Field Requirement Definitions
For each data type, an indication of whether a value in each field is required, required if applicable, optional, or not applicable is given.
R- (Required): The field is required.
RA- (Required if Applicable): The field is only required if it is relevant to
data being submitted.
O- (Optional): Field is not required but can be filled out if desired.
NA- (Not Applicable): Field is not relevant and should not be populated.
Default Value if Null
This column identifies the value that will be populated in the field if the field is left null or blank. In some cases, the default value changes based on the type of data being submitted. For example, the default value for Result Type Code field is TRG (Target) for all data except for Water Level and Flow data which has a default value of PM (Physical Measurement). Notations are made in this field in these cases. Fields that do not have a default value if left null will have a grey box.
SPECIAL CONSIDERATIONS WHEN COMPLETING THE EDD
Artesian Well Conditions
Occasionally, artesian conditions may be present at a well that is being sampled and surveyed for water level depth and elevation. When this is the case, obtaining a water level depth and elevation may or may not be completed. In the cases where the riser is extended so that the water level depth and elevation can be obtained, a new measuring point (reference) elevation must also be reported in the EDD since the reference point elevation will change with the extension of the well riser. In situations where the water level elevation or depth is not taken, the “concentration” field of the water level depth and/or elevation sample records in the EDD should be left null, “NAN” (not analyzed) should be populated in the Lab_Qualifier field, and a comment in the Lab_Comments field should be made to indicate that artesian conditions were present and no water level data was obtained.
Non-Detects and Zero Values
All non-detect data should be indicated by having a null in the Concentration field and a “U” (for non-detect) in the Lab_Qualifier or Validation_Qualifier field, whichever is applicable. For laboratory data, the Reporting Limit and MDL or IDL must be reported in the appropriate EDD fields. For field data taken using field instruments (such as turbidity, DO, and specific conductance meters) resulting in zero values, the concentration field should be left null and the Lab_Qualifier field populated with a “U” for non-detect. No reporting limit is required for these types of measurements. For data from field test kits such as spectrophotometers, color wheels, and titration test kits non-detects should be reported with a null Concentration field, and the lower range or limit of the test kit reported in the Reporting Limit field. Zero values in the concentration field are acceptable for the parameters of Temperature, Flow, and Landfill Gas Pressures & Flows.
QA/QC Data
Electronic data submitted to the ME DEP should meet the data requirements (QA/QC data included and level of data validation) set forth in the rules or the project QAPP
Surrogates: Surrogates should be reported in % recovery and identified with “SUR” (for surrogate) in the Result_Type_Code field. If also reporting the Acceptable Lower and Higher range limits, record each as a new sample record with a Result_Type_Code of “SC-Lower Limit”, and “SC-Higher Limit”, respectively.
Matrix Spike, Matrix Spike Duplicate, and Laboratory Control Samples: Matrix Spike, Matrix Spike Duplicate, and Laboratory Control Samples should be reported in % recovery and identified with “SC” (for spiked compound) in the Result_Type_Code field. If also reporting the Amount Spiked, Amount Recovered, and Acceptable Lower and Higher range limits, record each as a new sample record with a Result_Type_Code of “SC-Amount Spiked”, “SC-Recovered”, “SC-Lower Limit”, and “SC-Higher Limit”, respectively.
Blanks:
All blanks (Trip, Field, Equipment, Filter, etc) should be identified as such in the Sample_Type field according to appropriate Sample Type look-up value. The Sample_Type_Qualifier field should then be populated with “QC” (for quality control).
Field Duplicates:
Duplicates taken in the field should be identified as such by populating the Sample_Type_Qualifier field with “D” (for field duplicate). The Sample_Type should be the appropriate sample type of the sample (i.e. water, leachate, soil, etc.). No notations should be made in the Sample_Point_ID field that the sample is a duplicate (ex. MW-1-Dup, Dup2, or Duplicate should not be used). The Sample Point ID should have the same Sample Point ID as the original sample.
Lab Sample ID & DEP Sample ID
For laboratory data, the laboratory LIMS sample identifier should be reported in the Lab_Sample_ID field. The DEP stores the Lab Sample ID in order to be able to track and identify samples if there are questions or additional information is needed from the lab. For field samples and measurements, this field can be left blank. The DEP_Sample_ID field can be used to store a unique Sample ID assigned by either the DEP or an outside party that is independent of the Lab Sample ID.
Dilutions
Data from multiple dilution analyses should be combined into a single record. Data from original analyses and subsequent dilutions should be combined so that the lowest possible detection limit is reported for those parameters not requiring a dilution and only those parameters requiring a dilution are reported in place of the original results. Reporting Limits should reflect any dilutions used.
Reporting Limits, Method Detection Limits, and Instrument Detection Limits
The Reporting Limit (RL) and Method Detection Limit (MDL) or Instrument Detection Limit (IDL) must be reported for all laboratory samples identified as non-detect. RLs, MDLs, and IDLs must be reported in the same Units as the Parameter. The RL is generally equal to the laboratory Practical Quantitation Limit (PQL) which should be reported in the Reporting Limit field for most analyses. However, project sensitivity concerns may cause laboratories to report results to lower limits. When this is the case the specific required lower reporting limit should be reported in the Reporting Limit field, not the PQL. The lowest acceptable reporting limit in these cases is the laboratory method detection limit (MDL) or instrument detection limit (IDL).
Data Validation
Data validation results can be reported on the EDD using the following fields: Validation_Qualifiers, Validation_SQL, Validation_Level, Validation_Comment, and Validaiton_Comment_Type. At a minimum, the fields Validation_Qualifiers and Validation_Level must be filled out for all data validation results being reported. The remaining Validation fields should be used when applicable. All data validation must be a DEP pre-approved and acceptable validation procedure. If the validation procedure is either an EPA Tier II or Tier III validation procedure and has been pre-approved by the DEP, the concentration field may be left null when data validation qualifiers indicate non-detect (U or UJ) or rejected (R).
Non-Sample Data
On occasion samples are attempted to be collected (or measured for field data) but cannot be for a number of reasons (dry well or stream, inaccessibility, free product present, etc.). If desired, this type of information can be captured and reported using the EDD. For Non-Sample data the following fields are required: Poject/Site, Sample_Point_ID, Analysis_Lab, Sample_Date, Sample_Type, Sample_Type_Qualifier, and Sample_Collection_Method. The Sample_Location field is required if applicable and the fields DEP_Sample_ID, Sample_Comment are optional. Project/Site, Sample_Point_ID, Sample_Type, and Sample_Location should be filled out the same as any other sample would be. The Analysis_Lab field should be populated with “NA” for Not Applicable,
Sample_Date should be populated with the date sampling was attempted, Sample_Type_Qualifier should be populated with appropriate qualifier to describe why sample was not taken, and Sample_Collection_Method should be populated with “NS” for No Sample Taken. See also Data Element Dictionary.
Sample Taken but Not Analyzed
Sometimes samples are taken but cannot be analyzed due to the sample being “lost” as a result of broken glassware or instrument malfunction. If desired, this type of information can be captured and reported using the EDD. For each parameter that could not be analyzed for, the Lab_Qualifier field should be populated with “NAN” for Not Analyzed and the Lab_Comments field should be populated with a comment indicating why the sample could not be analyzed. The fields Concentration, Reporting_Limit, IDL, MDL, Validation_Qualifiers, Validation_SQL, Radiologic_Counting_Error, and Validation_Level should all be left null. All other fields should be filled out the same way as any other sample that was analyzed at a laboratory or in the field, whichever is applicable. See also Data Element Dictionary.
Samples Taken at Different Depths
If indicating that a sample is taken at a discrete depth, use the Sample_Depth and Sample_Depth_Unit fields (i.e. a sample collected at 3FT). Use the Sample_Depth_Interval_Top, Sample_Depth_Interval_Bottom, and Sample_Depth_Interval_Unit fields when a sample(s) is taken over a range or interval of depths (i.e. a sample collected at 3-6FT).
Test Methods & Prep Methods
When the Prep Method is within the Test Method used, the Prep_Method field should be left blank and only the Test_Method field should be populated.
Soil, Sediment, & Tissue Samples
For Soil, Sediment, and Tissue samples, indicate how the samples are being reported in the Wet_Basis field. The value must be either "Wet" for wet-weight basis or "Dry" for dry-weight basis. Reporting Limits should reflect the moisture content of the samples.
Sample Point ID Name Change
In cases where a Sample Point ID is identified by a property owner name (i.e. Smith well), please indicate any name changes due to property transfer in the deliverable package. All former Sample Point ID names are stored in EGAD.
Historical Data
Maine DEP understands that historical data may not always have all of the information and data that more recent sampling data includes and as a result may not follow all of guidelines and requirements of the Maine DEP EDD. If submitting historical data in which there is missing or unknown information and the requirements of EDDv5.0 cannot be met, please contact the appropriate EGAD Data Manager for your data submittal to work out how best to accommodate the historical data.
LUP Values & Tables
Use appropriate LUP Values from appropriate LUP Tables whenever it is indicated a field must be filled out according to a LUP Table in the Data Element Dictionary. Up-to-date LUP Tables and Values can be found on the EGAD Website at www.maine.gov/dep/rwm/egad. The LUP tables are updated frequently to reflect the most up to date LUP table values. Please contact the appropriate EGAD Data Manager if a lookup value needed is not included in a Table.
SUBMITTAL OF THE EDD
Format
After the data has been entered into the ME DEP EDD template it should be saved as an EXCEL worksheet or exported as a tab delimited text file. Please note that if a field is not required for the type of data being submitted, the columns which contain those fields may be deleted from the EDD before submission. Additionally, columns may be rearranged in any order, if desired.
Identifying
Each EDD submission should be identified by site name and sampling date, or site name and submission date if data for multiple sampling events is being submitted. Data for more than one site or project should not be combined into one EDD file for submission to ME DEP.
Sending to Maine DEP
EDDs should be submitted by e-mail, CD, or diskette. E-mailed EDDs should be e-mailed to dep.edd@maine.gov and EDDs submitted by mail should be addressed to the address at the end of the document. Both Excel worksheets and tab delimited text files are accepted.
Resubmitting
EDDs that are being resubmitted after modifications should be identified as resubmittals in the file name and/or the email or mail deliverable package.
TIPS ON PREPARING THE EDD
Common Problems
• Blank records (rows) within the data.
• Fields identified as Numeric format fields have non-numeric values (i.e. “<” signs in the concentration field).
• Dates and Times are not properly formatted with “/” and “:”.
• Values subject to a specific look-up table do not match the values contained in the look-up table
• Sample Collection Method field is blank
• Results identified as non-detects have a value in the Concentration field and do not have both Reporting Limit and MDL or IDL reported
• Data from original analyses and subsequent dilutions are not combined to report only the lowest possible detection limit for those parameters not requiring dilution with only parameters requiring dilution are reported in place of original results
• Prep Method is populated with the Test Method when the Prep Method is contained within the Test Method.
• All Required and Required if Applicable fields have not been populated when appropriate
• Duplicate Records present.
• Surrogates and Matrix Spikes/Matrix Spike Duplicates not reported in % recovery
• Static Depth Field contains sample interval data (i.e. 3-6FT)
Using the Macros
The Data_Element_Dictionary worksheet contains buttons that, when pressed, will run macros on the EDD worksheet to color code and hide EDD fields based on data type (Laboratory, Field, Water Level/Flow, Non-Sample, and Lost Sample). If desired, these macros can be used to aid in filling out an EDD with multiple data types by color coding to show which fields are required, required if applicable, or optional for each data type. Fields that are not applicable for a specific data type are hidden by the macro. Please note that not all users may be able to utilize and access these macros due to differences in software versions, firewall blocking, or security programs that may block the macros. EDD version 5.0 is available without macros and can be found on the EGAD website. Maine DEP offers these macros as a tool to aid users in the process of filling out the EDDs but does not make any guarantees on the effectiveness or performance of the macros and does not assume responsibility for programming errors that may be encountered.
CONTACTING EGAD DATA MANAGERS
For questions, concerns, and comments regarding Maine DEP EDD submittals please contact:
Bureau of Remediation & Waste Management (BRWM)
Data Submissions:
Erika Bonenfant - (207)-287-5767
BRWM EGAD Data Manager
Bureau of Land & Water Quality (BLWQ)
Data Submissions:
Seth Hubbard - (207) 287-5708
BLWQ EGAD Data Manager
DEP
28 Tyson Drive
17 State House Station
Augusta, ME 04333-0017
