Microsoft Access Basics - PowerPoint by 2KE0Yt3a


									Microsoft Access Basics

  Academic Technology & User
       December 2002
Explore benefits of using a database
Understand basic Access components
– tables, queries, forms and reports
Know sources of data at WWU
Benefits of Using A Database
Data Integrity
– Consistent entries
– Data validation rules
Ease of data entry with forms
Minimize duplicate data entry
Easy Reporting
Designing a database takes time and
technical knowledge
          Access Components
Table       The primary data storage unit in Access. Tables are used
            to input and display information. Fields of information are
            arranged in columns. There is one row for each record.
Query       Queries gather specific data from one or more tables. The
            data can be displayed in a table, form, or report.
Form        Forms are used to input and display information from
            tables or queries. They can be arranged for efficient work
            and can include special objects, such as drop-down lists
            and check boxes.
Report      Reports display information from a table or query. Data
            can be grouped and summarized, including calculated
            totals and subtotals.
Macros      Automate tasks you perform repeatedly with the need of
Modules     Stores Access Basic Programming Code for custom
            program enhancements.
      Creating a database
Name the file - Access automatically
saves data as it is entered, so a file name
must be established before working in the
Tables are the backbone of a database. All the
fields in a table should be closely related
The table design is the arrangement of fields - it
is saved with the save command
A table can have up to 256 fields (columns)
The table data is the information typed into the
fields - it is saved as each entry is completed
a primary key field with a unique value is useful
when establishing relationships between tables.
          Creating A Table
On the table tab,
– Click the New button
– Choose a new table
            Table Design
add and delete fields
edit field names
set field properties.
use F1 for help
               Table Fields
Each record (row) has the same fields (columns) with
the same properties
Up to 256 fields (columns) in a table.
Fields contain the smallest amount of information that
you will need: for example, FirstName and LastName
fields instead of a single Name field.
A field name can contain up 64 characters. It is best to
avoid special characters and spaces.
A primary key field is used to help identify records. It
must be a unique value for each record and is useful
when establishing relationships between tables.
The toolbar provides shortcuts for some of the most
common the design tasks.
                Access Data Types
  Data Type                                   Description
     Text        Data entries that will not be calculated-including data containing
                 numbers such as zip codes, phone numbers or order numbers. A
                 maximum of 255 characters.
    Memo         Use for comments or notes. Can be up to 32,000 characters (about 8
                 single-spaced pages).
   Number        Numerical data that will (or can) be calculated
  Date/Time      Date and time information
   Currency      Monetary values
 AutoNumber      Automatically counts entries, incrementing as you enter data. Each
                 entry will be unique.
    Yes/No       Use for Yes/No, True/False and On/Off options
 OLE Object      A linked object from an external source
  Hyperlink      Creates a “clickable” hyperlink
LookUp Wizard Lets you choose a value from another table or from a list of values
     Naming Conventions
Professional database developers use
“naming conventions” to help identify
database objects

tblEmployeeData    or   tEmployeeData
qryBudget          or   qBudget
frmEmployeeData    or   fEmployeeData
rptBudget          or   rBudget
                 Importing Data
    Data can be imported from many
        Banner, DataW, BI/Query
        Excel spreadsheets

If data exists, import instead of re-enter
1. File, Get External Data
2. Choose Import or Link
3. Select the file
4. Follow the Import Wizard Prompts
      Switching between views

Use the View button to switch between the Design and Datasheet Views.
         Working With Tables
When entering data
–           The pencil shape in the left column
    indicates that the current changes have not been
–   New record       Ctrl +
–   Current Date Ctrl ;
–   Current Time Ctrl :
–   Sort Ascending or Descending
–   Find Data
–   Filter By Selection
let you easily repeat sort and filter commands
let you display and/or print only selected
connect one or more tables through similar fields
create new fields based on calculated values
add or remove specific information from tables
with action queries
         Create A Query
Click the Query tab
Click the New button
Choose Design View and select the table(s) or
queries upon which to base the query
Add fields to the “Query by Design” window
Run the query
                    Query Criteria
Criteria limits the records returned by the query

Criteria on the same line must all
be met – “AND” criteria

Criteria on different lines will
return records that meet any
criteria – “OR” criteria

                         In Queries,
                         OR = More
             Criteria Operators
Operator                             Condition
    <        less than
    >        greater than
    =        equal to
   <=        less than or equal to
   >=        greater than or equal to
   <>        not equal
* asterisk   all records - usually used with a leading character,
                  such as B*
    ?        a single character wildcard
Between      selects values between two values

  Like       must match a pattern - sometimes created by
                Access from wildcard input
  null       returns blank records
      Multiple Table Queries
Join tables on common fields
  One side of the join should be a primary
  key (displayed in bold)
Fields can be arranged for easy data entry
Input aids
– Check boxes
– List boxes
– Combo boxes
– Calculations
– Macros
         Creating A Form
With a table open
– Click the New Object button
– Choose AutoForm
From the Form tab
– Click New
– Choose Design View or AutoForm
– Select the table or query that contains the
  data for the form
– Click OK
  Navigating with Forms
Tab from field to field
Click the New Record button to add a

 Use navigation bar to move from record to
           Form Design
Click the View button to switch between
Form View and Design View
– Move Objects
– Resize Objects
– Format Objects
Reports are for previewing and printing
only – no data entry
Reports can group data and perform
Reports can be exported to Excel or Word
       Creating A Report
Click the Report tab
Click New
Choose one of the following
– Design View
– Report Wizard
– AutoReport
           Working with Reports
Report Section                              Description
Report Header    Prints once at the beginning of the report
Page Header      Prints at the top of each page (including the first page)

Group Header     Prints at the start of each group
Detail Section   Contains the main body of the report. Prints once for every
                    record included in the report.
Group Footer     Prints once at the bottom of each group. Sometimes contains
                     formulas for group totals.
 Page Footer     Prints at the bottom of each page.
Report Footer    Prints once at the end of the report. Often used to contain report
         Sources of Data
Data can be imported from
– Banner downloads
– DataW
– BI/Query
– Excel Spreadsheets
– Text files
Access Version Compatibility
Access 97 can only open Access 97
Access 2000 can open Access 97
databases and enter data, however they
must be converted to change the database
Access 2002 databases use Access 2000
format by default
       Database Support
HR Training and Development
– Access Classes
– Access workbooks available for checkout
ATUS Help Desk
ATUS Software Support
– David Hamiter
– Susan Brown
ADMCS-for Banner and official data

To top