Document ID: EPA-HQ-OAR-2021-0317-1577
Agency: epa
Document Type: Supporting & Related Material
Title: 
Posted Date: 2022-12-06T05:00Z

Instructions-Access Steps to Create Centrifugal Compressor Analysis Sheet 
Steps Used to Create Excel File (cc_measured_single_2015-2020) via Microsoft Access:
                                     Step
Description
                                       1
Download 18 files, three for each of the 6 years from RY2015 to RY2020 files. These files are:
 EF_W_CENTRIF_COMP_ACTIVITY
 EF_W_CENTRIF_COMP_SOURCE
 EF_W_CENTRIF_LEAK_VENT_DATA
                                       2
Combine the different RY files (append spreadsheets) to create 3 Excel files with the names identified above that contain all records across RY2015 to RY2020. NOTE: Make sure the sheet name for each file is the same name as above. Import these 3 Excel files into Access.
                                       3
Create intermediate query file "CC1" using the following SQL:
SELECT DISTINCT EF_W_CENTRIF_COMP_SOURCE.REPORTING_YEAR, EF_W_CENTRIF_COMP_SOURCE.FACILITY_ID, EF_W_CENTRIF_COMP_SOURCE.FACILITY_NAME, EF_W_CENTRIF_COMP_SOURCE.INDUSTRY_SEGMENT, EF_W_CENTRIF_COMP_ACTIVITY.COMPRESSOR_MEASURED_OP_MODE, EF_W_CENTRIF_COMP_ACTIVITY.COMPRESSOR_MEASURED_NO_OP_MODE, EF_W_CENTRIF_COMP_ACTIVITY.TOTAL_OPERATING_MODE_HOURS, EF_W_CENTRIF_COMP_ACTIVITY.TOTAL_NOT_OPERATING_MODE_HOURS, EF_W_CENTRIF_COMP_ACTIVITY.BLIND_FLANGES_INSTALLED, EF_W_CENTRIF_COMP_SOURCE.COMPRESSOR_SOURCE, EF_W_CENTRIF_COMP_SOURCE.LEAK_OR_VENT_ID, EF_W_CENTRIF_COMP_ACTIVITY.SEAL_TYPE, EF_W_CENTRIF_COMP_ACTIVITY.COMPRESSOR_IDENTIFIER
FROM EF_W_CENTRIF_COMP_SOURCE RIGHT JOIN EF_W_CENTRIF_COMP_ACTIVITY ON (EF_W_CENTRIF_COMP_SOURCE.FACILITY_ID = EF_W_CENTRIF_COMP_ACTIVITY.FACILITY_ID) AND (EF_W_CENTRIF_COMP_SOURCE.REPORTING_YEAR = EF_W_CENTRIF_COMP_ACTIVITY.REPORTING_YEAR) AND (EF_W_CENTRIF_COMP_SOURCE.INDUSTRY_SEGMENT = EF_W_CENTRIF_COMP_ACTIVITY.INDUSTRY_SEGMENT) AND (EF_W_CENTRIF_COMP_SOURCE.COMPRESSOR_IDENTIFIER = EF_W_CENTRIF_COMP_ACTIVITY.COMPRESSOR_IDENTIFIER);
                                       4
Create intermediate query file "CC2" using the following SQL:
SELECT DISTINCT EF_W_CENTRIF_LEAK_VENT_DATA.TABLE_NUM, EF_W_CENTRIF_LEAK_VENT_DATA.FACILITY_ID, EF_W_CENTRIF_LEAK_VENT_DATA.FACILITY_NAME, EF_W_CENTRIF_LEAK_VENT_DATA.REPORTING_YEAR, EF_W_CENTRIF_LEAK_VENT_DATA.INDUSTRY_SEGMENT, cc1.LEAK_OR_VENT_ID, EF_W_CENTRIF_LEAK_VENT_DATA.MEASUREMENT_METHOD, EF_W_CENTRIF_LEAK_VENT_DATA.MEASUREMENT_DATE, EF_W_CENTRIF_LEAK_VENT_DATA.MEASUREMENT_FLOW_RATE, EF_W_CENTRIF_LEAK_VENT_DATA.COMPRESSOR_OPERATING_MODE, EF_W_CENTRIF_LEAK_VENT_DATA.COMPRESSOR_NOT_OPERATING_MODE
FROM cc1 INNER JOIN EF_W_CENTRIF_LEAK_VENT_DATA ON (cc1.REPORTING_YEAR = EF_W_CENTRIF_LEAK_VENT_DATA.REPORTING_YEAR) AND (cc1.FACILITY_ID = EF_W_CENTRIF_LEAK_VENT_DATA.FACILITY_ID) AND (cc1.LEAK_OR_VENT_ID = EF_W_CENTRIF_LEAK_VENT_DATA.LEAK_OR_VENT_ID) AND (cc1.INDUSTRY_SEGMENT = EF_W_CENTRIF_LEAK_VENT_DATA.INDUSTRY_SEGMENT)
WHERE (((EF_W_CENTRIF_LEAK_VENT_DATA.TABLE_NUM)="Table O.3.i"));
                                       5
Create intermediate query file "CC3" using the following SQL:
SELECT DISTINCT cc1.REPORTING_YEAR, cc1.FACILITY_ID, cc1.FACILITY_NAME, cc1.INDUSTRY_SEGMENT, cc1.COMPRESSOR_MEASURED_OP_MODE, cc1.COMPRESSOR_MEASURED_NO_OP_MODE, cc1.TOTAL_OPERATING_MODE_HOURS, cc1.TOTAL_NOT_OPERATING_MODE_HOURS, cc1.BLIND_FLANGES_INSTALLED, cc1.COMPRESSOR_SOURCE, cc1.LEAK_OR_VENT_ID, cc1.SEAL_TYPE, cc1.COMPRESSOR_IDENTIFIER, EF_W_CENTRIF_LEAK_VENT_DATA.AS_FOUND_MEASUREMENT_CONDUCTED, EF_W_CENTRIF_LEAK_VENT_DATA.CONT_MEASUREMENTS_CONDUCTED, EF_W_CENTRIF_LEAK_VENT_DATA.SINGLE_SOURCE_OR_MANIFOLD_GRP, EF_W_CENTRIF_LEAK_VENT_DATA.LEAK_OR_VENT_EMISS_RELEASED, EF_W_CENTRIF_LEAK_VENT_DATA.CO2_EMISSIONS_VENTED, EF_W_CENTRIF_LEAK_VENT_DATA.CH4_EMISSIONS_VENTED, EF_W_CENTRIF_LEAK_VENT_DATA.PERCENT_TIME_DEV_OPERATIONAL
FROM cc1 LEFT JOIN EF_W_CENTRIF_LEAK_VENT_DATA ON (cc1.LEAK_OR_VENT_ID = EF_W_CENTRIF_LEAK_VENT_DATA.LEAK_OR_VENT_ID) AND (cc1.INDUSTRY_SEGMENT = EF_W_CENTRIF_LEAK_VENT_DATA.INDUSTRY_SEGMENT) AND (cc1.FACILITY_ID = EF_W_CENTRIF_LEAK_VENT_DATA.FACILITY_ID) AND (cc1.REPORTING_YEAR = EF_W_CENTRIF_LEAK_VENT_DATA.REPORTING_YEAR)
WHERE (((EF_W_CENTRIF_LEAK_VENT_DATA.SINGLE_SOURCE_OR_MANIFOLD_GRP)="single") AND ((EF_W_CENTRIF_LEAK_VENT_DATA.TABLE_NUM)="Table O.2.ii and O.4"));
                                       6
Create final query file "CC4" using the following SQL:
SELECT DISTINCT cc3.REPORTING_YEAR, cc3.FACILITY_ID, cc3.FACILITY_NAME, cc3.INDUSTRY_SEGMENT, cc3.COMPRESSOR_IDENTIFIER, cc3.COMPRESSOR_SOURCE, cc3.BLIND_FLANGES_INSTALLED, cc3.LEAK_OR_VENT_ID, cc3.SEAL_TYPE, cc2.MEASUREMENT_METHOD, cc3.SINGLE_SOURCE_OR_MANIFOLD_GRP, cc3.COMPRESSOR_MEASURED_OP_MODE, cc3.COMPRESSOR_MEASURED_NO_OP_MODE, cc3.TOTAL_OPERATING_MODE_HOURS, cc3.TOTAL_NOT_OPERATING_MODE_HOURS, cc3.LEAK_OR_VENT_EMISS_RELEASED, cc3.PERCENT_TIME_DEV_OPERATIONAL, cc3.AS_FOUND_MEASUREMENT_CONDUCTED, cc3.CONT_MEASUREMENTS_CONDUCTED, cc3.CO2_EMISSIONS_VENTED, cc3.CH4_EMISSIONS_VENTED, cc2.MEASUREMENT_DATE, cc2.MEASUREMENT_FLOW_RATE, cc2.COMPRESSOR_OPERATING_MODE, cc2.COMPRESSOR_NOT_OPERATING_MODE
FROM cc2 LEFT JOIN cc3 ON (cc2.FACILITY_ID = cc3.FACILITY_ID) AND (cc2.REPORTING_YEAR = cc3.REPORTING_YEAR) AND (cc2.INDUSTRY_SEGMENT = cc3.INDUSTRY_SEGMENT) AND (cc2.LEAK_OR_VENT_ID = cc3.LEAK_OR_VENT_ID)
WHERE (((cc3.REPORTING_YEAR)>2014));
                                       7
Export the CC4 table to Excel (or click on top left corner of table to highlight entire table, select copy, open blank excel file, select cell A1, and paste).
                                       8
Add a field for converting MEASUREMENTFLOWRATE from a text string to a numeric value.