Access Скачать
презентацию
<<  Создание таблиц базы данных Microsoft Access  >>
A Painless Introduction to MS Access Databasing
A Painless Introduction to MS Access Databasing
Preliminaries
Preliminaries
Contents
Contents
Resources – MS Access
Resources – MS Access
Resources – Excel/MS Query
Resources – Excel/MS Query
Resources – MS Access Free Online Training Resources
Resources – MS Access Free Online Training Resources
Why Bother with MS Access
Why Bother with MS Access
Why MS Access
Why MS Access
Definitions, Definitions…
Definitions, Definitions…
Definitions
Definitions
Table and Field Definitions How It Looks In Access…
Table and Field Definitions How It Looks In Access…
Oracle MS Access MS SQL Server Filemaker MS SQL Server MySQL Postgress
Oracle MS Access MS SQL Server Filemaker MS SQL Server MySQL Postgress
Understanding Relational Databases
Understanding Relational Databases
Understanding the Relational Principle: A Simple Database
Understanding the Relational Principle: A Simple Database
The Relational Principle in Action
The Relational Principle in Action
The Database Schema: Your Roadmap For Querying
The Database Schema: Your Roadmap For Querying
Example: The Schema for SRI’s BioWarehouse System
Example: The Schema for SRI’s BioWarehouse System
Querying MS Access or any relational database…
Querying MS Access or any relational database…
How Relational Databases Are Queried
How Relational Databases Are Queried
Introducing The SQL Select Statement
Introducing The SQL Select Statement
Basic Syntax of Select Statement
Basic Syntax of Select Statement
Realistic Querying With the MS Access Query Builder
Realistic Querying With the MS Access Query Builder
A More Complete Description of Select Statement
A More Complete Description of Select Statement
Meaningful Queries in Acces
Meaningful Queries in Acces
Uploading Data in MS Access
Uploading Data in MS Access
Importing Data Into Acces From Access
Importing Data Into Acces From Access
Creating an Access DB from Excel
Creating an Access DB from Excel
Cool Things You Can Do In Access
Cool Things You Can Do In Access
Extras
Extras
Creating Forms Using MS InfoPath
Creating Forms Using MS InfoPath
Querying With MS Excel
Querying With MS Excel
To Conclude…
To Conclude…
Yannick Pouliot, PhD lanebioresearch@stanford
Yannick Pouliot, PhD lanebioresearch@stanford
The Nitty- Gritty Details
The Nitty- Gritty Details
How to Query Using MS Query
How to Query Using MS Query
Step1: Getting Drivers Essential for SQL Querying
Step1: Getting Drivers Essential for SQL Querying
MySQL Driver: Needed to Query MySQL Databases
MySQL Driver: Needed to Query MySQL Databases
Oracle Driver: Needed to Query Oracle Databases
Oracle Driver: Needed to Query Oracle Databases
Step 2: Creating a Data Source Name
Step 2: Creating a Data Source Name
Words of Caution
Words of Caution
Resources – SQL The Language to Query Relational Databases
Resources – SQL The Language to Query Relational Databases
42
42
The Relational Principle in Action
The Relational Principle in Action
SQL Querying…With What
SQL Querying…With What
Слайды из презентации «MS Access» к уроку информатики на тему «Access»

Автор: . Чтобы увеличить слайд, нажмите на его эскиз. Чтобы использовать презентацию на уроке, скачайте файл «MS Access.ppt» бесплатно в zip-архиве размером 2494 КБ.

Скачать презентацию

MS Access

содержание презентации «MS Access.ppt»
СлайдТекст
1 A Painless Introduction to MS Access Databasing

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

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

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

Resources – MS Access

Available as eBook

Available as eBook

4

5 Resources – Excel/MS Query

Resources – Excel/MS Query

In Lane catalog

5

6 Resources – MS Access Free Online Training Resources

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

Why Bother with MS Access

7

8 Why MS Access

Why MS Access

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

8

9 Definitions, Definitions…

Definitions, Definitions…

9

10 Definitions

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 Definitions How It Looks In Access…

Table and Field Definitions How It Looks In Access…

11

12 Oracle MS Access MS SQL Server Filemaker MS SQL Server MySQL Postgress

Oracle MS Access MS SQL Server Filemaker MS SQL Server MySQL Postgress

Sybase.

Main Relational Database Engines

12

13 Understanding Relational Databases

Understanding Relational Databases

13

14 Understanding the Relational Principle: A Simple Database

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

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 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

Example: The Schema for SRI’s BioWarehouse System

17

18 Querying MS Access or any relational database…

Querying MS Access or any relational database…

18

19 How Relational Databases Are Queried

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

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

SELECT LastName, FirstName FROM Patients

20

21 Basic Syntax of Select Statement

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

Realistic Querying With the MS Access Query Builder

22

23 A More Complete Description of Select Statement

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”)

23

24 Meaningful Queries in Acces

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

Uploading Data in MS Access

25

26 Importing Data Into Acces From Access

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

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

Cool Things You Can Do In Access

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

28

29 Extras

Extras

29

30 Creating Forms Using MS InfoPath

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

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…

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

Yannick Pouliot, PhD lanebioresearch@stanford

edu.

33

34 The Nitty- Gritty Details

The Nitty- Gritty Details

34

35 How to Query Using MS Query

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 Avalanche for lots more details.

35

36 Step1: Getting Drivers Essential for SQL Querying

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

36

37 MySQL Driver: Needed to Query MySQL Databases

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

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

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

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 – SQL The Language to Query Relational Databases

Resources – SQL The Language to Query Relational Databases

Beginning SQL, Wilton P & Colby JW: E http://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

42

43 The Relational Principle in Action

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

SQL Querying…With What

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

44

«MS Access»
http://900igr.net/prezentatsii/informatika/MS-Access/MS-Access.html
cсылка на страницу
Урок

Информатика

126 тем
Слайды
Презентация: MS Access.ppt | Тема: Access | Урок: Информатика | Вид: Слайды