Object Group Descriptions

Group Description

Linked Tables Tables that are linked from external database to bring in the source data. 

These tables are kept external to this database to minimize the size of this 

database and to protect against accidental editing of source data.

Initial Processing A set of queries used to edit selected source data sets and get some fields in 

shape for further processing. Also has primary query for linking the source 

data together

Lookup Tables A set of lookup tables that contain parameter translations as well as 

benchmark or ELG values for analysis as used herein

Benchmark Data Prep Queries that extract the data to be analyzed for the benchmark monitoring 

program analysis. The results of these queries are exported to excel for 

further manipulations that are beyond simple SQL operations.

Benchmark Data Analysis After the external manipulations are performed in excel, the yearly means 

are brought back into access for further manipulations and reporting 

summaries.

ELG Data Analysis Series of queries used to select and analyze ELG data. Reporting summaries 

are included.

Object Descriptions

Group Object Grou

p Sort

Order 

Sort

Description Date

Linked Tables PT_STATUS 1 1 PT_STATUS (n=669). Unique list of facilities. PK = PT_ID. 

Indicator of facility status. Will eventually screen for PT_STATUS 

= ACTIVE.

9/11/2012

Linked Tables RPT_FACILITY 1 2 RPT_FACILITY (n=5,684). Facility report table. PK = REPORT_ID. 

Relates back to PT_STATUS by PT_ID.

9/11/2012

Linked Tables RPT_MONITORING 1 3 RPT_MONITORING (n=42,351). Reported monitoring data. 

ERPT_DISCHARGE_LOCATION_ID appears to correspond to a 

unique key for OUTFALL. MONITORING_TYPES has several fields 

9/11/2012
(important values for this task include “QBM”, “ELG”, and 

“QBM, ELG”). PARAMETER_NAME needs to be unified. 

RESULT_DESC is either “Detected”, “Not Detected”, “Not 

Applicable”, or “Detected but below QL”. SAMPLE_VALUE is 

either a numeric value or null; however, it is not consistently 

null or filled in with respect to RESULT_DESC. Thus, it’s not 

possible to use MLE techniques to estimate the yearly mean.

Linked Tables RPT_DISCHARGE 1 4 RPT_DISCHARGE (n=3,968) Table has lots of duplicative records. 

If we ignore “REPORT_ID” and “PERIOD_CHECKED_YN” we can 

then use ERPT_MONITOR_PERIOD_ID as a primary key (with an 

effective n =2,688).

9/11/2012

Linked Tables PT_MONITORING_REQ 1 5 N=1,154. Links permits to SIC code and sector/subsector codes. 

It was found that the original Sector column was not always 

consistent with the sector value from the Subsector column. (So 

later a query is developed to rectify this.)

(Note, that I accidentally edited this table in some versions with 

a column called Sector_2; however, I’ve now added a query that 

computes sector_2 instead.)

9/11/2012

Linked Tables RPT_MONITORING_SAMPLE_NOTES

RPT_NO_REDUCT_REASON

RPT_OUTFALL

RPT_REASON

RPT_SITE_STATUS_CHANGE

1 6 Not used 9/11/2012

Initial Processing RPT_DISCHARGE_r01 2 1 RPT_DISCHARGE_r01 reads in RPT_DISCHARGE to reduce down 

duplicate records. By ignoring “REPORT_ID” and 

“PERIOD_CHECKED_YN” we can then use 

ERPT_MONITOR_PERIOD_ID as a primary key (with an effective 

n =2,688). 

9/11/2012

Initial Processing PT_MONITORING_REQ_r01 2 2 Read in data from PT_MONITORING_REQ, and create a 

“Sector_2” which relies on the SUBSECTOR field and when not 

available uses nonmissing SECTOR values. 

9/11/2012

Initial Processing PT_MONITORING_REQ_rELG 2 3 Read in data from PT_MONITORING_REQ_r01. Creates a unique 

list of permit_ID (PT_ID) and Sector category.

9/11/2012

Initial Processing RPT_FACILITY_r01 2 4 Query creating a unique list of facility permit and facility names 9/12/2012
Initial Processing q_jbh_01 2 5 Data from the following eNOI tables were queried for the 

purposes of this analysis: PT_STATUS, RPT_FACILITY 

RPT_MONITORING, ERPT_DISCHARGE_LOCATION_ID, and 

RPT_DISCHARGE_r01. N -> 42,351 

Requirements include:

PARAMETER_NAME <> null

PT_STATUS = “Active” 

RESULT_DESC<>"Not Applicable"

MONITORING TYPES = "QBM" Or "ELG" Or "QBM, ELG"

9/11/2012

Lookup Tables tbluk_parameter_monitoring 3 1 N=518. List of all chemical names from the monitoring table 

(Parameter). These values are simplified to Parameter_I and 

Parameter_II. Parameter_I gets rid of inconsistencies in spelling 

while Parameter_II gets rid of differences in ‘total’ vs. ‘total 

recoverable’. Based on an analysis decision we focus on the 

Parameter_II synthesis

9/11/2012

Lookup Tables tbluk_QBM 3 2 N=19. List of benchmarks that are constant (i.e., not a function 

of hardness). Includes link back to Parameter_II naming 

convention. ‘Total’ and ‘total recoverable’ aluminum and iron 

on in benchmarks, but have same concentration value. 

Therefore total recoverable aluminum and iron are recoded in 

Parameter_II to ‘not used’ to avoid record duplication.

9/11/2012

Lookup Tables tbluk_QBM_w_Hardness 3 3 N=6. List of benchmarks for 6 metals with hardness-specific 

benchmarks. Includes link back to Parameter_II naming 

convention. 

9/11/2012

Lookup Tables tb_QBM_required_monitoring 3 4 Identifies the required monitoring parameters for each 

subsector (n=1583). Data derived from extraction from MSGP 

permit.

9/11/2012

Lookup Tables tbluk_ELG_01 3 5.1 Detailed list of ELG values by SIC, Sector, and Subsector. 

Includes additional columns simplifying the parameter name 

(derived from tbluk_ELG_02.

9/11/2012

Lookup Tables tbluk_ELG_02 3 5.2 Listing of the conversion of ELG parameter to the simplified 

parameter name.

9/11/2012

Lookup Tables tbluk_ELG_03 3 5.3 Reduction of tbluk_ELG_01 to the Sector-Parameter-Statistical 

basis level. New field, “Apply” added to indicate “no” when 

statistical basis was for a monthly or 30-day average. Usually a 

corresponding concentration was supplied for a daily maximum 

9/11/2012
for which apply was set to “Yes”

Lookup Tables tbluk_ELG_03_Yes 3 5.4 Trimmed down version Tbluk_ELG_03 where Apply = “Yes” 9/11/2012

Benchmark Data 

Prep

q_jbh_02 QBM

(Exported to Excel for further 

processing: See QBM Analysis, rev 

01.xlsx, tab “QBM data”)

4 1 Links preliminary data pull (from q_jbh_01) with 

tbluk_parameter_monitoring and to tbluk_QBM in order to link 

measurements to the benchmarks after correcting for 

parameter name. (For parameters with constant benchmarks.)

Data requirements:

 Non standard reporting periods were removed

 "QBM" Or "QBM, ELG" were kept

 Records with missing or zero concentrations reported as 

‘detected’ or as ‘detected but below QL’ were eliminated

 Concentrations for ‘non-detects’ was recoded to zero and 

concentrations for ‘detected but below QL’ was recoded to 

one-half the quantitation limit.

9/11/2012

Benchmark Data 

Prep

q_jbh_02 QBM Hardness

(Exported to Excel for further 

processing: See QBM Analysis, rev 

01.xlsx, tab “QBM Metals data”)

4 2 Links preliminary data pull (from q_jbh_01) with 

tbluk_parameter_monitoring and to tbluk_QBM in order to link 

measurements to the benchmarks after correcting for 

parameter name.

Same data requirements as q_jbh_02 QBM, but also includes 

that hardness >0

9/11/2012

Benchmark Data 

Prep

q_jbh_02 QBM_Crosstab 4 3 Frequency table of q_jbh_02 QBM. Provides number of 

observations by season for each permit/outfall/parameter

9/11/2012

Benchmark Data 

Prep

q_jbh_02 QBM Hardness_Crosstab 4 4 Frequency table of q_jbh_02 QBM Hardness. Provides number 

of observations by season for each permit/outfall/parameter

9/11/2012

Benchmark Data 

Analysis

tb_QBM_results_01

(Imported from Excel: See QBM 

Analysis, rev 01.xlsx, tab “Yearly 

Avg”) (with records marked with

sector =”Z_Overall”).

5 1 Includes yearly averages derived from spreadsheet “manual” 

processing

Other records are added to this table (from 

tb_QBM_results_01_amend) in a later step to add sectorspecific results.

9/11/2012

Benchmark Data 

Analysis

q_jbh_05a_qbm_fac01

q_jbh_05a_qbm_fac02

5 3.1 Reads in tb_QBM_results_01 and computes the Number of 

Permits, Number of Annual Averages, and Number of Annual 

Averages Exceeding BM by Sector-Parameter.

9/11/2012

Benchmark Data q_jbh_05b_qbm_out01 5 3.2 Reads in tb_QBM_results_01 and computes the Number of 9/11/2012
Analysis q_jbh_05b_qbm_out02 Outfalls.

Benchmark Data 

Analysis

q_jbh_05c_qbm_uni_fac01

q_jbh_05c_qbm_uni_fac02

5 3.3 Reads in tb_QBM_results_01 and computes the Number of 

Permits with One or More Yearly Average Exceeding BM.

9/11/2012

Benchmark Data 

Analysis

q_jbh_05c_qbm_uni_out01

q_jbh_05c_qbm_uni_out02

5 3.4 Reads in tb_QBM_results_01 and computes the Number of 

Outfalls with One or More Yearly Average Exceeding BM.

9/11/2012

Benchmark Data 

Analysis

q_jbh_05d_qbm_combine (key 

result)

5 4 Combines the results from (5) 3.1-3.4 into one table 9/11/2012

Benchmark Data 

Analysis

q_jbh_06a_Unique_Facility_Subsect

or

This is the first step in replicating the 

yearly averages into sector specific

results.

5 5.1 Reads from q_jbh_06a_Unique_Facility_Subsector and 

computes a unique list of permits and subsectors. (One permit 

may be on multiple rows for multiple subsectors.) n=877

q_jbh_06a_Unique_Facility_Subsector

PT_ID SUBSECTOR

104142 C.1

104142 C.2

9/11/2012

Benchmark Data 

Analysis

q_jbh_06b_Unique_Subsector_Para

meter

5 5.2 Reads in tb_QBM_required_monitoring and computes a unique 

list of subsector and monitoring parameter requirements. 

N=104

q_jbh_06b_Unique_Subsector_Parameter

Subsector Parameter_II CountOfID

A.1 Chemical Oxygen Demand (COD) 1

A.1 Total Suspended Solids 1

9/11/2012

Benchmark Data 

Analysis

q_jbh_06c_Unique_Fac_Subsec_Par

m

5 5.3 Links 5.1 and 5.2 together creating of permits – sectors --

subsectors – parameter monitoring requirement. N=1641

q_jbh_06c_Unique_Fac_Subsec_Parm

PT_ID Sector SUBSECTOR Parameter_II

104142 C C.2 Aluminum

104142 C C.1 Iron

9/11/2012

Benchmark Data 

Analysis

q_jbh_06d_Unique_Fac_Sec_Parm 5 5.4 Reduces 5.3 to a unique list of permits – sectors – parameters. 

N=1532.

q_jbh_06d_Unique_Fac_Sec_Parm

PT_ID Sector Parameter_II

104142 C Aluminum

104142 C Iron

9/11/2012

Benchmark Data 

Analysis

q_jbh_07a_results_amend 5 5.5 Links tb_QBM_results_01 (when first imported from the Excel 

spreadsheet) to q_jbh_06d_Unique_Fac_Sec_Parm; and creates 

tb_QBM_results_01_amend.

9/11/2012
Note: This created table is manually appended to 

tb_QBM_results_01 ... so the table has more records. Be sure 

that we only do this once, as successive application would 

replicate results.

Benchmark Data 

Analysis

tb_QBM_results_01_amend 5 5.6 Created by q_jbh_07a_results_amend. It is a table that is the 

result of reading tb_QBM_results_01 and matching up with 

monitoring requirements. These records are manually added to 

tb_QBM_results_01 to create a table that has data for an overall 

analysis as well as a sector-specific analysis. (This is a once off 

task.)

9/11/2012

Benchmark Data 

Analysis

q_jbh_08a_qbm_output_yrly_details 5 7.1 Links table tb_QBM_results_01 back to a unique list of facility 

names .

9/12/2012

Benchmark Data 

Analysis

q_jbh_08a_qbm_output_yrly_details

_Crosstab

5 7.2 Presents the number of exceedances by facility and parameter.

(Exported to Excel for further processing: See QBM Analysis, 

rev 01.xlsx, tab “q_jbh_08a”)

9/12/2012

Benchmark Data 

Analysis

q_jbh_08a_qbm_output_yrly_details

_Crosstab_Count

5 7.3 Presents the number of yearly averages that were evaluated.

(Exported to Excel for further processing: See QBM Analysis, 

rev 01.xlsx, tab “q_jbh_08a”)

9/12/2012

ELG Data Analysis q_jbh_03 ELG Data 6 1 Links preliminary data pull (from q_jbh_01) with 

tbluk_parameter_monitoring and to tbluk_ELG_02 in order to 

link measurements to the ELGs after correcting for parameter 

name. (For parameters with constant benchmarks.)

Data requirements:

 "ELG" Or "QBM, ELG" were kept

9/11/2012

ELG Data Analysis q_jbh_03 ELG Data 02 6 2 Links q_jbh_03 ELG Data to PT_MONITORING_REQ_rELG and 

tbluk_ELG_03_Yes. Linking to PT_MONITORING_REQ_rELG 

replicates measurements if the facility belongs to more than one 

sector. Linking to ELG_03_Yes links the measurements to the 

ELG concentrations for comparison.

An exceedance is flagged if a reported “detected” measurement 

is greater than the ELG concentration. (For pH there is a special 

calculation to check for outside range of 6-9.) Nondetects, <QL, 

and detects with no reported concentration are not flagged.

9/11/2012

ELG Data Analysis q_jbh_03a facility01

q_jbh_03a facility02

6 3.1 Read q_jbh_03 ELG Data 02 and compute the number of 

facilities evaluated for each sector and parameter.

9/11/2012
ELG Data Analysis q_jbh_03b outfall01

q_jbh_03b outfall02

6 3.2 Read q_jbh_03 ELG Data 02 and compute the number of outfalls 

evaluated for each sector and parameter.

9/11/2012

ELG Data Analysis q_jbh_03c num_detects01 6 3.3 Read q_jbh_03 ELG Data 02 and compute the number of 

detected results by sector and parameter.

9/11/2012

ELG Data Analysis q_jbh_03d num_gt_elg01 6 3.4 Read q_jbh_03 ELG Data 02 and compute the number of 

detected results greater than the ELG by sector and parameter.

9/11/2012

ELG Data Analysis q_jbh_03e num_obs 6 3.5 Read q_jbh_03 ELG Data 02 and compute the number of 

observations by sector and parameter.

9/11/2012

ELG Data Analysis q_jbh_03f combine 6 3.6 Combine for output (6) 3.1-3.6 9/11/2012

ELG Data Analysis q_jbh_03h_output 6 4.1 Links table q_jbh_03 ELG Data 02 back to a unique list of facility 

names .

9/12/2012

ELG Data Analysis q_jbh_03h_output_Crosstab 6 4.2 Presents the number of ELG exceedances by facility and 

parameter.

(Exported to Excel for further processing: See QBM Analysis, 

rev 01.xlsx, tab “q_jbh_03a”)

9/12/2012

ELG Data Analysis q_jbh_03h_output_Crosstab_Count 6 4.3 Presents the number of measurements that were evaluated.

(Exported to Excel for further processing: See QBM Analysis, 

rev 01.xlsx, tab “q_jbh_03a”)

9/12/2012