Linking Alive Samples to Observations

•Reporting Resources

Samples and observations can be related in a few different ways. In some scenarios, multiple observations are combined into a single sample (e.g., catching and observing individual small fish then grouping them together into a single sample). In other scenarios, a single observation may be divided into multiple samples, while others still may have a simple one-to-one relationship.

EQuIS Alive handles each of these scenarios by employing the AT_SAMPLE_OBSERV table as denoted below:

AT_SAMPLE_OBSERV Field	Description
FACILITY_ID	The key that links to DT_FACILITY
SAMPLE_ID	The key that links to DT_SAMPLE
SURVEY_OBSERV_ID	The key that links to DT_SURVEY_OBSERV
EBATCH	For tracking additions made through EDP
EUID	Database unique identifier

Each record in this table points to a sample and an observation. This allows for one-to-many, many-to-one, one-to-one, and many-to-many relationships.

In some cases, a sample code is generated on the spot while taking the observations. In this scenario, the sample code can be stored in the OBSERV_SAMPLE_CODE field in the DT_SURVEY_OBSERV table.

Once the sample data has been loaded into EQuIS and assigned a SAMPLE_ID, the following SQL script can be run to populate the AT_SAMPLE_OBSERV table:

INSERT INTO at_sample_observ (facility_id, sample_id, survey_observ_id)
SELECT ds.facility_id, ds.sample_id, survey_observ_id
FROM dt_survey_observ so join dt_sample ds on so.observ_sample_code = ds.sys_sample_code
WHERE not exists (select * from at_sample_observ a where a.facility_id = ds.facility_id and a.sample_id = ds.sample_id and a.survey_observ_id = so.survey_observ_id)

Reporting Resources

The following reporting resources enable the association of analytical and Alive data.

Analytical Survey Results Report

The Analytical Survey Results report shows Alive and Analytical (sample/test/result) data.

In addition to joining by sample, analytical and Alive data associations can be joined by survey subfacility and sample location in the Analytical Survey Results report. The EQuIS Schema association is through DT_LOCATION.SYS_SAMPLE_CODE and DT_SURVEY.SUBFACILITY_CODE. The following SQL script should be run to populate the AT_SUBFACILITY_LOCATION table:

INSERT INTO at_subfacility_location (facility_id, sys_loc_code,subfacility_code)
SELECT l.facility_id, l.sys_loc_code, l.subfacility_code
FROM dt_location l
INNER JOIN dt_subfacility s ON l.facility_id = s.facility_id AND l.subfacility_code = s.subfacility_code
LEFT JOIN at_subfacility_location a ON l.facility_id = a.facility_id AND l.sys_loc_code = a.sys_loc_code AND l.subfacility_code = a.subfacility_code
WHERE a.subfacility_code IS NULL;

Alive_Analytical-Survey-Report-Location

Survey Results (By Survey Type) II Report

The Survey Results (By Survey Type) II report shows the associated SYS_SAMPLE_CODE if this is added from the Additional Fields parameter DT_SAMPLE table. Also this report includes the DT_SURVEY_OBSERV.OBSERV_SAMPLE_CODE field in the output.