A Painless Introduction to MS Access Databasing by wwr69367

VIEWS: 0 PAGES: 44

									A Painless Introduction
to MS Access Databasing

   Yannick Pouliot, PhD
   Bioresearch Informationist
   lanebioresearch@stanford.edu
   Lane Medical Library & Knowledge Management Center
   4/20/07




   Lane Medical Library & Knowledge Management Center
   http://lane.stanford.edu
Preliminaries

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




    Lane Medical Library &
    Knowledge Management Center
                                                          2
    http://lane.stanford.edu
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

    Lane Medical Library &
    Knowledge Management Center
                                      3
    http://lane.stanford.edu
Resources – MS Access




                               Available as eBook   Available as eBook




 Lane Medical Library &
 Knowledge Management Center
                                                                         4
 http://lane.stanford.edu
Resources – Excel/MS Query




In Lane catalog




 Lane Medical Library &
 Knowledge Management Center
                               5
 http://lane.stanford.edu
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




    Lane Medical Library &
    Knowledge Management Center
                                                                            6
    http://lane.stanford.edu
                Why Bother
                with MS
                Access?

Lane Medical Library &
Knowledge Management Center
                              7
http://lane.stanford.edu
Why MS Access?

   More: http://office.microsoft.com/en-
    us/access/HA010429181033.aspx




    Lane Medical Library &
    Knowledge Management Center
                                            8
    http://lane.stanford.edu
                Definitions,
                Definitions…


Lane Medical Library &
Knowledge Management Center
                               9
http://lane.stanford.edu
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

    Lane Medical Library &
    Knowledge Management Center
                                                                                     10
    http://lane.stanford.edu
Table and Field Definitions
How It Looks In Access…




  Lane Medical Library &
  Knowledge Management Center
                                11
  http://lane.stanford.edu
Main Relational Database Engines

 Oracle
   MS Access
   MS SQL Server
   Filemaker
   MS SQL Server

 MySQL
   Postgress
   Sybase


    Lane Medical Library &
    Knowledge Management Center
                                   12
    http://lane.stanford.edu
                      Understanding
                      Relational
                      Databases

Lane Medical Library &
Knowledge Management Center
                                      13
http://lane.stanford.edu
Understanding the Relational Principle: A
Simple Database

                                  “join”




   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
    Lane Medical Library &
    Knowledge Management Center
                                                           14
    http://lane.stanford.edu
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




    Lane Medical Library &
    Knowledge Management Center
                                                   15
    http://lane.stanford.edu
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


    Lane Medical Library &
    Knowledge Management Center
                                                          16
    http://lane.stanford.edu
Example: The Schema for SRI’s
BioWarehouse System




 Lane Medical Library &
 Knowledge Management Center
                                17
 http://lane.stanford.edu
Querying MS Access
or any relational database…




Lane Medical Library &
Knowledge Management Center
                              18
http://lane.stanford.edu
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)




    Lane Medical Library &
    Knowledge Management Center
                                                   19
    http://lane.stanford.edu
Introducing The SQL Select Statement

                                   SELECT LastName, FirstName
   Good news: This is the         FROM Patients
    only SQL statement
    you need to understand
    for querying
   Exercise: run
    SuperSimpleQuery1 in
    Simple1 database




     Lane Medical Library &
     Knowledge Management Center
                                                            20
     http://lane.stanford.edu
Basic Syntax of Select Statement
SELECT field_name
 FROM table
 [WHERE condition]                         [ ] = elective

Example:

   Select LastName,FirstName
   From Patients
   Where Alive = „Y‟;

Important: Everything is MS Access is case sensitive
   LastName ≠ lastName


   Lane Medical Library &
   Knowledge Management Center
                                                            21
   http://lane.stanford.edu
         Realistic
         Querying With
         the MS Access
         Query Builder

Lane Medical Library &
Knowledge Management Center
                              22
http://lane.stanford.edu
A More Complete Description of Select
Statement
SELECT [DISTINCT|COUNT] field_list
 FROM table_list
 [WHERE conditions]
 [GROUP BY field_list]
 [ORDER BY field_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”)
   Lane Medical Library &
   Knowledge Management Center
                                                      23
   http://lane.stanford.edu
Meaningful Queries in Acces
Exercises - Database: Simple2.mdb
  1.     Returning ordered records
  2.     Counting records
         LifetimeNumberFAQs1
                   Exercise: how many FAQs per day?
             LifetimeNumberFAQs2
                   Demo: creating Pivot Tables
  3.     Generating averages
         Querying from a query!
  4.     Change order of columns
  5.     Change sorting of rows


  Lane Medical Library &
  Knowledge Management Center
                                                       24
  http://lane.stanford.edu
           Uploading
           Data in MS
           Access

Lane Medical Library &
Knowledge Management Center
                              25
http://lane.stanford.edu
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
    Lane Medical Library &
    Knowledge Management Center
                                                           26
    http://lane.stanford.edu
Creating an Access DB from Excel

   One table per Excel worksheet
   Demo: http://office.microsoft.com/en-
    us/access/HA012013211033.aspx




    Lane Medical Library &
    Knowledge Management Center
                                            27
    http://lane.stanford.edu
Cool Things You Can Do In Access

   Store entire files in records
        Excel, Word, sounds, pictures anything




    Lane Medical Library &
    Knowledge Management Center
                                                  28
    http://lane.stanford.edu
                         Extras


Lane Medical Library &
Knowledge Management Center
                                  29
http://lane.stanford.edu
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/HA011199501
    033.aspx
    Lane Medical Library &
    Knowledge Management Center
                                  30
    http://lane.stanford.edu
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
    Lane Medical Library &
    Knowledge Management Center
                                                                        31
    http://lane.stanford.edu
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



    Lane Medical Library &
    Knowledge Management Center
                                                              32
    http://lane.stanford.edu
                     Yannick Pouliot, PhD
                     lanebioresearch@stanford.edu



Lane Medical Library &
Knowledge Management Center
                                                    33
http://lane.stanford.edu
                    The Nitty-
                    Gritty Details


Lane Medical Library &
Knowledge Management Center
                                     34
http://lane.stanford.edu
How to Query Using MS Query

Steps
1. Make sure you have the requisite driver (next
        slide)

2.      Create a Data Source Name (Windows only)
3.      Write your query
4.      Get the results back into Excel!
                                   See Lane videorecorded class Managing
                                   Experiment Data Using Excel and Friends:
                                   Digging Out from Under the Avalanche for lots
                                   more details.
     Lane Medical Library &
     Knowledge Management Center
                                                                                   35
     http://lane.stanford.edu
Step1: Getting Drivers
Essential 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
    Lane Medical Library &
    Knowledge Management Center
                                                     36
    http://lane.stanford.edu
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




    Lane Medical Library &
    Knowledge Management Center
                                                          37
    http://lane.stanford.edu
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
    Lane Medical Library &
    Knowledge Management Center
                                                 38
    http://lane.stanford.edu
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


    Lane Medical Library &
    Knowledge Management Center
                                                                      39
    http://lane.stanford.edu
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…

    Lane Medical Library &
    Knowledge Management Center
                                                                          40
    http://lane.stanford.edu
Resources – SQL
The Language to Query Relational Databases


   Beginning SQL, Wilton P & Colby JW: E
    http://jenson.stanford.edu/uhtbin/cgisirsi/5AG
    uKeptoD/GREEN/59960102/9#holdings
   Oracle SQL*Plus, Gennick, J.
   Beginning MySQL: e-book
    http://site.ebrary.com/lib/stanford/Doc?id=101
    14227


    Lane Medical Library &
    Knowledge Management Center
                                                 41
    http://lane.stanford.edu
Lane Medical Library &
Knowledge Management Center
                              42
http://lane.stanford.edu
The Relational Principle in Action

   Related records can be found using a shared
    key
        Example: Patients.ID = Visits.PatientID




    Lane Medical Library &
    Knowledge Management Center
                                                   43
    http://lane.stanford.edu
SQL Querying…With What?




                                                                                    ?
                                                                                  er
                                                                               ag
                                                                  e




                                                                            an
                                                               Us
                                              s
                                            se




                                                                           M
                                                           of




                                                                                              ?
                                          ba




                                                                       BC




                                                                                            er
                                                          se
                                        ta




                                                                                          iv
                                                                       D
                                     Da




                                                        Ea




                                                                                        Dr
    Tool




                                                                      O
    MySQL Query Browser            MySQL only             High              No          Yes
    MS Query                       All (e.g., Oracle)      OK               Yes         No


          Other query browsers exist but are more sophisticated
              = more complex
              Example: PL/SQL Developer, from Allround Automations




     Lane Medical Library &
     Knowledge Management Center
                                                                                                  44
     http://lane.stanford.edu

								
To top