Ms Access Library Management System

							A Painless Introduction
to MS Access Databasing

   Yannick Pouliot, PhD
   Bioresearch Inf ormationist
   lanebioresearch@stanf ord.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 &
    Know ledge 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 &
    Know ledge Management Center      3
    http://lane.stanford.edu
Resources – MS Access




                                Available as eBook   Available as eBook




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




In Lane catalog




 Lane Medical Library &
 Know ledge 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 &
    Know ledge Management Center                                             6
    http://lane.stanford.edu
                 Why Bother
                 with MS
                 Access?

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

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




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


Lane Medical Library &
Know ledge 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 &
    Know ledge Management Center                                                      10
    http://lane.stanford.edu
Table and Field Definitions
How It Looks In Access…




  Lane Medical Library &
  Know ledge 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 &
    Know ledge Management Center   12
    http://lane.stanford.edu
                       Understanding
                       Relational
                       Databases

Lane Medical Library &
Know ledge 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 &
    Know ledge 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 &
    Know ledge 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 &
    Know ledge Management Center                          16
    http://lane.stanford.edu
Example: The Schema for SRI’s
BioWarehouse System




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




Lane Medical Library &
Know ledge 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 &
    Know ledge 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 &
     Know ledge 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 &
   Know ledge Management Center                             21
   http://lane.stanford.edu
          Realistic
          Querying With
          the MS Access
          Query Builder

Lane Medical Library &
Know ledge 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 &
   Know ledge 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 &
  Know ledge Management Center                          24
  http://lane.stanford.edu
           Uploading
           Data in MS
           Access

Lane Medical Library &
Know ledge 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 &
    Know ledge 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 &
    Know ledge 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 &
    Know ledge Management Center                  28
    http://lane.stanford.edu
                          Extras


Lane Medical Library &
Know ledge 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 &
    Know ledge 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 &
    Know ledge 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 &
    Know ledge Management Center                              32
    http://lane.stanford.edu
                      Yannick Pouliot, PhD
                      lanebioresearch@stanford.edu



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


Lane Medical Library &
Know ledge 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 &
     Know ledge 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 &
    Know ledge 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 &
    Know ledge 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 &
    Know ledge 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 &
    Know ledge 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 &
    Know ledge 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 &
    Know ledge Management Center                 41
    http://lane.stanford.edu
Lane Medical Library &
Know ledge 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 &
    Know ledge 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 &
     Know ledge Management Center                                                                  44
     http://lane.stanford.edu