Resources – MS Access Free Online Training Resources

1. Resources – MS Access Free Online Training Resources • Using an Access database to store and information (2 min) http://office.microsoft.com/en-us/assistance/HA011709681033.aspx • Creating tables in Access (50 min): http://office.microsoft.com/training/training.aspx?AssetID=RC061183261033 • Writing queries (50 min): http://office.microsoft.com/training/training.aspx?AssetID=RC010776611033 • Youtube • http://www.youtube.com/view_play_list?p=D1836790490DF1AD&search_query=ms+access+2003+tutorial

2. Definitions, Definitions… • Database: Collection of tables and queries • Table (or relational table) • Collection of records that share a common fundamental characteristic • Record • A record is composed of fields • Query • Set of instructions to a database “engine” to retrieve, sort and format returning data. • “find me the # of housing sales in my database”

3. What is the difference between a table and a query?

4. What is the difference between tables and queries?

5. Create MS Access database

6. Querying MS Accessor any relational database… • Querying = extracting information out of the database • This is done using the Structured Query Language (SQL) Select PARCELID, ADD2, SQFT, LANDUSE From PARCELS Where SQFT>5000 Select fields Select table Select records

7. Example table

8. Querying MS Accessor any relational database… • Example: • Wildcard characters: ‘*’ ‘%’ • AND vs. OR Select PARCELID, ADD2, SQFT, LANDUSE From PARCELS Where LANDUSE like ‘C’ OR LANDUSE like ‘R*’ OR LANDUSE like ‘A’ Select fields Select records

9. Querying MS Accessor any relational database… • What if you want to query two tables? Ownership Parcel What will happen if you don’t match up the tables with owerid? A shared key • Shared key (ownerid) that is: • 1. unique to each table • 2. can be referenced by another table

10. SELECT PARCELS.PARCELID, PARCELS.SQFT, OWNERS.OWNERNUM, PARCELS.ONUM, OWNERS.ONAME FROM OWNERS INNER JOIN PARCELS ON OWNERS.OWNERNUM = PARCELS.ONUM WHERE (((PARCELS.SQFT)>5000));

11. From queries to tables

12. Import MS-Access tables to ArcGIS -Use MS Access database provider to talk to MS-Access

13. 1. Specify the location of your database 2. Test connection 3. Click “OK”

14. Miscellaneous • One OLE DB connection will create only one connection to the database. • ArcGIS will remember the connection for you.