A Painless Introduction to MS Access Databasing

1. A Painless Introduction to MS Access Databasing Yannick Pouliot, PhD Bioresearch Informationist lanebioresearch@stanford.edu Lane Medical Library & Knowledge Management Center 4/20/07

2. Preliminaries • Launch MS Access • Open Simple1.mdb • File/Open • Using the Help  don’t neglect it! • Exercise: ask the Office Assistant for “select syntax” 2

3. Contents • Familiarization with MS Acces • Understanding a simple database • Basics of SQL querying • Creating a simple database • Loading database via MS Excel • Tools for running SQL queries • Installation (Windows) • Selected databases • Resources & cheat sheet 3

4. Resources – MS Access Available as eBook Available as eBook 4

5. Resources – Excel/MS Query In Lane catalog 5

6. 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 a database from Excel (5 min): http://office.microsoft.com/en-us/assistance/HA012013211033.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 6

7. Why Bother with MS Access? 7

8. Why MS Access? • More: http://office.microsoft.com/en-us/access/HA010429181033.aspx 8

9. Definitions, Definitions… 9

10. Definitions • Database: Collection of tables  • Table • Collection of records that share a common fundamental characteristic • E.g., patients and locations can each be stored in their own table • Schema • A view that inter-relates tables to each other • Record • Basic unit of information in a relational table • E.g., 1 record per person • A record is composed of fields • Query • Set of instructions to a database “engine” to retrieve, sort and format returning data. • E.g., “find me all patients in my database” • Database engine • Software application that stores the data and enables querying 10

11. Table and Field DefinitionsHow It Looks In Access… 11

12. Main Relational Database Engines • Oracle • MS Access • MS SQL Server • Filemaker • MS SQL Server • MySQL • Postgress • Sybase 12

13. Understanding Relational Databases 13

14. “join” Understanding the Relational Principle: A Simple Database • Every patient gets ONE record in the Patients table • Every visit gets ONE record in the Visits table • Rows in different tables can be related one to another using a shared key (identifier  number unique to table) • There can be multiple visits records for a given patient • There can be multiple tissue records for a given patient 14

15. The Relational Principle in Action • Related records can be found using a shared key • Shared key = identifier that is: • unique to each table • Can be referenced by another table • Example: Patients.ID = Visits.PatientID 15

16. The Database Schema: Your Roadmap For Querying • The schema describes all tables and all fields • Describes relationships between tables • Crucial in enabling retrievall of desired data • Simple example: • Very important • Must understand schema for accurate querying • Wrong understanding = wrong results 16

17. Example: The Schema for SRI’s BioWarehouse System 17

18. Querying MS Accessor any relational database… 18

19. How Relational Databases Are Queried • Querying = extracting information out of the database • … and into something, e.g., Excel • This is done using the Structured Query Language (SQL) 19

20. Introducing The SQL Select Statement SELECT LastName, FirstName FROM Patients • Good news: This is the only SQL statement you need to understand for querying • Exercise: run SuperSimpleQuery1 in Simple1 database 20

21. [ ] = elective Basic Syntax of Select Statement SELECT field_name FROM table [WHERE condition] Example: Select LastName,FirstName From Patients Where Alive = ‘Y’; Important: Everything is MS Access is case sensitive  LastName ≠ lastName 21

22. Realistic Querying With the MS Access Query Builder 22

23. A More Complete Description of Select Statement SELECT [DISTINCT|COUNT] field_list FROM table_list [WHERE conditions] [GROUPBY field_list] [ORDER BYfield_list [ASC | DESC] ]  more: http://office.microsoft.com/en-us/access/HP010322651033.aspx?pid=CH010410171033 Includes examples and descriptions of each SELECT operator (e.g., “group by”) 23

24. Meaningful Queries in Acces Exercises - Database: Simple2.mdb • Returning ordered records • Counting records • LifetimeNumberFAQs1 • Exercise: how many FAQs per day? • LifetimeNumberFAQs2 • Demo: creating Pivot Tables • Generating averages • Querying from a query! • Change order of columns • Change sorting of rows 24

25. Uploading Data in MS Access 25

26. Importing Data Into Acces From Access Important • Column headers in Excel file must match those of columns in Access target table • No spaces, weird characters • Must ensure that data in Excel columns is of correct type (text, number, date) • Blanks (not empty cells!) are nasty • Exercise: Loading data via Excel • File: LoadingData.xls • Demo: http://office.microsoft.com/en-us/access/HP010950951033.aspx 26

27. Creating an Access DB from Excel • One table per Excel worksheet • Demo: http://office.microsoft.com/en-us/access/HA012013211033.aspx 27

28. Cool Things You Can Do In Access • Store entire files in records • Excel, Word, sounds, pictures anything 28

29. Extras 29

30. Creating Forms Using MS InfoPath • Really the easiest way to create interfaces to Access • 10 min of work • Probably 30 for you  • More: http://office.microsoft.com/en-us/access/HA011199501033.aspx 30

31. Querying With MS Excel • MSQuery, an unknown hero • Free • Facilitates writing of a SQL query  graphical • First, need to find it! • Search for “MSQRY32.EXE” using “Search for Files or Folders” • Search hidden files and folders • On my disk, it is located in C:\Program Files\Microsoft Office\OFFICE11 • Once you find it, create a shortcut to it and rename it e.g. MSQuery • move the shortcut to a desired location • Also needed: creating a data source name 31

32. To Conclude… • If using Excel is a headache, use MS Access • Access can work very well on its own • reasonably easy to learn/use.. • Lots of free resources to quickly learn how to use it • MS Access + Excel = dynamite 32

33. Yannick Pouliot, PhD lanebioresearch@stanford.edu 33

34. The Nitty- Gritty Details 34

35. How to Query Using MS Query Steps • Make sure you have the requisite driver (next slide) • Create a Data Source Name (Windows only) • Write your query • Get the results back into Excel! See Lane videorecorded class Managing Experiment Data Using Excel and Friends: Digging Out from Under the Avalanchefor lots more details. 35

36. Step1: Getting DriversEssential for SQL Querying • A driver is a piece of software that lets your operating system talk to a database • Installed drivers visible in ODBC manager • “data connectivity” tool • Each database engine (Oracle, MySQL, etc) requires its own driver • Generally must be installed by user • Drivers are needed by Data Source Name tool and querying programs • Require (simple) installation 36

37. MySQL Driver: Needed to Query MySQL Databases • Windows: Download MySQL Connector/ODBC 3.51 here • Must be installed for direct querying using e.g. Excel • Not necessary if you are using the MySQL Query Browser 37

38. Oracle Driver: Needed to Query Oracle Databases • Installing “client” software will also install driver • Windows: Download 10g Client here • Mac: Download 10g Client here • Free Oracle user account required to download • Must be installed if you are querying using MS Query or any other query browser involving Oracle 38

39. Step 2: Creating a Data Source Name • A Data Source Name (DSN) tells programs on your PC where and how to query a database • Populating the fields: • Data Source Name: Unique name of your choice • Description: anything • Server: exactly as given by the database provider • Port number: as specified by database provider • Defaults: MySQL: 3306; Oracle: 1521; MS Access: N/A 39

40. Words of Caution • Easy to build queries that • Retrieve nonsense • Never complete, end up completely bogging down the database • Scotty to Captain Kirk: “Where going in circles, and at warp 6 we’re going mighty fast…” • Understanding schema is only way to prevent that • Not always easy… 40

41. Resources – SQLThe Language to Query Relational Databases • Beginning SQL, Wilton P & Colby JW: Ehttp://jenson.stanford.edu/uhtbin/cgisirsi/5AGuKeptoD/GREEN/59960102/9#holdings • Oracle SQL*Plus, Gennick, J. • Beginning MySQL:e-book http://site.ebrary.com/lib/stanford/Doc?id=10114227 41

42. 42

43. The Relational Principle in Action • Related records can be found using a shared key • Example: Patients.ID = Visits.PatientID 43

44. SQL Querying…With What? • Other query browsers exist but are more sophisticated • = more complex • Example: PL/SQL Developer, from Allround Automations 44