Microsoft Access

1
Microsoft Access

• The Basics

2
Your Expectations/Experience?
3
Class Objectives

• To learn when to use MS Access
• To learn how to use MS Access
• Toolbars
• Views
• Data entry and editing
• To create usable databases of your own
• Using templates and wizards
• Using basic queries and reports

4
Definitions

• MS Access - software used for creating databases
• Quickly
• Accurately
• Using wizards and templates developed to maintain
  the integrity of your data
• Data are just information
• Database
• Collection of data
• Related to a particular topic or project

5
Data Integrity

• Data must be accurate.
• Data are RELATED to other data in your database
  (e.g., library patron is related to the book(s)
  that s/he has checked out).
• Maintaining the INTEGRITY of the relationship
  between different pieces of data is very
  important.
• Example of compromised integrity sending an
  overdue notice to the wrong library patron

6
MS Access vs. MS Excel

• MS Excel
• spreadsheet
• flat database
• all information has a one-to-one relationship
• MS Access
• like multiple spreadsheets that are connected to
  one another
• one-to-many relationships
• many-to-many relationships

7
Examples

• Printed Phone Directory (White Pages)
• Flat database One-to-one relationships
• Library Catalog
• Relational database Many-to-many relationships
• Library patrons check out many books.
• Books are checked out by many patrons.
• University Class Schedule
• Students have many professors.
• Professors have many students.
• Classes can be held in many classrooms.

8
MS Access vs. MS Excel

• The choice is simple
• IF you have only one-to-one relationships, you
  need to use MS Excel.
• IF you have one-to-many or many-to-many
  relationships, you need to use MS Access.

9
About your assignment

• Mostly about Contact management
• No creativity?
• You could create database to manage
• a store (products, customers, suppliers)
• Student course (Student, courses/subjects,
  teachers, semester)
• Agricultural database (plants, seeding, harvest,
  yielding, treatment,..)
• etc.

10
Questions
11
Now for the Hands-On Practice!

• MS Access Tutorial
• Part 2 Advanced Access

12

• Turn on your PC
• Start Excel
• Use your Assignment file
• (borrow from your friend if you dont have it
  now)
• Import the data from MS Access to Excel
• Save it

13
Import/Export data between Excel and MS Access

• Database management systems allow user to
  exchange data between them
• Get data to Excel from MS Access
• Data gt Import External data gt Import Data gt
  Select data source
• File gt Open gt File type Access Databases
• Now Import Contact and Call data from your
  Access Assignment to Excel, save it as
  Contact.xls and Calls.xls for later use.

14
Import/Export data between Excel and MS Access

• Database management systems allow user to
  exchange data between them
• Import data from Excel to Access
• File gt Get External Data gt Import (later)

15
Practice

• Create Table in Design View for the
  contact_management with the fields as provided.
• Save it for later usage

16
Practice agricultural example

• How do you grow over 50 different crops for 500
  shareholders who are coming to your farm to pick
  up 13lbs of vegetables for 24 weeks?

• Keeping track of seed inventory, planting dates,
  greenhouse schedules, field records, and harvest
  records can be difficult on any vegetable farm.
• Using database management (Excel, Access)

17
Practice

• Create Table in Design View for the crop planning
  database as following
• Crop plan cropID, fieldID, assetID, acres,
  yield, price
• Field plan fieldID, seed needed, seeding date,
  planting date, of plants, rows per bed, beds
  needed, harvest time.
• Asset plan assetID, land, labor, machinery,
  building lease.
• Crops could be tomato, onion, carrot, cabbage,
  lettuce, beet, pea
• Feel free to add fields, enter data, etc.

18
(No Transcript)
19
Practice Crop database

• Design Tables 3 tables
• Create database enter data for all Tables
• Implement relationship
• Using Queries
• Make report