Ms Access Filtering Data from Within a Form - PowerPoint by qjv85898

VIEWS: 8 PAGES: 60

More Info
									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
• 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

• What is a database?
• When do you use a database?
• 3 main types of databases
 Flat file
 Relational
 Object-oriented
     Microsoft Access

A relational database
management program that
allows you to store, organize,
and retrieve information in an
effective manner.
            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

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



  Database                                   Database
 Production                                  Prototype




         Database               Database
          Testing              Development
                  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

•   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
MS Access: Access Basics
             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
•   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


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
             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
               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
MS Access: Creating
    Databases
            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

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
•   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
            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
Independent Practice
      Activity
MS Access: Working with
   Fields & Records
               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

• Meaningful
• Descriptive
• Self-Explanatory
• Purposeful
• Understandable
• Readable
• Consistent
    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
 Organize field data values in a
 particular sequence (i.e. ascending,
 descending)

Filtering
 Temporarily isolate a subset of
 records to analyze
 Sorting Multiple Fields

• Fields must be adjacent
  to each other in
  Datasheet view

• Sorts from left to right
               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
Independent Practice
      Activity
MS Access: Querying
      Tables
 Instructor: Vicki Weidler
 Assistant: Joaquin Obieta
                 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

 >   Greater than
 <   Less than
 =   Equal to
<=   Less than or equal to
>=   Greater than or equal to
<>   Not equal to
      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

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

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

•   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

• 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
                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
Independent Practice
      Activity
MS Access: Creating &
    Using Forms
                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
                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
Independent Practice
      Activity
MS Access: Creating &
    Using Reports
               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
               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
Independent Practice
      Activity
  MS Access: Importing,
Exporting, & Linking Objects
               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
               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
• 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


• Definition - design or structure of
               the object

• Data - information stored within
  the        object
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
          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

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.
               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
               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
Independent Practice
      Activity
     Conclusion


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

								
To top