Master Species List Updates – March 2015

Master Species List(710 records originally)

  • Parse scientific name to obtain Genus/Species/Variety as individual fields
  • Rename [Scientific] field to [Name_Original]
  • Rename [Species] to [Species_Old] (this is the old [Species] code field)
  • Create new fields:
  • [Symbol]to indicate the new USDA based code field – replaces [Species], consider renaming (symbol is used by USDA)?
  • [National_Common] for comparison to existing common names
  • [Target_Weed] to indicate records on the Parks Target Weed list
  • [Noxious_Class] to import this info from the Target Weed list
  • [USDA_Status] to indicate current decisions used by EarthCorps to updated USDA Symbols - see below for descriptions
  • [Update_Notes_2015] for general questions or notes for consideration

Import USDA symbols into master table and integrate Target Weed List:

  1. Convert and import all excel files into an Access
  2. Query USDA data tables to only show accepted names (remove all synonymous species entries)
  3. Join with Master and import symbols and common names – indicate as “Clean” in [USDA_Update] field
  4. Query USDA data tables to only show synonymous species entries”
  5. Run query to show all Master species that match directly with Genus/species/variety fields and import [Symbol] and [State Common] data into Master table - indicate as “Synonym”
  6. These records will need to be updated (genus/species) to the USDA accepted species nomenclature.
  7. Note that USDA .txt files make it very difficult to parse out var. and ssp. records.
  8. Update records with existing [National_Code] if field is populated and is not covered in above steps.
  9. Indicate these as “Existing” updates in “USDA Status” field
  10. Check and update those records where new [Symbol] does not equal existing National Code field and update as necessary (add varieties as appropriate)
  11. Note that many discrepancies were found, will need to check all records indicated as “existing” for completeness…
  1. Clean and check fields and delete duplicates (not including tree/shrub entries)

[USDA Status] key:

Clean= imported directly from NRCS List

Synonym= Symbol updated through joined table, but nomenclature represents a known synonym (nomenclature needs to be updated in Master)

Name Change= synonym: new nomenclature and symbol updated manually from USDA

USDA= manually looked up in USDA and imported symbol (may not be accurate for WA, or may indicate unlisted varieties)

Existing= copied directly from existing [National_Code] field, no QA/QC

Variant= national code represents the general Genus and Species, although USDA does not recognize the listed variant

Check Var= imported code does not match existing [National Code] field and may need further delineation

N/A= not recognized in USDA…

Exclude= non-species records

Next steps

  • Manually check “Existing” records for Symbols and Common Names (80)
  • Manually update all “synonym” records(16)
  • Determine how to resolve “N/A” records for species not recognized by USDA (3)
  • Manually check duplicate [Symbol] entries and rename (Tree/Shrub entries etc.) as necessary (26)
  • Update Invasive fields?
  • Update wetland indicator status?
  • Update all existing databases (or only current data?) to reflect these changes…