Linking Alive Samples to Observations

<< Click to Display Table of Contents >>

Navigation:  Alive >

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:





The key that links to DT_FACILITY


The key that links to DT_SAMPLE


The key that links to DT_SURVEY_OBSERV


For tracking additions made through EDP


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;





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.