This index page is arranged in sections: Permanent and Transitory items.
- Permanent Items
- Pesticide Statistics and Trends
- Miscellaneous Facts and Information
- Access 2002 and PPIS. Tutorials Ready-to-Run/Use
- US Registrations Y2009 Summaries
Look for the 
and
flags to find recent updates or additions.
EPA-OPP. Pesticides Home Page
EPA's new format, as of 2003. For a detailed list of links go to the A-Z
Index page.
Regulatory, Registration, and Industry News
Here is a list, with links, to useful Web sites
Label Review Manual,3rd Edition. And Q&A About Labels
EPA's instructions to its reviewers of pesticide labels. 19 Chapters.
View/Download as HTML (chapter-by-chapter) or PDF (entire manual).
In addition, link to, among others, Questions to EPA-OPP and the
Agency's responses.
Confidential Statement of Formula (CSF):
Preparation Hints
EPA help site about Biochemicals, but many hints for avoiding problems
are applicable to all pesticide products.
Electronic Submission of Labels and Studies for Pesticide Registation
How-to, hints, formatting, etc., etc.
Updated by EPA June 2008. Now includes forms, instructions on converting other
formats to PDF, etc.
Just in case.
Australia's APVMA documents for electronic submissions.
Pesticide Models
EPA site with links to individual sites for models.
EPA/USDA/FDA Test Methods And Related
- Index to EPA Test Methods . If EPA has a method it is supposed to be referenced here.
- Pesticide Analytical Manuals vol. I, II. Plus: Metals, Chemical Contaminants, and Natural Toxins.
- PAM Technical References; Pesticide Residue Reports. Same site as above.
- Index of Residue Analytical Methods. Methods (760+) by chemical, commodity, method source, method ID, method date, instrument, and LOQ (ppm). Methods are published as TIF/TIFF (text image file format), same as labels in PPLS.
- Environmental Test Methods and Guidelines. General, Air, Pesticides and Toxic Substances, Solid Waste, and Water.
- Index of Environmental
Chemistry Methods/Pesticide Analytical Methods and Procedures. Now a general
site, with links to pages for specific areas.
Also see separate site for only Environmental Chemistry Methods. Index to ECM. - BenchMark Dose Software. Many links to Environmental Information Management System (EIMS) pages.
- Analytical Methods for Drinking Water
- Antimicrobial Testing Methods and Procedures (also a variety of SOPs used by EPA and applicable to all types of labs)
- Antimicrobial/Disinfectant Science Policies. From EPA's Antimicrobial Division. "How to" documents. Other useful links.
- FDA Pestrak. Pestdata. Chemical compound information; behavior in analytical methods; more. Stuff of interest to FDA in re use on foods.
- Exposure Assessment Models. One page with links and descriptions for each model.
- Surface and ground water pesticide concentration, simulation models. PRZM, EXAMS, etc.
- AQUATOX 1.1. Aquatic ecosystem process model
- Persistent, Bioaccumulative Toxic Substances. Entry to EPA's PBT Profiler methodology.
- Acute Oral Toxicity Up-And-Down Procedure. Documentation and computer program files.
- Dietary Exposure Potential Model. A huge, downloadable database program. Read EPA's introduction to see what all the program can do.
- Pesticide Fate Database. Active ingredients and degradates with summaries of environmental fate studies. Has link to Alan Wood's site for ISO naming information, structural formula, etc.
- EPA Exposure Assessment Modeling. Extrapolate(?) from some known factors to potential/probable outcomes.
EPA Dockets
Information about regulatory actions.
- E-DOCKETS. EPA regulatory actions which are in progress and about which comments can be submitted to EPA.
- Major program dockets . Links to sites for information about regulatory and non-regulatory Agency activities.
- OPP Regulatory docket. Organophosphates plus Special Review and Special Docket monthly entries. Haphazardly updated but worth an occasional look.
EPA's Risk Assessment Portal
Agency site with collection of Guidance documents, models, etc.
EPA-OECD Harmonized Test Guidelines
Link to the index page for
access to all OPPTS Guidelines. Links to various OPPTS sites.
EPA's GLP and SOP Guidelines
- GLP advisories 1-77 are titled and indexed by the University of Georgia
- EPA Quality System. Information and instructions for its environmental data collectors.
- Antimicrobial Testing Methods and Procedures (also a variety of SOPs used by EPA and applicable to all types of labs)
EPA
Compliance And Enforcement
Starting point for actions and
documents.
Child-Resistant Packaging For Pesticides
EPA-OPP Web site which has all the Who, What, When, Where, and Why.
Plant Incorporated Protectants: Current EUPs
U.S. EPA's consolidated list of currently active PIP EUPs.
CDPR.
Toxicology Review Summaries On-Line
California provides a succinct
summary of tox studies for pesticidal active ingredients. Saves rummaging Federal
Register pesticide petition notices, etc. Updated on a regular basis.
EPA Guidance/Interpretive Documents
Select by any of several
criteria (e.g., OPPTS) and get back a list of non-binding guidance documents
issued by EPA since Jan. 1, 1999.
Check it out
Pesticide Product Labels And MSDSs
Links to free-of-charge sites on the WWW.
EPA's Pesticide
Label Site
Eventually every label for every product ever registered
and every significant label change will appear on the EPA site. Be certain to
read about limitations of the collection.
State Registrations
NPIRS (National Pesticide Information Retrieval System) at
Purdue University has a free-of-charge site which offers searching for
pesticide products registered by individual States. Very useful.
ISO Approved Common Names
Alan Wood publishes an invaluable site, "Compendium of Pesticide
Common Names". Much more than just names. Updated often, and you can
receive e-mail notices. Make use of. Go to:
Compendium.
ISO has published a revised version,
ISO 257:2004, of its rules
and procedures for "Pesticides and other agrochemicals - Principles for
the selection of common names"
Linked List Of Common Names
Alan Wood has generously provided an HTML file which lists all names as
of 16 February 2006. This file, iso_name.htm, is
arranged alphabetically; clicking on a name will take you to the relevant
page on Alan's site.
Active Ingredients: ISO Common Name and Chemical Class
Note: Not all common names are ISO Approved, and not every chemical is an
Active Ingredient. Below is my best effort at extracting information from Alan Wood's
"Compendium of Pesticide Common Names" web site. Any errors are mine.
1900+ row Excel file, cpcnsumm.xls, has:
Name, CAS Registry Number (if available), activity/use type, chemical classification,
and EPA-OPP PC Code number where I can do matching...(28 April 2009)
Crop And Non-Crop Chemical Companies On The WWW
Links to (some) pesticide product company sites.
International Sites On The WWW
A selected, but I trust useful, list of sites for information on pesticides
and agriculture outside the United States. Updated and revised irregularly, so
you'll just have to look.
International Maximum Residue Limits
Link to USDA's
Foreign Agricultural Service (FAS) site with a searchable database of
MRLs. Also check out the FAS Home Page for potentially other
interesting stuff.
Registration/Regulatory Affairs Consultants
Information, supplied by the principals, about individuals and companies
conducting registration and regulatory affairs consulting services on a contract
basis.
Pesticide
Formulation Devlopment Labs & Consultants
Information about
individuals and facilities offering their services for development and/or
evaluation of pesticide product formulations (added: Focus Formulation and
Consulting).
Pesticide Process Chemistry/Engineering.
Consultants
Need help with pesticide AI production, or related?
Here are consultants I've found. Know of others(?), send them my way for a free
listing.
Pesticide Chemical Usage and Crop Statistics
Links to federal and state sites containing reports about amounts
of pesticides used in the United States, and about other crop statistics.
Pesticide Contract Formulators/Packagers On The
WWW
Companies I've found with Internet sites.
Federally Registered Pesticides. PAN
Database
PANNA (Pesticide Action Network North America) offers an
excellent on-line database for identifying products using multiple criteria. In
addition PANNA provides toxicology and environmental information for active
ingredients.
Anti-Pesticide Action Groups
Every company involved with pesticide products should be aware of what is
being said and done outside the industry. Plus, they often have the first scoop
on what's new in the industry. Here are several source links.
Resistance Action Committees
RACs do more than track developing
resistance to insecticides, herbicides, and fungicides. One of their services is
classification of pesticidal actives by chemical type and mode of action. {In
the event you're worried: RACs are sponsored and promoted by major pesticide
registrants.}
Pesticide
Product Inert Ingredients
NCAP (Northwest Coalitionfor
Alternatives to Pesticides) site. Inerts in products, obtained via FOIA
requests. Arranged by product active ingredient.
Tracking The Industry, Competitors, AIs, etc.
This 7182 row Excel file, trackyr1.xls, could be
expanded for more individual years. You ought get the idea.
Count Registrations Pre2008, in 2008, in 2009, for Registrant, by All Actives
in products. Now ought be easy to see who has been doing what and is doing what.
Many options to search and sort. E.g., individual registrant, specific AI(s).
And get details. 322 row Excel file, trackyr2.xls,
has CoNr, All AIs, Total Percentage AIs for Y2009 registrations. Easy to glean,
for example, what CoNr 279 is doing with Cypermethrin, zeta-. Many more details
in other PPIS files: Pct each AI in a combination, Sites, Pests...(July 2)
LEAFHOPPERS Sites: Nufarm, Loveland, Control Solutions
The 3 title registrants have LEAFHOPPERS as a top Pest on Y2005-2009 labels.
408 row Excel file, ilh3co_a.xls, has for each
Registrant How Many Labels for Sites on Labels, AND ALL 3 label for the same Site when
LEAFHOPPERS is on the label.
1438 row Excel file, ilh3conaq.xls, is Counts for
Sites where Only 1 or 2 agree on the Site.
FYI: Nufarm labels for 285 Sites which are not on Loveland or Control Solutions
labels.
Loveland labels for 238 Sites the other 2 don't.
Control Solutions labels for 293 Sites the other 2 don't...(June 30)
577 Insects Pests: Y2005-2009 Labels
577 row Excel file, i577.xls, has 577 Insect Pests labeled
at least one of the years. File has Pest Name and Count of Registrations each year
(529 labeled to date in 2009).
522 row Excel file, i577_3co.xls, has Count of
Registrations Total Y2005-2009 for Pest by Registrants Nufarm, Loveland, Control
Solutions (using file posted below in "All Label Pests, Registrants").
107 row Excel file, i577_4i.xls, has Count of Registrations
Total Y2005-2009 for Registrant by Pests APHIDS, CUTWORMS, LEAFHOPPERS, WHITEFLIES.
Other things to do, for example. Get Label Counts for Registrants for LARVAE
pests...(June 30)
All AIs, Percentages
Zip file, aipctall.zip (0.11MB), when extracted returns
the 17192 row (plus Header row) text file aipctall.txt (0.81MB). Current, June,
Registrations: RegNr, Sum of Percentages, Name(s) All AIs. Use this of course to see
who is doing which Pests, Sites, etc...(June 29)
All Label Pests, Registrants
Zip file, pest_all.zip (2.8MB) when extracted will
return the 454504 row (plus Header Title row) (25.6MN) text file pest_all.txt.
Text file has: RegNr, CoNr, Pest Name, Pest Code, 1 letter Pest Tpe, Year Reg
1st Approved (by someone). All Pests for All Registrants on All Labels, as of
June's PPIS files update. Want same for Label Sites? Just ask...(June 29)
Top 56 Insect Pests: Stuffed On 1 Label?
How many of the Top 56 Insect Pests (entry below) can a Company stuff onto a
single label?
5213 row Excel file, itop1lbl.xls, has the answer.
Number of Top 56 Insect Pests, Registrant, Product EPA Reg. Nr., Product Name...
(June 26)
Which Are Most Labeled Insect Pests?
No surprise, answer depends on the question. Entry below I chose by Number of
Products. 56 row Excel file, itop2way.xls, is another
look. (1)Top 50 Insect Pests based on Number of Labels; (2) Top 53 Insect Pests
(ties) based on Number of Registrants. Not a perfect match, but majority agreement.
So, choose your method.
Note: Just because is easy, file includes How Many Different AIs on labels, and
Sole and Combination AIs are counted separately...(June 26)
Who Does How Many Of Top 50 Pests
Find the 50 Insect, Weed, Fungus pests most often on US pesticide labels. Then
find which Registrants labels for how many of each pest. And for sorting find how
many of the Top 150 are labeled by each registrant.
Excel file, 50_ihp.xls, has Counts for Registrants
...(June 26)
If Label Pest = SHEPHERDSPURSE, Then...
Most common Weed on US labels is SHEPHERDSPURSE (OK, ALGAE is more common). 1257
labels, 109 Registrants (as EPA Co. Nr.). Most common Site on labels is NONCROP
AREAS (FOLIAR TREATMENT). 331 labels.
61 row Excel file, spnc_co.xls, has Count of Products
for each of the 61 Registrants with NONCROP AREAS (FOLIAR TREATMENT) label(s).
107 row Excel file, spnc_ai.xls, has Count of Products
for each AI on labels...(June 25)
If Label Pest = ANTS, Then...
Most often named Insect Pest on US labels is ANTS (there are of course many
specific types of ants on labels). 2355 products. Now ask about products.
(i) Most common Site on labels is DOMESTIC DWELLINGS (INDOOR). 1350 products.
(ii) Separate products labeled for DOMESTIC DWELLINGS (INDOOR) and products Not
labeled for DOMESTIC DWELLINGS (INDOOR). 1005 products NOT labeled for INDOOR.
(iii) Ask How Many Products for Active Ingredient labeled for INDOOR and NOT
labeled for INDOOR. Total of 227 AIs, 173 for Indoor, 115 Not for Indoor.
227 row Excel file, ants_ais.xls, Has Count of
Products for Indoor and Not for Indoor labels. Interesting, at least to me, to note
such as only 2 Imidacloprid products labeled for Indoor application
...(June 25)
Label Sites: Industry, Company Comparisons
66 row Excel file, scat_ind.xls, has: Percentage x10
for All Products by All Registrants for Years 2000 and 2009 and Change 2009-2000, for
Label Site Categories.
66 row Excel file, scat_bw.xls, has: Percentage x 10 for
Registrations as of June 2009 for BASF and Whitmire-Microgen (Co. Nr. 499).
Notes: (i) "Percentages x10" as I think (using the Portugues verb achar: to have an
opinion, rather than pensar: think deeply) Integer Numbers are easier to scan than are
decimal values. Prefer decimals? Divide by 10.
(ii) Percentages x10 are just Counting of Labels for each Site Category and then
dividing by Number of All Registrations.
(iii) Easy(?) to see what the Industry as a whole is doing and/or compare
individual companies. Of course, further subdevisions would be for such as Insecticides,
Disinfectants, Insect Repellants, Plant Growth Regulators, etc...(June 24)
How Many Ways To Control...
12 row Excel file, dandeais.xls, has for labels to
control DANDELION: Count of Registered Products, Count of Unique AIs (i.e., either
as Sole AI or as a specific Combination), EPA's Weed name.
878 row Excel file, pre_ais.xls, is for control of weeds
PREEMERGENCE, formatted as above but also includes the Integer ratio
(All Products/Unique AIs)...(June 23)
EPA Reg. Nr. 8033-97: All PPIS Information
153 row Excel file, 8033_97.xls, has PPIS information
for the Subregistration 8033-97-279. I.e., FMC's subregistration of Nippon Soda's
Acetamiprid product. 14 Fields/Columns with all in PPIS (well, OK, I didn't bother to
add the Y2007 subregistration date). Who, What (AI), Where (Label Sites), Why (Label Pests),
Products Names, Formulation Type. Yes, tedious but if wanting ALL in PPIS
...(June 22)
US Subregistrations: Most Successful Primary Registrants
2681 row Excel file, subsever.xls,
has Count of how many of the 244698 once or
now Subregistered pesticide products for the Primary Registrant. At the least this
might suggest to those who don't that subregistrations could be a potential income
source, but you already know that, right! (June 22)
Bifenthrin/Imidacloprid: A Deeper Look
42 row Excel file, bifimid1.xls, has RegNr, Percentage
Bifenthrin and Percentage Imidacloprid in the combination products.
Choose the 7 products from 4 registrants with Bifenthin @0.16% / Imidacloprid
@0.2%.
118 row Excel file, bifimid2.xls, has How Many of the
7 products are labeled for What Pest.
26 row Excel file, bifimid3.xls, has How Many of the
7 products are labeled for What Site. Easy to find for example, which are (or not)
labeling for the Airport site...(July 1)
5 Majors: Exclusive AI And Percentage
1055 row Excel file, 5majexcl.xls, has: AI(s),
Total Percentage, Registrants, Year of 1st registration.
Only a BBDS company registers the AI(s) at the Total Percentage
...(July 1)
5 Majors: Others with Same AI And Percentage
For those wondering about what BBDDS (BASF, Bayer, Dow Agro, DuPont, Syngenta)
are doing. One answer is to ask "Who does the same".
429 row Excel file, aimajoth.xls, has How Many Other
Registrants have labels for Exactly the Same AI and Total Percentage.
E.g., you might note 2 for 9% Bifenthrin / Imidacloprid. Go look and find the
two are Nufarm and Ensystex IV...(June 30)
GLASSYWINGED SHARPSHOOTER: 224 Products To Control
Only because this is my current favorite pest name.
Excel file, gwssprod.xls, has information, including %AI(s).
..(June 29)
IMIDACLOPRID: <1%, ASIATIC GARDEN BEETLE LARVAE - Sites
527 row Excel file, imidsite.xls, has Sites on the 59
Labels with Imidacloprid as Sole AI at <1%.
File has: Count of RegNr, Actual Percent Imidacloprid, Label Site.
Obviously not much agreement on Site labeling, it seems to me...(June 29)
If Percentage Imidacloprid = ... Then Pests Are...
From reducing All Percentages of AIs to decade-like range values (i.e., <1,
1-10, 10-20, 20-30, 30-40, 40-50, 50-60, 60-70, 70-80, 80-90, 90-100, 100). Always
possible, in a relational database, to work back to an individual REG_NR.
301 row Excel file, imidpest.xls, has How Many
Products with Imidacloprid as the Sole AI and decade-like Percentage AI are labeled
for each Insect pest.
(FYI: Table of All Pests, All RegNr, All AI(s), All Percentages is just 454504 rows).
As you know *LARVAE* are most often on Imidacloprid as Sole AI labels...(June 29)
US Subregistrations: Brand Names
Zip file, subbrand.zip (1.4MB) when extracted will
return the 59542 row Excel file, subbrand.xls (5.8MB). Currently registered, says
PPIS, subregistered products. File has EPA Reg. No. for primary registrant, primary
registrant's Brand Name, Brand Names(s) used by SubRegistrants (June 22)
Foreign Production Reports To EPA; Y2002-2007
Reducing to unique entries as much as possible and converting various EPA files
from SSTS (Section Seven Tracking System), and adding PPIS information,
still leaves appreciable sludge. From my Access database some information.
12 row file, ssts_chk.xls, all entries for the Czech
Republic Establishments.
66 row file, sstssite.xls, count of RegNr by reporting
country.
5 row file, sststype.xls, count of RegNr by product
type.
595 row file, ssts_co.xls, count of RegNr by product
registrant.
FYI: SSTS Y2007 report has 40000+ entries, of which about 1200 are from foreign
Establishments, the remainder from US Establishments...(May 29)
US Production Reports To EPA: Y2007, Some Contract Facilities
276 row file, ssts_pkg.xls, is from EPA SSTS file for
Y2007 reports. My version, the EPA sending this year's report as a PDF document.
Reporting Establishments in the US where the Establishment name contains "packaging".
94 row file, sstsshrm.xls, is also from Y2007. All
entries, in my database, for Schirm USA, a presumptive contract formulator/packager.
FYI: In the 40000+ reports for Y2007 the most most often occurring product is
Monsanto's RegNr 000524-00539, which is named in PPIS files
"ROUNDUP ORIGINAL 2K HERBICIDE". In the SSTS reports there are 190 variations on
the product name, the most common (1297) being "ROUNDUP ORIGINAL MAX".
FYI: There are in the SSTS 2007 report 11648 unique RegNr. There are 18476 unique
Product Names. Not all reporters are using the same Name for the same RegNr!
...(May 26)
All Establishments with SSTS Reports: Y2007
6710 row file, ssts_est.xls, has How many reports
(as RegNR), Establishment Name, and for foreign (non-US) Country Code.
Note the majority are Cooperatives, Dealers, Warehouses.
I've tried to convert, where they appear, EPA's 2-letter Country Codes to
standard 3-letter Codes, not catching all.
Uses? E.g., other than finding such as Chinese or Indian or Mexican facilities,
names such as "...Packaging..." or "...Formulation..." or "Aerofil", etc. to find
custom Formulators/Packagers...(May 31)
SSTS Y2007: All DUPONT Entries
1161 row file, ssts_dup.xls, has summaries for any
DUPONT RegNr (Co Nr 352) reported and any report from a DUPONT Establishment using
Est Nr 000352,whether or not doing for DUPONT.
Note: DUPONT uses up to 7 EstNr, other than 000352, and they're in SSTS Y2007
reports, perhaps even in the above file!...(June 1)
Q1 (January-March)
Note: EPA-OPP's PPIS files are never entirely up to date. Some information for
some products is always missing, being added in future updates.
Note: Files are Excel version 5, as exported from Access.
09q1name.xls, has the 235 product names.
09q1regs.xls, has count of registrations for
registrants.
09q1aico.xls, has count of active ingredients for
registrants.
09q1pest.xls, has counts of registrations for pests.
09q1site.xls, has count of registrations for site
subcategory.
09q1pcts.xls, has count of registrations for
percentage active ingredients...(May 5)
0. Downloading and Importing PPIS Data files:
i. Download the Zipped database and data files. UnZip/Extract. {New files posted
each month at
http://www.epa.gov/opppmsd1/PPISdata/index.html and also read the
explanatory text files for file structure, field names, what's in the file.}
ii. Open the database, then do File | Get External Data | Import
iii. Go find the data text file (*.txt). Double-click its name
iv. Import Wizard window. If the "Fixed-width" button is not selected click
on it
v. Click the "Advanced" bar. Click the "Specs" bar
vi. Double-click the "ImportSpecification..." name which matches the text file
name, or highlight the name and click the "Open" bar
Click the "OK" bar
Click the "Next" bar until Access asks where to put the data. Select "In an
existing table" and browse to find and double-click the desired table name
"Next" and "Finish". Access should report successful Import.
1. Import the PPIS product.txt file (basic information), Run a few Queries,
Find All currently Active Registrations.
This Zip file, ppisimp1.zip, extracts to ppisimp1.mdb.
This Zip file, product.zip, extracts to the PPIS data
Fixed-width file product.txt.
ppisimp1.mdb is all set up to Get the 90,451 entries for all 90,451 products in
product.txt. Everything, nominally, ever registered with EPA-OPP. How to Get after
Opening/Starting Access 2002 (if your newer version looks different give a shout and
perhaps I can help)? Ready-to-Run queries will fill a Table with January's 17,096
active registrations.
Importing Data Text File: Go to the top of this section and follow the
instructions. If any problem send me a message.
Time to Run Queries. They're all written ready-to-go. Look at them, and/or Tables
in Design view if interested. Double-click a Query name to Run/execute the query.
First one extracts CO_NR and PROD_NR from REG_NR, adding them to the named fields in
the table. Second Query combines CO_NR and PROD_NR and puts that into the EPA REG
NR field. The third Query Finds Active Registrations and puts them into
the Active Registrations table. The fourth Query Finds TGAI registrations.
You can change this Temporary Results Query to a Make Table, permanent, one.
2. Import the PPIS company.txt (registrant/subregistrant information) file.
Run Queries. Count Products By Registrant Name
Zip file ppisimp2.zip extracts to ppisimp2.mdb.
Zip file company.zip extracts to company.txt.
Access database has the previous data Tables and Queries and Import
Specifications. Plus one new table for (Sub)Registrant identification and address
information, with a ready-to-use Import Specification. Plus two new Queries: Count
active products (as EPA Reg Nr) for each of the 1693 Registrant Names and Company
Numbers; combine City State Zip Code information in one field as for mailing and/or
report writing.
Important! To use the Counting Query you must Import the product.txt
file (see 1. above) and run the Queries to Find Active Registrations.
Note: Company file has the field CO_NAME2 which is invariably empty (hold over
from pre-2004 versions of PPIS).
3. Copy tbl_Product, with data, to another database.
i. You imported product.txt into tbl_Product in ppisimp1.mdb. You want to try
importing company.txt into ppisimp2.mdb, but hate redoing the import of product.txt.
ii. No problem. (a)Open ppisimp2.mdb. (b)Do "File | Get External Data | Import".
(c) The default choice is an Access database. Go find where you've stored
ppisimp1.mdb and double-click its name. (d) Access shows you everything in the
database and gives you options. (e) Highlight the tbl_Product name and click the
Options bar. You ought see under Import Tables that the "Definition and Data" button
is selected. (f) Click on "OK". You will be back to the ppisimp2.mdb screen and will
see tbl_Product (the original table) and tbl_Product1. Look in tbl_Product1 to be
assured it contains all 90,000+ products information. (g) Delete tbl_Product (the
original, empty table). Highlight then Rename tbl_PRODUCT1 to tbl_PRODUCT
(Edit | Rename). The now renamed tbl_Product1 has become a PERMANENT part of
ppisimp2.mdb.
{FYI: The "File | Get External Data | Link..." choice is how you can
TEMPORARILY attach a piece (e.g., a table) of one database to another database.}
4. Import All 18 PPIS Data Text Files
Zip file, ppisimp3.zip, extracts to ppisimp3.mdb.
This Access 2002 database is set up with Tables and Specs (Import Specifications)
for each of the 18 data files (after extract *.txt from *.zip) posted each month
on EPA-OPP's PPIS Web site. You need know nothing other than as described above
"How to Import". Import as many or as few as might be of interest.
Note #1: EPA-OPP says REG_NR are "Numeric file type". My database is set up
(Tables and Specs) to import REG_NR as "Text file type". For now, but you could ask
for details, just accept and use what I've done.
Note #2: I've named Table and Import Specifications (Specs) just as are in
EPA-OPP's PPIS Web site explanatory notes. The Access database I use for PPIS has
entirely different names because I find them easier to remember.
Note #3: Should you import the agent.txt file. Table is exactly the same as for
company.txt, so tell Access to use the "COMPANY IMPORT SPECIFICATION" for stuffing
text file into tbl_AGENT.
Note #4: formula.txt is where you find Percentage of each AI in each product.
EPA-OPP's text file has percentage as a 4-decimal place, 7 digit number
(e.g., 0441500). To convert this to a number with decimal point (e.g., 44.15) just
divide by 10000 (using of course the Access Build function). ?? Ask and ye shall
receive instruction/example.
Note #5: You've imported some *.txt files. You want to ask for example
"What are the SITES and/or PESTS for EPA Reg NR 279-3133". tbl_PRODSITE and
tbl_PRODPEST come up with: REG_NR and SITE_CODE or PEST_CODE. Say what? Access is a
RELATIONAL DATABASE program (nominally). About one-half of the PPIS files are
DICTIONARIES/CONVERTERS. Don't ask why (there are excellent reasons). The
DICTIONARIES/CONVERTERS translate CODE to a NAME. A simple drag-and-drop Query.
Note #6: Did I mistype something when doing all the Table and Import
Specification Designs? Do you have a question? Just ask.
5. Using Access: Nicosulfuron and zeta-Cypermethrin
Zip file, cypznico.zip, extracts to the 890KB
Access 2002 database file,cypznico.mdb. Only the 2 Actives, to keep the file small
but the examples apply to everything in PPIS. How To Find Out About Specific
Products. In this tutorial you can see how to find out about Nicosulfuron (do zeta-
Cypermethrin on your own).
The database is populated with Data Tables and ready-to-run Queries. Here are
the Tables and Queries: cypznico0.pdf shows the Data
Tables; cypznico1.pdf shows the Queries.. Table Design
are those I use in my Access version 2 for PPIS. The Queries begin by selecting
Nicosulfuron products, continuing on to ask questions about Registrants, Sites, Pests,
Percentage AI(s), etc.
Note #1: You ought look at each Query in Design View. You should Run each Query
(double-click on Query name or in Design View click on the Run, !, icon) as several
require a Table Made from a previous Query.
Note #2: Data is as appears in EPA-OPP's March 2009 web-posted files. Caveat
Lector!!
Note #3: An Access 2002 "enhancement" (hah). Design for the Query which finds
End-Use products. Criterion for FORM (Formulation Type Code) I typed as:
not 1 and not 2. In Saving the Make Table Query Access converted "not" to the Formal
Logic symbol "< >", i.e., Not Equal To. This, I believe, merely makes the Query
look more confusing to the new or occasional user of Access.
Note #4: Two of the Tables (AI Common Name and All AIs) are my doing.
AI Common Name I use to convert PC Code Number to my preferred name. All AIs (on
one line) is from another of my Access databases (simple, quick 4 Queries) using
PPIS files.
For further information, or to discuss a specific project, contact Bruce
McKay:
e-mail your request to bmckay@ix.netcom.com
(that's bmckay@ix.netcom.com).
All original materials are copyright © 1996 - 2008 by Bruce M. McKay, and all rights are reserved.
The URL for this page is http://www.bmckay.com