Transcript Using MS-Access with the FIA plot database

FIA
• Forest Inventory Analysis
• Plots in forests over the US
– Coordinates “fuzzed” at 1km
– Tree species and characteristics
– Resampled every decade
• FIA database:
– Downloadable by state
– CSV files or MS-Access
– “CN” for primary keys
– Includes complete database dictionary!
MS-Access
• Part of MS-Office professional
• Relational database
– No concurrent access
– No spatial extensions
• Has some unique characteristics
– Brackets on field names: [Field1]
• “Easy to Use” wizards for queries
– Can get to SQL statements behind the
wizards
Query Wizards
Select Tables and Fields
Missing Relationships
FIA Relationships
Query Options
Add Tables
Add Tables (con’t)
Adding Joins
Drag to Join Tables
SQL View
SELECT PLOT.[CN]
FROM
(PLOT INNER JOIN TREE ON
PLOT.CN = TREE.PLT_CN)
INNER JOIN REF_SPECIES ON
TREE.SPCD =
REF_SPECIES.SPCD;
Add Field to Select
Select the field
Final Query – Wizard View
Datasheet View
SQL View
Sorting
Filtering
MS-Access
• Access uses “*” for a string wildcard
• Fieldnames are in brackets (sometimes)
• First “INNER JOIN” must be in
parenthesis
Final Query
SELECT PLOT.[CN], PLOT.LAT, PLOT.LON,
REF_SPECIES.COMMON_NAME, TREE.HT
FROM (PLOT INNER JOIN TREE ON
PLOT.CN = TREE.PLT_CN)
INNER JOIN REF_SPECIES ON
TREE.SPCD = REF_SPECIES.SPCD
WHERE (((REF_SPECIES.COMMON_NAME)
Like 'Doug*') AND ((TREE.HT)>200))
ORDER BY TREE.HT DESC;
Export Data