Linking Alive Samples to Observations

<< Click to Display Table of Contents >>

Navigation:  Alive >

Linking Alive Samples to Observations

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.

 

alive-survey_results_typeii_report_zoom60