1. 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!

2. 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

3. Query Wizards

4. Select Tables and Fields

5. Missing Relationships

6. FIA Relationships

8. Query Options

9. Add Tables

10. Add Tables (con’t)

12. Adding Joins Drag to Join Tables

13. 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;

14. Add Field to Select

15. Select the field

16. Final Query – Wizard View

17. Datasheet View

18. SQL View

19. Sorting

20. Filtering

21. MS-Access • Access uses “*” for a string wildcard • Fieldnames are in brackets (sometimes) • First “INNER JOIN” must be in parenthesis

22. 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;

23. Export Data