slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
MS Access 2002: Basic PowerPoint Presentation
Download Presentation
MS Access 2002: Basic

Loading in 2 Seconds...

play fullscreen
1 / 60

MS Access 2002: Basic - PowerPoint PPT Presentation


  • 330 Views
  • Uploaded on

MS Access 2002: Basic Instructor: Vicki Weidler MS Access: Database Concepts Overview Become familiar with MS Access database terminology Learn how to start MS Access, open a database & navigate the software interface Understand database objects & their corresponding functions

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'MS Access 2002: Basic' - paul2


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

MS Access 2002: Basic

Instructor: Vicki Weidler

overview
Overview
  • Become familiar with MS Access database terminology
  • Learn how to start MS Access, open a database & navigate the software interface
  • Understand database objects & their corresponding functions
  • Use standard design principles to plan a database
  • Explore relationships between tables
  • Use Help options to find information on MS Access topics
  • Learn how to close a database & MS Access
databases
Databases
  • What is a database?
  • When do you use a database?
  • 3 main types of databases
    • Flat file
    • Relational
    • Object-oriented
microsoft access
Microsoft Access

A relational database management program that allows you to store, organize, and retrieve information in an effective manner.

planning a database
Planning a Database
  • What is the purpose of the database?
  • What do you need it to do?
  • Who will use it?
  • How many will use it?
  • When will they use it? (i.e. one at a time, simultaneously)
  • How will they use it? (i.e. desktop, server, web-interface)
  • Who will create it?
  • Who will maintain & modify it?
  • Who will do data entry?
  • Do you need to build in security measures?
  • Who will provide technical support for it?
  • What types of information need to go in it?
  • How will this be organized into tables?
  • How will these tables relate to one another?
  • What types of queries will you need to create?
  • Will you need to create any forms? How many? What kind?
  • Will you need to create any reports? How many? What kind?
  • Will you need to create any pages? How many? What kind?
common mistakes to avoid
Common Mistakes to Avoid
  • Don’t assume every problem can be solved with a database
  • Don’t leave too long between reviews
  • Don’t spend too much time on the prototype
  • Don’t develop the database until you fully understand what you need it to do
  • Don’t develop the database until it is clear where all responsibilities lie
  • Don’t aim for 100% functionality first time around; use 80-20% rule
  • Involve all users in the database planning, design & testing
  • Has anyone else solved the problem? Don’t reinvent the wheel
  • Plan on an iterative development process
  • Use consistent database standards and naming conventions
  • Use consistent, universal coding
  • Use consistent viewing standards
  • Keep sufficient documentation; build into the database when possible
  • Tech support, tech support, tech support………..
database development life cycle
Database Development Life-Cycle

Database

Design

Database

Production

Database

Prototype

Database

Testing

Database

Development

summary
Summary
  • Become familiar with MS Access database terminology
  • Learn how to start MS Access, open a database & navigate the software interface
  • Understand database objects & their corresponding functions
  • Use standard design principles to plan a database
  • Explore relationships between tables
  • Use Help options to find information on MS Access topics
  • Learn how to close a database & MS Access
independent practice activity
Independent Practice Activity
  • Question #2: 3 types (tables, queries, reports)
  • Question #3: 5 fields and 10 records
  • Question #5: Database, Table, Data Value
  • Question #6: Table, Query, Report
overview12
Overview
  • Explore the viewing options for MS
  • Access tables, forms, queries &
  • reports
  • Learn how to navigate in a table and
  • a form
  • Learn how to run a query
views in ms access
Views in MS Access
  • Design View - displays the structure of a table, query, form or report
  • Datasheet View – displays data in a tabular format containing rows & columns in tables, queries & forms; view & edit
  • PivotTable View – spreadsheet-like table used to analyze data dynamically in different ways in tables, queries & forms
  • PivotChart View – presents data in a chart form in tables, queries & forms
  • SQL View – allows you to create queries using SQL statements
  • Form View – allows you to view and edit data in a form
  • Layout Preview – allows you to check a report’s design; shows design elements & just enough records to verify the design is correct
  • Print Preview – allows you to view a report including moving around a single page, multiple pages, look at several pages at once & change magnification to view details
using queries to retrieve data
Using Queries to Retrieve Data

Define the following:

  • The conditions that you want the data to meet
  • The fields that you want to see in the query result
  • The tables from which you’ll extract the fields
  • The means of extracting data
summary15
Summary
  • Explore the viewing options for MS
  • Access tables, forms, queries &
  • reports
  • Learn how to navigate in a table and
  • a form
  • Learn how to run a query
independent practice activity16
Independent Practice Activity
  • Datasheet View-shows data is a tabular format
  • Design View-gives control over table structure
  • Navigation Button-buttons at bottom of datasheet view used to move through records
  • Record Selector-small box to left of each record in a table that you can click to select a record
overview18
Overview
  • Learn how to create & save a database
  • Use the Table Wizard to create a table & set a primary key
  • Use Design view to create tables & enter records
naming rules
Naming Rules

A database, object or field name:

  • Can have any combination of letters, numbers, special characters, & embedded spaces
  • Cannot contain more than 64 characters & cannot start with a space
  • Cannot include a period (.), exclamation mark (!), an accent grace (‘), or brackets ([ ])

CAUTION: Good practice to use underscores (_) instead of embedded spaces because it is easier to refer to an object name & avoid potential issues

practice
Practice
  • Inventory_ _ Control
  • Yes, although one underscore is the accepted standard
  • .Access.Database 1
  • No, because the name contains periods & spaces should be avoided
  • finanCIAL$Transaction
  • Yes, although try to use names that are as simple & descriptive as possible
  • Contact [Client] Address
  • No, because the name contains brackets & spaces should be avoided
  • SalesPerson3
  • Yes, it is fine to run words together & use different cases
summary21
Summary
  • Learn how to create & save a database
  • Use the Table Wizard to create a table & set a primary key
  • Use Design view to create tables & enter records
overview24
Overview
  • Learn how to modify a table’s design by editing fields & setting field properties
  • Learn how to add & delete records
  • Use the Find feature to find & replace records
  • Use the Spell Checker to correct mistakes in a table
  • Learn how to sort & filter records
effective field names
Effective Field Names
  • Meaningful
  • Descriptive
  • Self-Explanatory
  • Purposeful
  • Understandable
  • Readable
  • Consistent
text formatting characters
Text Formatting Characters

@ at least one letter or space must be entered in the field

< the letters entered in the field will be converted to lowercase letters

> the letters entered in the field will be converted to uppercase letters

& letters cannot be entered in the field

sorting filtering
Sorting & Filtering

Sorting

Organize field data values in a particular sequence (i.e. ascending, descending)

Filtering

Temporarily isolate a subset of records to analyze

sorting multiple fields
Sorting Multiple Fields
  • Fields must be adjacent to each other in Datasheet view
  • Sorts from left to right
summary29
Summary
  • Learn how to modify a table’s design by editing fields & setting field properties
  • Learn how to add & delete records
  • Use the Find feature to find & replace records
  • Use the Spell Checker to correct mistakes in a table
  • Learn how to sort & filter records
slide31

MS Access: Querying Tables

Instructor: Vicki Weidler

Assistant: Joaquin Obieta

overview32
Overview
  • Learn how to create, run, print, and save queries
  • Use queries to sort data and filter query results
  • Learn how to modify query results
  • Understand how to modify queries by adding/ removing fields and by using comparison

operators

  • Learn how to use AND and OR conditions
  • Learn how to find records with empty fields
  • Perform calculations in queries by using expressions and aggregate functions
comparison operators
Comparison Operators

> Greater than

< Less than

= Equal to

<= Less than or equal to

>= Greater than or equal to

<> Not equal to

or conditions
OR Conditions

Used to specify two conditions in the criteria

Example:

Unit_price is greater than 2 OR Qty_Available is equal to 700

and conditions
AND Conditions

Used to show only the records that satisfy all the specified conditions

Example:

Unit_price is greater than 1.4 AND less than 1.9

wildcard operators
Wildcard Operators

Used to locate text values when you can’t remember the exact text or word; placeholder

? Used to substitute for a single character

* Used to substitute for any number of characters

empty fields
Empty Fields
  • Unknown (empty) values in fields are referred to as null values
  • Null values cannot be used in primary key fields or in calculations
  • Can display or remove null values to avoid/prevent problems
calculations in queries
Calculations in Queries
  • When calculations are built into queries (and not stored in the source tables) it ensures the most up-to-date information
  • Calculations are performed each time the query is run
  • Perform calculations on single records or on a group of records
  • Examples: Totals, Averages, Counts
summary39
Summary
  • Learn how to create, run, print, and save queries
  • Use queries to sort data and filter query results
  • Learn how to modify query results
  • Understand how to modify queries by adding/ removing fields and by using comparison

operators

  • Learn how to use AND and OR conditions
  • Learn how to find records with empty fields
  • Perform calculations in queries by using expressions and aggregate functions
overview42
Overview
  • Use the AutoForm feature to create forms
  • Use the Form Wizard to create forms
  • Understand how to modify forms in Design view
  • Use forms to find, sort, and filter records
summary43
Summary
  • Use the AutoForm feature to create forms
  • Use the Form Wizard to create forms
  • Understand how to modify forms in Design view
  • Use forms to find, sort, and filter records
overview46
Overview
  • Use the Report Wizard to create reports
  • Learn how to group records in a report
  • Learn how to summarize information in a report
  • Understand how to change the report layout
  • Learn how to print a report
summary47
Summary
  • Use the Report Wizard to create reports
  • Learn how to group records in a report
  • Learn how to summarize information in a report
  • Understand how to change the report layout
  • Learn how to print a report
overview50
Overview
  • Learn how to import MS Access objects from a different database
  • Learn how to export objects to a different database
  • Understand how to export and import XML documents
  • Understand how to link objects from one database to another & update those links
overview51
Overview
  • Learn how to import an MS Excel spreadsheet into MS Access
  • Learn how to import a text file into MS Access
  • Learn how to link an MS Excel spreadsheet to MS Access
  • Learn how to link a text file to MS Access
definitions
Definitions
  • Importing - data is copied from an external source & pasted into the currently active Access database
  • Exporting - data is copied from the currently active Access database & pasted into an external source
  • Linking - a table that’s not stored in the currently active Access database, but which you can manipulate as though it were a native table
exporting access objects
Exporting Access Objects
  • Definition - design or structure of the object
  • Data - information stored within the object
exporting importing xml documents
Exporting & Importing XML Documents

XML (Extensible Markup Language)

is a format that enables you to deliver structured data between different applications that run on different operating systems in a standardized & consistent manner. Most commonly used on the WWW to describe data, but also used to transfer data between applications more easily.

exporting importing xml documents55
Exporting & Importing XML Documents
  • Schema – exports only the structure of the object; creates a document with .xsd extension
  • Data – exports both structure and data; creates a document with .xml extension
  • Presentation – defines the presentation of an XML document; creates a document with .xsl extension
linked table manager
Linked Table Manager

The Linked Table Manager tests for the existence of linked .mdb or other types of files and, if the links aren’t valid, lets you change the path to the linked files. The database must be open to use this utility.

summary57
Summary
  • Learn how to import MS Access objects from a different database
  • Learn how to export objects to a different database
  • Understand how to export and import XML documents
  • Understand how to link objects from one database to another and update those links
summary58
Summary
  • Learn how to import an MS Excel spreadsheet into MS Access
  • Learn how to import a text file into MS Access
  • Learn how to link an MS Excel spreadsheet to MS Access
  • Learn how to link a text file to MS Access
slide60

Conclusion

  • Resources
  • Questions & Answers
  • Evaluations
  • Thank You!!!