Transcript A Painless Introduction to MS Access Databasing

A Painless Introduction
to MS Access Databasing
Yannick Pouliot, PhD
Bioresearch Informationist
[email protected]
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
http://lane.stanford.edu
2
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
http://lane.stanford.edu
3
Resources – MS Access
Available as eBook
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
Available as eBook
4
Resources – Excel/MS Query
In Lane catalog
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
5
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/enus/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
http://lane.stanford.edu
6
Why Bother
with MS
Access?
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
7
Why MS Access?

More: http://office.microsoft.com/enus/access/HA010429181033.aspx
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
8
Definitions,
Definitions…
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
9
Definitions


Database: Collection of tables 
Table
 Collection of records that share a common fundamental
characteristic


Schema


E.g., patients and locations can each be stored in their own table
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
http://lane.stanford.edu
10
Table and Field Definitions
How It Looks In Access…
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
11
Main Relational Database Engines
 Oracle

MS Access


MS SQL Server
Filemaker

MS SQL Server
 MySQL


Postgress
Sybase
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
12
Understanding
Relational
Databases
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
13
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
http://lane.stanford.edu
14
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
http://lane.stanford.edu
15
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
http://lane.stanford.edu
16
Example: The Schema for SRI’s
BioWarehouse System
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
17
Querying MS Access
or any relational database…
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
18
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
http://lane.stanford.edu
19
Introducing The SQL Select Statement


Good news: This is the
only SQL statement
you need to understand
for querying
Exercise: run
SuperSimpleQuery1 in
Simple1 database
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
SELECT LastName, FirstName
FROM Patients
20
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
http://lane.stanford.edu
21
Realistic
Querying With
the MS Access
Query Builder
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
22
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/enus/access/HP010322651033.aspx?pid=CH010410171033
Includes examples and descriptions of each
SELECT operator (e.g., “group by”)
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
23
Meaningful Queries in Acces
Exercises - Database: Simple2.mdb
1.
2.
Returning ordered records
Counting records

LifetimeNumberFAQs1


LifetimeNumberFAQs2

3.
4.
5.
Exercise: how many FAQs per day?
Demo: creating Pivot Tables
Generating averages

Querying from a query!
Change order of columns
Change sorting of rows
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
24
Uploading
Data in MS
Access
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
25
Importing Data Into Acces From
Access
Important
 Column headers in Excel file must match those of
columns in Access target table


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


No spaces, weird characters
File: LoadingData.xls
Demo: http://office.microsoft.com/enus/access/HP010950951033.aspx
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
26
Creating an Access DB from Excel


One table per Excel worksheet
Demo: http://office.microsoft.com/enus/access/HA012013211033.aspx
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
27
Cool Things You Can Do In Access

Store entire files in records

Excel, Word, sounds, pictures anything
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
28
Extras
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
29
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
/enus/access/HA0111995010
33.aspx
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
30
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”



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


Search hidden files and folders
move the shortcut to a desired location
Also needed: creating a data source name
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
31
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
http://lane.stanford.edu
32
Yannick Pouliot, PhD
[email protected]
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
33
The NittyGritty Details
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
34
How to Query Using MS Query
Steps
1. Make sure you have the requisite driver (next
slide)
2.
3.
4.
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 Avalanche for lots
more details.
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
35
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


Each database engine (Oracle, MySQL, etc)
requires its own driver



“data connectivity” tool
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
http://lane.stanford.edu
36
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
http://lane.stanford.edu
37
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
http://lane.stanford.edu
38
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
http://lane.stanford.edu
39
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
http://lane.stanford.edu
40
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
http://lane.stanford.edu
41
Lane Medical Library &
Knowledge Management Center
http://lane.stanford.edu
42
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
http://lane.stanford.edu
43
MySQL Query Browser
MS Query
MySQL only
All (e.g., Oracle)
No
Yes
Dr
iv
er
?
an
ag
er
?
BC
M
Us
e
of
High
OK
O
D
Tool
Ea
se
Da
ta
ba
se
s
SQL Querying…With What?
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
http://lane.stanford.edu
44