Chemistry
Chemistry data is stored primarily in the Chemistry Samples and Chemistry Results tables.
A Chemistry Sample is typically associated with a Location and, if relevant, a Well. There will often be multiple Chemistry Samples associated with a single Location (and Well if relevant).
Chemistry Samples may also be associated with a Lab Sample Delivery Group and a Laboratory Report. Chemistry Samples are also often associated with a Monitoring Round, which can combine multiple Laboratory Reports as well as Field Data.
Chemistry Results are connected to a Chemistry Sample by having a matching SampleCode, which is unique per sample within a Project. There will typically be multiple Chemistry Results associated with a single Chemistry Sample.
Lab Reports
PK | Column | Type | Description |
| PID | Integer | References Projects.ID - a unique integer for each project. |
* | Lab_Report_Number | Text(25) | The Identifier for a Lab Report, as assigned by the Laboratory |
* | Lab_Name | Text(40) |
|
| Project_ID | Text(20) | Unrelated to the core Project_ID in the system - a text field reporting the Project value used by the Lab. |
| Date_Reported | DateTime |
|
| Project_Name | Text(255) | Unrelated to the core Project_ID in the system - a text field reporting the Project value used by the Lab. |
| Lab_Signatory | Text(50) | Person at the lab who signed off on the results. |
| Report_Recipient1 | Text(255) | Name of first person who is a recipient of the Lab Report |
| Report_Recipient2 | Text(255) |
|
| Report_Recipient3 | Text(255) |
|
| Documents_ID | Integer | An integer that refers to a document in the Documents table (not editable) |
Reporting_Status | Text(255) | Typically Pending, Draft or Final. Used when preliminary reports may be issued by the laboratory. | |
| Accepted | True/False | If the lab report is accepted |
| Rejected | True/False |
|
| Accepted_By | Text(40) | Person who accepted the Lab Report. |
| Accepted_Date | DateTime |
|
| Acceptance_Comments | Text(255) |
|
Comments | Text(500) | General comment in relation to the lab report or associated data, as distinct from Acceptance Comments. | |
Primary Recipient Org | Text(255) | The organization that the lab report was issued to. |
Chemistry Samples
PK | Column | Type | Description |
| SID | Integer | References Sites.ID - a unique integer for each Site. |
* | PID | Integer | References Projects.ID - a unique integer for each project. |
* | SampleCode | Text(40) | A unique code for each sample within each Project |
| Field_ID | Text(50) | The ID given to the Sample in the field. |
| Sampled_Date_Time | DateTime | The Date/Time at which the sample was collected. |
| Location_Code | Text(20) | References a Location in the Locations Table. |
| Well | Text(10) | References a Well in the Wells table. |
| Sample_Depth_From | Numeric | Top depth at which the sample was taken. |
| Sample_Depth_To | Numeric | Bottom depth at which the sample was taken |
| Matrix_Type | Text(14) | Water, Soil, Sediment etc.. Available as a drop-down of valid values in the system. |
| Sample_Type | Text(22) | List of: |
| Parent_Sample | Text(40) | Used for Duplicate samples, populated with the SampleCode of the primary Sample. |
| SampleComments | Text(255) | Comments related to the Sample as entered by the data reviewer. |
| Matrix_Description | Text(255) | A description of the Sample Matrix, such as a soil description. |
| Sampled_By | Text(50) |
|
| Sampling_Method | Text(20) |
|
| Monitoring_Round | Text(30) | Name of each monitoring round, makes extracting data that is obtained in one round over multiple dates easier. |
| SDG | Text(50) | Sample Delivery Group - a batch of samples received at a laboratory. Often the COC Number is used to identify the SDG. |
| Lab_Name | Text(40) | If either Lab Name or Lab Report Number is populated they must both be populated. |
| Lab_Report_Number | Text(25) | Lab Report Number as issued by the Laboratory. |
| Lab_SampleID | Text(20) | Sample Identifier as used within the lab. |
| Lab_Comments | Text(255) | Comments related to the Sample as provided by the lab. |
| Odour_Ranking | Text(30) | Field Description of any odor related to the sample |
| Env_Stds_Conditional_Matrix_Type | Text(14) | A specific matrix type that applies to this sample for the purposes of comparing with conditional environmental standards. |
| Location Visit | Integer | References Location_Visits.ID - a unique integer for each Location Visit. Relevant only for field samples collected in the ESdat Field Portal / App. |
Equipment_ID | Text(40) | Identifier for equipment used in the sampling | |
Equipment_ID2 | Text(40) | Identifier for equipment used in the sampling | |
Field_Sample | Text(40) | Used for lab samples, populated with the SampleCode of the corresponding Field Sample. |
Chemistry Results
PK | Column | Type | Description |
* | PID | Integer | References Projects.ID - a unique integer for each project. |
* | SampleCode | Text(40) | A unique code for each sample within each Project. It must match a value in the Chemistry Samples table for the same Project. |
* | ChemCode | Text(20) | A unique code (CAS Number where possible) that identifies each compound |
| OriginalChemName | Text(70) | The name of the analyte in the original source data. Note, in ESdat the name used for outputs for each ChemCode can be configured and standardised in the Chemistry Profiles. |
| Prefix | Text(3) | < (for non-detects) or > (for greater than) |
| Result | Numeric | Numeric Results |
Text_Result | Text(255) | For use when a result is not numeric. | |
| Result_Unit | Text(15) | Units a Numeric Result is reported in. |
* | Total_or_Filtered | Text(1) | T or F to indicate if a Sample is filtered in the field. |
* | Result_Type | Text(20) | One of the following values: Reg(regular result) Calc (calculated result) leached_REG (regular leached results) SUR (Surrogate) leached_SUR (leached surrogate) SC (Spike Compound) SC (Spike Compound Duplicate) |
| Method_Type | Text(50) | The type of method, typically matches requests on COC forms. |
* | Method_Name | Text(90) | The method used to obtain the result, used for laboratory results |
| Extraction_Date | DateTime | Date/Time the extract was obtained from the samples, for lab results. |
Extraction_Method | Text(255) | Lab Method used for extraction prior to analysis | |
| Analysed_Date | DateTime | Date the results were analysed if lab data. |
| EQL | Numeric | Estimated Quantitation Limit reported in the units specified in the Detection Limit Units column. |
MDL | Numeric | Method Detection Limit | |
RDL | Numeric | Reportable Detection Limit | |
ODL | Numeric | Other Detection Limit, for use if required in contract or client specific requirements such as the US Defense “Limit of Detection”. | |
| Detection Limit Units | Text(15) | Units the above Detection Limits are reported in. |
| Lab_Comments | Text(500) | Comments specific to a result as provided by the Laboratory. |
| Lab_Qualifier | Text(20) |
|
| UCL | Numeric | Upper Confidence Limit, for lab data QA |
| LCL | Numeric | Lower Confidence Limit, for lab data QA |
Dilution_Factor | Numeric | Populated during the import of Lab Reports to indicate sample dilution prior to analysis. | |
Spike_Concentration | Numeric | Lab QA Spike Concentration | |
Spike Measurement | Numeric | Lab QA Spike Measurement | |
Spike Units | Text(15) | Units for the Spike Concentration and Measurement. | |
Lab_Preperation_Batch_ID | Text(255) | A lab identifier for samples included in a preperation batch | |
Lab_Analysis_Batch_ID | Text(255) | A lab identifier for samples included in an analysis batch | |
* | Lab_Analysis_ID | Text(255) | A lab identifier for a specific analysis. |
| Validation_Comments | Text(500) | Comments from the person who has validated the data. |
| Validation_Qualifier | Text(5) | Qualification code from the person who has validated the data. |
| Final_Comments | Text(500) | The Comments to be presented in the final report. |
| Final_Qualifier | Text(5) | The Qualifier to be presented in the final report. |