* _ \ KWJ *1 PRO1^ Point Source Subtraction Using MS Access ------- ------- EPA-454/B-19-025 February 2016 Point Source Subtraction Using MS Access U.S. Environmental Protection Agency Office of Air Quality Planning and Standards Air Quality Assessment Division Research Triangle Park, NC ------- Point Source Subtraction using MS Access (shared by Lynn Deahl, KS February 2016) Step 1. Create a new Access database. Step 2. Import your state's 2014 inventory, or run a make-table query on your state's 2014 emissions inventory to generate a table with the (minimal) fields "Facility," "State_county_FIPS," "SCC," Pollutant," and "2014_tons." SCCs should be formatted (short) text. Step 3. Download and save EPA's table named "np_pt_reconciliation..." at the link labeled "Point to Nonpoint Crosswalk (Point Subtraction) for 2014 NEI (xlsx)" at http://www.epa.gov/air-erriissiorts- inventories/2014-national-emissions-inventory-nei-information. Import into database. Step 4. Create a new query using the two tables above, linking the two point SCC fields. From the 2014 inventory table select "Facility," "State_county_FIPS," "SCC," "Pollutant," and "2014_tons" fields; from the crosswalk table select "Point SCC Level One," "Point SCC Level Two," "Point SCC Level Three," "Point SCC Level Four," "Nonpoint SCC," and "Nonpoint SCC Short Name" fields. Note running a query at this point should give a complete crosswalked point-to-nonpoint table you can use for finding and subtracting point emissions on a county basis for (again, query-based) selected nonpoint SCCs. You can reduce the size of the output table by filtering pollutants and/or tons, e.g., using the criterion for 2014_tons ">0." Note also the reason for including the facility names and SCC descriptions is twofold. First, the point SCC descriptors are required for the solvent and ICI tool input tables. Second, comparing the nonpoint SCC with the facility name can be useful to make sure the two are compatible. In other words, to quote an EPA instructor from many years ago: Quality assurance is never having to say you're sorry." Solvent and ICI tools. For the solvent and ICI tools, where point source subtractions require a properly formatted Excel spreadsheet populated with point SCCs, a couple more steps need to be done. First, remember emissions need to be converted to pounds (and no HAPs yet, either). Next, the following sets of point SCCs need to be pasted in (under "Criteria") as filters to get all the solvent- or ICI-related point source processes in the query for table data. You can use the following sets of SCCs to make this easier, but note there are too many codes for Access to process in a single query, so queries need to be done. Solvent-related point SCCs - query 1 of 3 (source: EPA Solvent Tool vl.5. Point Emissions State Level table) 30101470 or 30101471 or 30101472 or 30102070 or 30102071 or 30102072 or 30102426 or 30400340 or 30400342 or 30400711 or 30400725 or 30503701 or 30510005 or 30701199 or 30900301 or 30900302 or 30901101 or 30901102 or 31303502 or 31306501 or 31306502 or 31401560 or 31401561 or 31401562 or 31401563 or 31401570 31612001 or 31612002 or 31612003 or 33000102 or 40100101 or 40100102 or 40100104 or 40100105 or 40100107 or 40100146 or 40100147 or 40100160 or 40100161 or 40100162 or 40100163 or 40100198 or 40100199 or 40100201 or 40100202 or 40100203 or 40100204 or 40100205 or 40100206 or 40100207 or 40100208 or 40100209 40100215 or 40100221 or 40100222 or 40100223 or 40100224 or 40100225 or 40100235 or 40100236 or 40100251 or 40100252 or 40100253 or 40100254 or 40100255 or ------- 40100256 or 40100257 or 40100258 or 40100296 or 40100298 or 40100299 or 40100301 or 40100302 or 40100303 or 40100304 or 40100305 or 40100306 or 40100307 40100308 or 40100309 or 40100310 or 40100311 or 40100335 or 40100336 or 40100399 or 40100401 or 40100499 or 40188898 or 40200101 or 40200706 or 40200840 or 40200841 or 40200842 or 40200843 or 40200845 or 40200846 or 40200847 or 40200848 or 40200849 or 40200901 or 40200902 or 40200903 or 40200904 or 40200905 40200906 or 40200907 or 40200908 or 40200909 or 40200910 or 40200911 or 40200912 or 40200913 or 40200914 or 40200915 or 40200916 or 40200917 or 40200918 or 40200919 or 40200920 or 40200921 or 40200922 or 40200923 or 40200924 or 40200925 or 40200926 or 40200927 or 40200928 or 40200929 or 40200930 or 40200931 40201101 or 40201103 or 40201104 or 40201105 or 40201112 or 40201114 or 40201116 or 40201121 or 40201122 or 40201197 or 40201199 or 40201301 or 40201303 or 40201304 or 40201305 or 40201310 or 40201320 or 40201330 or 40201399 or 40201401 or 40201402 or 40201403 or 40201404 or 40201405 or 40201406 or 40201410 40201411 or 40201431 or 40201432 or 40201433 or 40201434 or 40201435 or 40201436 or 40201437 or 40201438 or 40201499 or 40201501 or 40201502 or 40201503 or 40201504 or 40201505 or 40201531 or 40201599 or 40201601 or 40201602 or 40201603 or 40201604 or 40201605 or 40201606 or 40201607 or 40201608 or 40201609 - query 2 of 3 40201619 or 40201620 or 40201621 or 40201622 or 40201623 or 40201624 or 40201625 or 40201626 or 40201627 or 40201628 or 40201629 or 40201630 or 40201631 or 40201632 or 40201699 or 40201702 or 40201703 or 40201704 or 40201705 or 40201706 or 40201721 or 40201722 or 40201723 or 40201724 or 40201725 or 40201726 40201727 or 40201728 or 40201729 or 40201731 or 40201732 or 40201733 or 40201734 or 40201735 or 40201736 or 40201737 or 40201738 or 40201739 or 40201799 or 40201802 or 40201803 or 40201804 or 40201805 or 40201806 or 40201807 or 40201899 or 40201901 or 40201903 or 40201904 or 40201999 or 40202001 or 40202002 40202003 or 40202004 or 40202005 or 40202010 or 40202011 or 40202012 or 40202013 or 40202014 or 40202015 or 40202020 or 40202021 or 40202022 or 40202023 or 40202024 or 40202025 or 40202031 or 40202032 or 40202033 or 40202034 or 40202035 or 40202036 or 40202037 or 40202038 or 40202039 or 40202099 or 40202101 40202103 or 40202104 or 40202105 or 40202106 or 40202107 or 40202108 or 40202109 or 40202110 or 40202111 or 40202117 or 40202118 or 40202131 or 40202132 or 40202133 or 40202140 or 40202199 or 40202201 or 40202202 or 40202203 or 40202204 or 40202205 or 40202206 or 40202207 or 40202208 or 40202209 or 40202210 40202211 or 40202212 or 40202213 or 40202214 or 40202215 or 40202220 or 40202229 or 40202230 or 40202239 or 40202240 or 40202249 or 40202250 or 40202259 or ------- 40202270 or 40202280 or 40202299 or 40202301 or 40202302 or 40202303 or 40202304 or 40202305 or 40202306 or 40202399 or 40202401 or 40202402 or 40202403 40202404 or 40202405 or 40202406 or 40202499 or 40202602 or 40202605 or 40202701 or 40202801 or 40202802 or 40202899 or 40203001 or 40204001 or 40204002 or 40204003 or 40204004 or 40204010 or 40204011 or 40204012 or 40204013 or 40204020 or 40204021 or 40204022 or 40204023 or 40204121 or 40204130 or 40204140 40204150 or 40204151 or 40204152 or 40204160 or 40204161 or 40204162 or 40204221 or 40204230 or 40204240 or 40204250 or 40204251 or 40204252 or 40204260 or 40204261 or 40204262 or 40204321 or 40204330 or 40204340 or 40204350 or 40204351 or 40204352 or 40204360 or 40204361 or 40204362 or 40204421 or 40204430 - query 3 of 3 40204431 or 40204432 or 40204435 or 40204440 or 40204441 or 40204442 or 40204443 or 40204450 or 40204455 or 40204460 or 40204461 or 40204462 or 40204470 or 40204471 or 40204472 or 40204521 or 40204530 or 40204531 or 40204532 or 40204550 or 40204555 or 40204560 or 40204561 or 40204562 or 40204570 or 40204571 40204572 or 40204621 or 40204630 or 40204631 or 40204632 or 40204650 or 40204655 or 40204660 or 40204661 or 40204662 or 40204670 or 40204671 or 40204672 or 40204721 or 40204730 or 40204735 or 40204740 or 40204750 or 40204755 or 40204760 or 40204761 or 40204762 or 40204770 or 40204771 or 40204772 or 40206010 40206030 or 40206031 or 40206032 or 40206033 or 40206034 or 40206035 or 40206050 or 40288824 or 40299998 or 40500101 or 40500201 or 40500202 or 40500203 or 40500215 or 40500301 or 40500302 or 40500303 or 40500304 or 40500305 or 40500306 or 40500307 or 40500314 or 40500315 or 40500318 or 40500319 or 40500401 40500413 or 40500415 or 40500416 or 40500417 or 40500418 or 40500421 or 40500422 or 40500431 or 40500502 or 40500503 or 40500506 or 40500507 or 40500510 or 40500511 or 40500514 or 40500597 or 40500599 or 40500601 or 40500701 or 40500801 or 40500802 or 40588801 or 40600259 or 41000101 or 41000115 or 41000125 41000126 or 41000130 or 41000131 or 41000132 or 41000133 or 41000140 or 41000141 or 41000142 or 41000143 or 41000144 or 41000145 or 41000146 or 41000147 or 41000160 or 41000161 or 41000162 or 41000163 or 41000164 or 41000165 or 41000166 or 41000202 or 41000215 or 41000225 or 41000226 or 41000230 or 41000231 41000232 or 41000233 or 41000240 or 41000241 or 41000242 or 41000243 or 41000244 or 41000245 or 41000246 or 41000247 or 41000260 or 41000261 or 41000262 or 41000263 or 41000264 or 41000265 or 41000266 or 41080001 or 41082001 or 41082002 or 41082599 or 49000101 or 49000102 or 49000103 or 49000104 or 49000105 49000199 or 49000202 or 49000203 or 49000206 or 49000207 or 49000208 or 49000209 or 49000299 or 49000301 or 49000302 or 49000303 or 49000304 or 49000399 or ------- 49000401 or 49000402 or 49000403 or 49000404 or 49000405 or 49000499 or 49000501 or 49000502 or 49000503 or 49000504 or 49000599 or 49000601 or 49099998 ICI-related point SCCs - query 1 of 2 (source: EPA ICI Tool vl.4 state-level point source template) 10200101 or 10200104 or 10200107 or 10200117 or 10200201 or 10200202 or 10200203 or 10200204 or 10200205 or 10200206 or 10200210 or 10200212 or 10200213 or 10200217 or 10200218 or 10200219 or 10200221 or 10200222 or 10200223 or 10200224 or 10200225 or 10200226 or 10200229 or 10200401 or 10200402 or 10200403 10200404 or 10200405 or 10200501 or 10200502 or 10200503 or 10200504 or 10200505 or 10200601 or 10200602 or 10200603 or 10200604 or 10200901 or 10200902 or 10200903 or 10200904 or 10200905 or 10200906 or 10200907 or 10200908 or 10200910 or 10200911 or 10200912 or 10201001 or 10201002 or 10201003 or 10201401 10201403 or 10201404 or 10300101 or 10300102 or 10300103 or 10300203 or 10300205 or 10300206 or 10300207 or 10300208 or 10300209 or 10300211 or 10300214 or 10300216 or 10300217 or 10300218 or 10300221 or 10300222 or 10300223 or 10300224 or 10300225 or 10300226 or 10300401 or 10300402 or 10300403 or 10300404 10300501 or 10300502 or 10300503 or 10300504 or 10300601 or 10300602 or 10300603 or 10300901 or 10300902 or 10300903 or 10300908 or 10300910 or 10300911 or 10300912 or 10301001 or 10301002 or 10301003 or 10500102 or 10500105 or 10500106 or 10500110 or 10500202 or 10500205 or 10500206 or 10500209 or 10500210 20200101 or 20200102 or 20200103 or 20200104 or 20200105 or 20200106 or 20200107 or 20200108 or 20200109 or 20200201 or 20200202 or 20200203 or 20200204 or 20200205 or 20200206 or 20200207 or 20200208 or 20200209 or 20200251 or 20200252 or 20200253 or 20200254 or 20200255 or 20200256 or 20200401 or 20200405 20200406 or 20200407 or 20200501 or 20200505 or 20200506 or 20200507 or 20200901 or 20200902 or 20200905 or 20200906 or 20200907 or 20200908 or 20200909 or 20201001 or 20201002 or 20201005 or 20201006 or 20201007 or 20201008 or 20201009 or 20201011 or 20201012 or 20201013 or 20201014 or 20300101 or 20300102 20300105 or 20300106 or 20300107 or 20300108 or 20300109 or 20300201 or 20300202 or 20300203 or 20300204 or 20300205 or 20300206 or 20300207 or 20300208 or 20300209 or 20300901 or 20300908 or 20300909 or 20301001 or 20301002 or 20301005 or 20301006 or 20301007 or 20400110 or 20400111 or 20400112 or 20400406 - query 2 of 2 27000320 or 27300320 or 30190001 or 30190002 or 30190003 or 30190011 or 30190012 or 30190013 or 30190021 or 30190022 or 30190023 or 30290001 or 30290002 or 30290003 or 30290005 or 30291001 or 30390001 or 30390002 or 30390003 or 30390011 or 30390012 or 30390013 or 30390021 or 30390022 or 30390023 or 30400406 ------- 30400407 or 30490001 or 30490002 or 30490003 or 30490011 or 30490012 or 30490013 or 30490021 or 30490022 or 30490023 or 30490031 or 30490032 or 30490033 or 30490035 or 30500206 or 30500207 or 30500208 or 30500209 or 30505020 or 30505021 or 30505022 or 30505023 or 30590001 or 30590002 or 30590003 or 30590005 30590011 or 30590012 or 30590013 or 30590021 or 30590023 or 30600105 or 30600107 or 30600111 or 30600901 or 30600902 or 30600903 or 30600905 or 30602401 or 30609901 or 30609902 or 30609903 or 30609905 or 30790001 or 30790002 or 30790003 or 30790011 or 30790012 or 30790013 or 30790021 or 30790022 or 30790023 30890001 or 30890002 or 30890003 or 30890004 or 30890011 or 30890012 or 30890013 or 30890021 or 30890022 or 30890023 or 30990001 or 30990002 or 30990003 or 30990011 or 30990012 or 30990013 or 30990023 or 31000401 or 31000402 or 31000404 or 31000406 or 31000411 or 31000412 or 31000414 or 31390001 or 31390002 31390003 or 39000189 or 39000199 or 39000201 or 39000203 or 39000288 or 39000289 or 39000299 or 39000402 or 39000403 or 39000489 or 39000499 or 39000501 or 39000502 or 39000503 or 39000589 or 39000598 or 39000599 or 39000602 or 39000603 or 39000605 or 39000689 or 39000699 or 39000989 or 39000999 or 39001089 39001099 or 39900501 or 39900601 or 39901001 or 39990001 or 39990002 or 39990003 or 39990011 or 39990012 or 39990013 or 39990021 or 39990022 or 39990023 or 40201001 or 40201002 or 40201003 or 40201004 or 40290011 or 40290012 or 40290013 or 40290023 or 49090011 or 49090012 or 49090013 or 49090021 or 49090022 49090023 or 50100602 or 50100603 or 50190002 or 50190005 or 50190006 or 50190010 or 50290002 or 50290005 or 50290006 or 50290010 or 50390002 or 50390005 or 50390006 or 50390010 ------- United States Office of Air Quality Planning and Standards Publication No. EPA-454/B-19-025 Environmental Protection Air Quality Assessment Division February 2016 Agency Research Triangle Park, NC ------- |