submitdaHazSite Tables and Fields for the Electronic Data Submittal Application-Version 7 (EDSA7)

This document explains EDSA7 on a field-by-field basis. New fields or changes to the fields have been highlighted in Bold text. The component tables and fields are detailed below. By way of a general discussion of the tables and fields, it should be noted that EDSA7 (like all prior versions) reviews tabular data by field position. You will note that the first column in the description of the tables (below) contains the field position. Because EDSA programs check data by column position, omitting a column can often result in thousands of errors! It is critically important that all columns be retained in each table, even if no data are to be stored in the column. While NJDEP attempted to use simple, meaningful names for the columns, many end-users have had (and continue to have) misconceptions about some of the columns. We have attempted to clarify names in the EDSA error messages as well as in this webpage.

Table Definitions

Different colored rows correspond to different requirements. See the footnotes of each table for more details.

DTST

Position Field Name Aka Name Field Type Length Decimals Required
11/14/2012 and before
Required
11/15/2012 and after
1 Directory - C 8    Y Y
2 Desc - C 40    Y Y
3 SRPID - C 24    Y Y
4 Consultant - C 40    Y Y
5 Phase - C 12      
6 Status - C 10      
7 Transmit - C 1      
8 Submitdate - D 8    Y Y
9 Packnum - N 2 0    
10 Contactnam - C 60   didn't exist Y
11 Contacttel - N 10 0 didn't exist Y
12 Contactext - N 10 0 didn't exist  
13 Contactema - C 254   didn't exist Y

Back to Top


HZSAMPLE

Position Field Name Aka Name Field Type Length Decimals Required
11/14/2012 and before
Required
11/15/2012 and after
1 SRPID - C 24      
2 Sampdate - D 8    Y Y
3 Sampnum - C 50    Y Y
4 Samptime - C 25      
5 DupSamp SampDup C 1    Y Y
6 Matrix SampMatrix C 15    Y Y
7 Fieldid FieldLocId C 20    Y Y
8 Aocid - C 60      
9 Lat_degree - N 10 7 1 1
10 Lat_minute - N 8 5 1 1
11 Lat_second - N 6 3 1 1
12 Lon_degree - N 10 7 1 1
13 Lon_minute - N 8 5 1 1
14 Lon_second - N 6 3 1 1
15 Sp_x - C 14   1 1
16 Sp_y - C 14   1 1
17 Depth_top Depth_Samp_Top C 6   2 2
18 Depth_botm Depth_Samp_Botm C 6     2
19 GroundElev - C 6      
20 Well_elev WellElev C 6      
21 Samptype SamLocType C 15    Y Y
22 Datetolab - D 8    Y Y
23 Sampmethod - C 15      
24 Sampnote - C 254   5 5
25 Submitdate - D 8      
26 Qaqc - C 15      
27 Coordmeth - C 15   didn't exist 1
28 Coordnote - C 10   didn't exist  
29 GWDepthPri DepToGwPrior C 7   didn't exist 6
30 GWDepthPos DepToGwPost C 7   didn't exist 6
31 Screentop ScreenTopDepth C 7   didn't exist 7
32 Screenbot ScreenBotDepth C 7   didn't exist 7
33 Wellpermit - C 10   didn't exist 8
34 Srp_dir - C 8   didn't exist  
35 Samplabid LABID C 20   didn't exist  

Back to Top


HZRESULT

Position Field Name Aka Name Field Type Length Decimals Required
11/14/2012 and before
Required
11/15/2012 and after
1 SRPID - C 24      
2 Sampdate - D 8   Y Y
3 Sampnum - C 50   Y Y
4 Labid Samplabid C 20   Y
5 Tdanalyz Danalyz C 20   Y Y
6 Labname - C 20     9
7 Njdlabcert - C 7   Y Y
8 Resulttype - C 1   Y Y
9 Analtparam - C 60   Y Y
10 Cas - C 15   Y Y
11 Filtunfilt FieldFilt C 1     Y
12 Conc - C 12   Y Y
13 Concunits - C 15   Y Y
14 Qaqual - C 16     10 & 11
15 Mdl - C 12   14 3
16 Quanttype - C 8   14 3
17 Quantlevel - C 12   14 3
18 Anlys_mthd AnlysMethd C 35   Y Y
19 QAQC QAQC_SDG C 15   4 4
20 Uncor_conc - C 12   4 4
21 Uncor_unit - C 15   4 4
22 Reten_time - C 8   didn't exist 12
23 Dilut_fac - C 12   didn't exist 13
24 Prep_mthd - C 35   didn't exist 10
25 Clnup_mthd - C 35   didn't exist 10

Back to Top


Field Descriptions

DTST

HZSAMPLE

HZRESULT

Back to Top


Valid Values

PHASE

PI Preliminary Investigation
SI Site Investigation
RI Remedial Investigatioin
RA Remedial Action
RAO Remedial Action Outcome
MP Monitoring Permit

MATRIX

SOIL
SURFACE WATER
GROUND WATER
AIR
SEDIMENT
OTHER
SOLID
WASTE
BLANK
QC WATER
QC AIR
SOIL_GAS
RINSATE
WIPE
PRODUCT
QC SOIL
ELUTRIATE
TCLP WASTE
TCLP SOIL
SPLP SOIL
TCLP SEDIMENT
SPLP SEDIMENT

SAMPTYPE

AIR STRIPPER
AMBIENT
BACKGROUND
BLANK
BLANK - AMB AIR
BLANK - FIELD
BLANK - TRIP
BUILDING FLOOR
BUILDING WALL
CHIP
DEBRIS
DRUM
EFFLUENT
FLOWING WATER
INFLUENT
INJECTION WELL
INTERIOR AIR
LEACHATE
MONITOR WELL
NEAR SLAB
OTHER
POTABLE WELL
RAD SAMPLE
SANITARY SEWER
SEDIMENT
SEPTIC SYSTEM
SLUDGE
SOILGASEXTERIOR
STANDING WATER
STORM SEWER
SUBSLAB
SUBSURFACE SOIL
SUMP BASEMENT
SUMP EXCAVATION
SURFACE SOIL
TCLP
TEMP PILE
TEMP WELL
TEST PIT
WIPE

RESULTTYPE

Code Description Definition
A Analyte Refers to one of a group of targeted compounds that share similar characteristics (e.g. Volatile Organics) that can be anticipated to show up in a sample. Calibration of the laboratory instrument, using the target analyte or a compound with similar properties (a surrogate), allows for accurate measurement of the concentration of the compound and a threshold of detection (the Reporting Limit) below which the substance cannot be measured with the same level of accuracy.
P Parameter Refers to characteristics of a sample (e.g. pH, Dissolved Oxygen, Alkalinity) or to classes or summations of contaminants (e.g. total Phenolics) or to radioactive characteristics (e.g. Gross alpha) or to various fuel oils (e.g. #2 Fuel Oil).
T Tentatively Identified Compound (TIC) A substance detected from a sample, that when measured against a "Library" of known substances exhibits characteristics similar to the known substance. It is reflected in terms of probability of matching with one (or more of the) Library substance(s). Since it was not expected in the list of targeted compounds, no calibration standards were used to measure specifically for the compound and no Reporting Limit can be assigned.
S Surrogate A Quality Assurance/Quality Control (Q/QC) substance used for measuring the performance of the analytical instrument that is added to the sample during analysis.
I Internal Standard It describes a Q/QC substance used for measuring the performance of the analytical instrument that is added to the sample during analysis.
L Laboratory Generated Quality Control Sample Use for any laboratory-generated quality control sample that measures the performance of an analytical instrument. Which will distinguish these samples from an environmental contaminant.

FILTUNFILT

U Unfiltered
F Filtered

QAQUAL

Organic

U Indicates the compound was analyzed for but not detected. The sample method detection limit should be corrected for dilution and percentage moisture where required by the specific analytical method
J Indicates an estimated value. Use this flag under the following circumstances:
  1. When estimating the concentration for a tentatively identified compound (TIC) where a 1:1 response ratio is assumed, OR
  2. When the mass spectral and retention time data indicate the presence of a compound that meets volatile and/or semi-volatile GC/MS identification criteria, and the result is less than the method detection limit but greater than zero, OR
  3. When the retention time data indicates the presence of a compound that meets the pesticide/Aroclor criteria and the result is less than the method detection limit but greater than zero.

NOTE: The "J" reporting flag shall not be used, and the compound not reported as identified for pesticide/Aroclor results less than the method detection limit, if the technical judgment of the pesticide residue analysis specialist determines that the peak used for compound identification is from instrument noise or other interferences. Use the sample method detection limit corrected for dilution and percent moisture where required by the specific analytical method.

N Indicates presumptive evidence of a compound. Use only for tentatively identified compounds, where the identification is based on a mass spectral library search. Apply to all TIC results. Do not use for generic characterizations, such as "unknown chlorinated hydrocarbon."
P Use for pesticide/Aroclor target analytes with greater than 25% difference for detected concentrations between the two GC columns. Report the lower of the two values and flag with this code.
C Use for pesticide identification confirmed by GC/MS analysis. If the attempted confirmation is unsuccessful, do not use this flag. Use another flag defined by your laboratory for explanations.
B Use if the analyte is found in the blank as well as the sample. It indicates probable blank contamination. It warns the data user to take appropriate actions. Use for both positively identified and tentatively identified target compounds.
E Use for identification of compounds with concentrations exceeding the GC/MS calibration range for that specific analysis. Dilute the sample if one or more of the compounds has a response greater than full scale, and reanalyze. Flag such compounds with "E." If the dilution of the extract caused any compound identified in the first analysis to fall below the calibration range in the second analysis, flag the results for the second analysis "D." Affix the "DL" suffix to the sample number of the diluted sample and report both analyses.
D Use for identification of compounds in an analysis at a secondary dilution factor. Flag if a sample or extract is reanalyzed at a higher dilution factor. Flag the reanalyzed sample or extract with "DL." This alerts the user that there are discrepancies between reported concentrations possibly due to the dilution.
A Indicates that the tentatively identified compound is a suspected aldol condensation product.

Inorganic

E The reported value is estimated because of interference. Include an explanatory note in the nonconformance summary if the problem applies to all the samples, or in the individual form if it is an isolated problem.
M Duplicate injection precision not met.
N Spiked sample recovery not within control limits.
S Reported value determined by the "Method of Standard Additions" (MSA).
W Post digestion spike for Furnace AA analysis not within control limits, absorbence is less than 50% of the spike absorbence.
* Duplicate analysis not within control limits.
X Ion chromatographic peaks outside the 5% acceptance window.
+ Correlation coefficient for the MSA is less than 0.995.

QUANTTYPE

Type Description
PQL Practical Quantitation Level
CRQL Contract Required Quantitation Level
CRDL Contract Required Detection Limit
MDA Minimum Detectable Activity (Radiochemistry)
IDL Instrument Detection Limit
RL Reporting Limit
RL-PPBV Reporting Limit parts per billion volume
RL-UG/M3 Reporting Limit micrograms per cubic meter
LOD Limit of Detection
LOQ Limit of Quantitation
EQL Estimated Quantitation Limit
MQL Minimum Quantitation Limit
SDL Sample Detection Limit
SQL Sample Quantitation Limit

 

Back to Top


General Errors

Primary Key Fields

More details on SAMPNUM

The Primary Key is defined as one or more fields which, taken together, can be used to identify a single unique row in the Parent Table. The Parent Table in this case is the HZSAMPLE table. It contains information about samples collected in the field. A Child Table contains multiple rows (a One-to-Many relationship) of data that are related to the Parent Table. In this case, the HZRESULT table is the child table because it has many analytical results (one per row) that are related to a single row in the HZSAMPLE table. In the HZRESULT table, duplicate records include ANALTPARAM as a part of the primary key to the HZRESULT table. This means that results having more than one unique result row for a single SAMPNUM are counted as duplicates and an error counted for each row. A final requirement for key fields is that they cannot be empty (null).

Orphan Records: "Orphan Records" are records that have information in the Child Table with no matching records in the Parent Table. For instance, in the example in Figure 1 below, if any one of the three highlighted key fields were changed in HZSAMPLE (e.g. if SAMPDATE was changed to 2/28/2008) all of the related HZRESULT records would become "orphans" because they no longer correspond to the "parent" record. For more information about Parent and Child records see the section on the Primary Keys

No Results (Orphans) Error Message: No result records found for sample.

Explanation: Records in HZRESULT exist with no match in HZSAMPLE. This can be caused by a mismatch in the SAMPNUM or SAMPDATE fields between the two tables. If they do not match, Childless records (in HZSAMPLE) or Orphan records (in HZRESULT) may be found.

SRPID Character 24
SAMPDATE Date 8
SAMPNUM Character 50

The Electronic Data Submittal Application (EDSA) uses a three-field Primary Key: SRPID + SAMPDATE (as a character expression of the date) + SAMPNUM. These three fields exist in both the HZSAMPLE and the HZRESULT tables.

Figure 1:

Back to Top


Error Message: Example: This SampLabId (new required field in HZSAMPLE that is related to LABID in HZRESULT) associated with multiple samples: 02/25/2008 195770 AND 02/25/2008 195771

Error Message: Analyte or parameter duplicates another that appears in 1 record(s) above. Each duplicate analysis of a sample requires a unique SAMPNUM.

Explanation: Please note (for the second error message) that there are 2 sets of the same analytes, Benzene, Ethylbenzene, Toluene and total Xylenes (with Benzene highlighted in red) for SAMPNUM = 1; The primary key uses SAMPDATE and SAMPNUM, therefore the two records are "duplicates". See Primary Key

Corrected File; Duplicates remedied:


Back to Top


Error Message: Table structure problem. Missing at least 1 expected field.

Explanation: Missing columns will cause each succeeding column to be evaluated according to the rules for the column to its immediate left in the table. This will normally result in hundreds or even thousands of errors. If no data exist for a particular column (e.g. AOCID), leave the column in the table and input no data to that column.

Back to Top


Error Message: In DAO.Workspace err3274 - External table isn't in the expected format. Format differs from what file extension indicates. Opening file with Excel may help to confirm

Explanation: This error message occurs when one or more of the fields in HZSAMPLE or HZRESULT have are incorrectly structured or not in the proper order. Please check the table structure listed below to ensure the each field has the correct Field Name, is of the right type and that it conforms to the required size.

Back to Top


Error Message: An entire record of table is empty.

Explanation: EDSA encountered a row within a table (HZSAMPLE or HZRESULT) that contains ALL EMPTY FIELDS.

Back to Top


Error Message: Empty or corrupted table? RecordCount= 0 EOF= True.

Explanation: EDSA has encountered an entire table that has ALL BLANK ROWS (no data).

Back to Top


CHEMICAL NAME AND CAS NUMBER

CAS Lookup

Error Message Examples:

Error 3_08_A Example: NJDLabCert (AKA Lab Cert Num)= 12543; Anlys_Mthd= 200.7; ResultType= A; AnaltParam= COBALT; CAS= 1330-20-7; FiltUnfilt= WARNING 3_08_A Inconsistent name: This table reports different AnaltParam: CHROMIUM with same NJDLabCert (AKA Lab Cert Num), ResultType, CAS, FiltUnfilt, Anlys_Mthd

Explanation: The error above is an example of the same CAS number being used for both Cobalt and Chromium. This is an obvious error. NJDEP does not pick out the correct analyte based on CAS because it is not possible to tell whether the error was based on the wrong name and right CAS number or vice-versa.

Back to Top


Error Message: AnaltParam= Potassium; CAS= 9/7/7440; Anlys_Mthd= 200.7

Explanation: Excel converts Potassium's CAS # 7440-09-7 to a date format. If the cell in Microsoft's Excel program is set to its default value of "General" data, this CAS number is converted to a date format 9/7/7440. Other variants of the conversion may be 9/7/40 (which evaluates to 9/7/1940) or to 2023695, which is the serial date format.

How do I fix this error? The best remedy for this error is to set the default number format for all cells to "@" or "Text" on the "Style" sub-menu of the "Format" menu of Excel. Select the column of cells to format, then select Format -->> Style from the Excel menu. Choose Modify and modify the Number format to: Custom -->> @.

Back to Top


Error Message: All 9 nonblank samples share one coordinate location

Explanation: Each unique location must have a unique X and Y coordinate. A unique location is defined by the same FIELDID + X-coordinate + Y-coordinate for each distinct location that was collected in the field. There is now an absolute requirement for distinct X and Y locations that are submitted in either Latitude and Longitude or State Plane X and Y coordinates. Where both are submitted, they must agree as to the actual location of the sample. For further explanation, see FIELDID XY explanation

Back to Top


Date errors

Error Message: Required DTST.SUBMITDATE is empty. EDSA attempts to check EDD without it.

Explanation: EDSA7 recognizes that the date of submission was omitted, but will check HZSAMPLE and HZRESULT for errors and regard the omission as a warning.

Back to Top


Error Message: SUBMITDATE, DATETOLAB, SAMPDATE later than file datetimestamp.

Explanation: The SUBMITDATE in HZSAMPLE should be earlier than the date stamp that indicates when the file was last modified (i.e. the file cannot be created before the sampling was conducted).

Back to Top


Error Message: SUBMITDATE, DATETOLAB, SAMPDATE [or TDANALYZ] later than today's date

Explanation: The date of sampling must be earlier than today's date.Each of these dates should be no later than the date when the checker is running. If one is later, please correct any typographical error in the date.

Back to Top


Error Message: SUBMITDATE, DATETOLAB, SAMPDATE or TDANALYZ earlier than 1950.

Explanation: The Site Remediation Program's Data requirements mostly began in the 1980's (1970's in some cases). Electronic Data Deliverable have been required since July 18, 1997. Submissions pre-dating these dates cannot be correct and may cause problems in the correct evaluation of prograss in remediating a site, therefore, such dates will not be accepted.

If this error is found in the DTST.SUBMITDATE or HZSAMPLE.SUBMITDATE fields, it may be a problem that is inherent in the Lotus 123 format, which defaults all dates to the 1900's; dBase (DBF) files may also cause such errors because there is a system variable (CENTURY) that must be set to "ON" to prevent DBF's from defaulting to the 1900's.

Back to Top


Error Message:

Explanation: The date order is checked, events happen in a specific chronological order.

Order Table Field Explanation
1 HZSAMPLE SAMPDATE Date of sampling (primary key field)
1 HZRESULT SAMPDATE Date of sampling (primary key field)
2 HZSAMPLE DATETOLAB Date samples were delivered to lab
3 HZRESULT DANALYZ Date lab analyzed samples
4   timestamp Date file was created
5 DTST SUBMITDATE Date EDD was submitted to NJDEP

Back to Top


Error Message: DANALYZ Earlier than SampDate in HZRESULT record OR DANALYZ Later than SubmitDate in DTST table

Explanation: The main error associated with the Date Analyzed Field is that this date cannot be earlier than either the sample date (SAMPDATE) or the Submission date (SUBMITDATE).

Error Message: DATETOLAB earlier than SAMPDATE

Error Message: SAMPDATE later than DATETOLAB

Explanation: Sampling date must occur before sending the samples to the lab.

Back to Top


DTST

HZSAMPLE

HZRESULT

Back to Top