ATTACHMENT 2 NOAA Query Manager Deliverable Format.pdf

C-1

NOAA/OR&R - Laboratory Electronic Deliverable Format

Laboratory analytical results and corresponding field data for the NOAA OR&R sampling events are stored in a relational database management system. The objective in acquiring laboratory data as electronic data deliverables (EDDs) is to minimize the manipulation required to incorporate the data into the relational database management system. Laboratories submitting electronic data should provide either Excel 2003 (or lower) files or ASCII tab delimited text files. Electronic files are due at the same time as the hard copy chemical reports. Table 1 below describes the fields required in the EDDs. The column labeled “Required?” indicates where the field must be completed.

A “Yes” indicates that the field must be completed for ALL records.

Table 1. Description of fields to be completed for laboratory electronic data deliverables (EDDs)

Field name Required? Description

Project name Yes Name given to the project as designated on the chain of custody: e.g., Kalamazoo River

Sample delivery group

Yes Identifier for the sample delivery group or quality control batch

Client sample ID Yes, for client samples and some

QC

samples

Identifier as provided on the sample label. For matrix QC, (MS and lab duplicates) this should be the parent sample ID (without MS or D suffix).

For non-project matrix QC (i.e., MS or LD performed on another client’s samples), “XXXXX” or other identifier may be used. For laboratory generated samples (i.e. blanks and LCS), field may be left blank.

Matrix Yes Sample matrix such as water, sediment, tissue, oil, tarball, etc.

Laboratory sample

ID

Yes Identifier as used by the laboratory. A unique identifier for each analysis date and time is preferred.

Date collected Yes Date the sample was collected. The date should be formatted as mm/dd/yyyy. Where the month and/or day is a value less than 10, a leading zero should be used (e.g., 06/09/2001). Where the sample is lab generated (e.g., method blank, etc.), use “NA” for the date collected.

C-2

Field name Required? Description

Date extracted Yes Date the sample was prepared or extracted in the laboratory. The date should be formatted as mm/dd/yyyy. Where the month and/or day is a value less than 10, a leading zero should be used (e.g., 06/09/2001). Where the sample is not extracted/prepared (e.g., Volatiles, etc.), use “NA” for the date extracted.

Date analyzed Yes Date the sample was analyzed. The date should be formatted as mm/dd/yyyy. Where the month and/or day is a value less than 10, a leading zero should be used (e.g., 06/09/2001).

Analytical method Yes Analytical method including laboratory SOP number, version and general instrumentation.

Preparation method

Yes Preparation method including laboratory SOP number, version and general description.

CAS number Optional CAS registry number Analyte name Yes Full chemical name Result Yes Measured result or Method detection limit if not detected Detection limit Yes Method detection limit (MDL) adjusted for dilution and varying sample sizes.

Reporting limit Yes Quantitation limit based on low standard adjusted for dilution and varying sample sizes.

Detect flag Yes Y for detections and N for non-detects Laboratory qualifiers

Yes, but nulls allowed

Qualifiers associated with the result. The laboratory should provide definitions of all qualifiers used in supplementary documentation such as additional text file or additional worksheet in an Excel file.

Analyte Type Yes TRG for target analytes, SUR for surrogates, SPK for spike compounds, IS for internal standards and TIC for tentatively identified compounds.

Dilution factor Yes 1 for undiluted samples; dilution factor for other results

Units Yes Units used for the concentration (e.g., mg/kg, mg/L).

Total or dissolved Yes for aqueous samples

For aqueous samples, indicate “tot” for total results and “dis” for dissolved fractions.

Measurement basis

Yes Measurement basis for the result. Should be “dry” or “wet”.

Sample type Yes Indicator of sample type. See Table 2 for a list of appropriate sample types.

C-3

Field name Required? Description

Instrument ID Optional A unique identifier specifying the Instrument the analysis was performed on.

Column ID Optional An identifier specifying the column used (primarily for GC methods)

SpikeConc Yes, where applicable for QC samples

For spiked samples and surrogates, the concentration of the analyte added. For SRMs/CRMs or standards, the true value.

Percent Recovery Yes, where applicable for QC samples

For spike samples and surrogates, the percentage recovery achieved.

RPD Yes, where applicable for QC samples

For duplicates the relative percent difference.

For multiple replicates, the relative standard deviation.

Lab name Yes Laboratory name or suitable short form name.

Table 2. Codes to use in Sample type field of EDD

Sample Type Code Description Samples and standard QC SMP Sample submitted by field team LD Laboratory duplicate result (add number if there is more than one duplicate result e.g., LD, LD2, etc.)

MS Matrix spike MSD Matrix spike duplicate SRM Standard reference material MB Method blank or preparation blank LCS Laboratory control sample LCD Laboratory control sample duplicate Calibration and instrument QC (if supplied) CAL Calibration standards (initial, continuing or other) CB Calibration or instrument blanks PS Post Spike SRD Serial Dilution ICS Interference Check Sample

Additional information concerning the format and content of the electronic data deliverables is provided below:

1) Each row of data should contain only one analyte for a given sample. Therefore, one complete sample will require multiple rows.

C-4

2) Each row should contain the following information as a minimum: date of sample analysis, laboratory method, analyte name, measured result, reporting limit, detection limit, laboratory qualifiers, units, measurement basis (wet or dry), sample type, sample delivery group (SDG) or quality control batch, and lab name. All samples submitted by the client must also include the client sample ID.

3) If a spreadsheet program is used to produce the electronic deliverable, the values representing the measured concentration and reporting limit should be limited to the appropriate number of significant figures (i.e, there should be no trailing digits to the right of the decimal that express greater precision than is appropriate). Number formats used in Excel should not hide digits.

4) Laboratory samples for quality assurance/quality control should be clearly identified in the file (using the sample type designation as described above) and each should have a unique laboratory sample identifier.

5) If replicate analyses are conducted on a submitted sample, the laboratory sample identifier must be different on each of the replicates. The original analysis will be designated in the sample type field as “SMP”. The second analysis will be designated as “LD” in the sample type field. If additional analyses of the sample are completed, a number should be appended to the end of the “LD” code (e.g., LD, LD2, LD3, etc.).

6) An additional text file or worksheet in an Excel file should be provided with supplementary explanations for the data provided. This file or worksheet should contain the following information:

- Name of laboratory

- Sample delivery group (SDG) or batch number

- Date on transmittal letter or case narrative of the hard copy report

- Name and number of laboratory contact

- Tabular summary of qualifiers used in the EDD and their definitions

- A narrative of any missing data or limitations to the use or interpretation of the data

- A list of any new sample type codes or analyte types that were used and their definitions