Microsoft Access 2003 Property Management Database Template - PowerPoint

Document Sample
Microsoft Access 2003 Property Management Database Template - PowerPoint Powered By Docstoc
					Introduction to Microsoft
      Access 2003

    Presenter: Jolanta Soltis
    MCSE, MCT, A+
Attendee Introductions
Your name
Current position
Background in Microsoft Office
     Course Objectives

In this course you will learn how to:
   • Understand database concepts and terminology in
     Access 2003
   • Design and create tables
   • Enter and manipulate data in tables
   • Use Access queries to select and analyze
     information in a table
   • Create data forms for viewing and inputting data
   • Create reports that summarize and group data
   • Perform database maintenance procedures
Access 2003 Structure
What is Microsoft Access?
 Powerful Relational Database Management System
 (RDBMS) design to run in Microsoft Windows
   –Data can be organized as a set of related tables
 Integration with other Office applications allows
 seamless exchange of data with centralized database
   What is an Access
Collection of data objects stored with filename
extension .mdb (Microsoft database)
Main Access data objects
Access basic Data Objects

     Macros         s
How to open Access?

         Access opens with the
         dialog box shown here
       Database Window
Database Window
    – Main database design/management window
    – Displayed when creating or opening an Access
    – You can use the Objects toolbar to access the
      different objects that make up a database
What is table
  • Basic container for data, arranged as a grid of
    rows and columns
  • Each row contains a single record
  • Each column represents a field within the record
Access tables
  • Fundamental data objects in Access
     – Forms, queries and reports are all based on tables
  • Table Wizard provides automated table creation
  • Tables can also be created manually for more
    precise specification

What is form
  • Electronic version of paper form
  • Used to simplify entry of data into an Access database
What is a Query?
   • A question asked of the database
   • Used to extract specific information from database
   • Used to extract specific information from database
       – Example:What is the three top-selling products in our
         company‘s product line?
   • Queries are composed of structured query
     language(SQL) statements
       – Example:
       SELECT Products.[Product #], Products.[Product Name],
       FROM Products
       WHERE (((Products.Price)<4.75));

Access allows queries to be created graphically
   • Hides complexity of SQL language
What is report?
   – Formatted template used to print reports of
     database or query results
   – Allows user to specify fields, grouping levels,
     arrangement of printed data
What is access macro?
  – User-defined sequence of actions to be performed by
    Access 2000
  – Macros will not be covered
What is a module?
  – User-created sections of code which provide
    sophisticated automation of Access functions
  – Written in Visual Basic for Application(VBA)
Creating New Database

   Address Book
     Creating Database
Case study scenario:
– In this section, we will create a database
  (including tables, forms, reports, and queries).
– The database we will create will have
Creating a new database
– Start Access
– Select ―Create new database using blank
– Assign a name for the new database
   • AddressBook.MDB
      Create New Table
Creating a new table
– In the database window, select the Tables tab,
  then click on New
Table creation options
– Design View
   • Manual table creation by user(maximum control over
     table specifications)
– Table Wizard
   • Automated table creation facility
– Import table
   • Import data from external tables
– Link Table
   • Link to data in external tables
Creating a new table in Design
  For each field in new database,specify the
  following items
   – Field Name
      • Descriptive name of field to be used in table
          – 64-character maximum
          – Prohibited characters:period(.), accent grave(‗), square
            brackets([]), exclamation point(!)
   – Data Type
      • Drop-down list displays available data types
          – Number, Date/Time, Currency,Auto number, Yes/No, OLE
            Object, Hyperlink, Lookup Wizard
   – Description
      • Comment describing details of field. Appears on the
        status bar in Datasheet view when you click a row in the
        field's column
                            Access Data Types.

Data Type    Usage                                                                      Size
Text         Alphanumeric data                                                          Up to 255 characters

Memo         Alphanumeric data—sentences and paragraphs                                 Up to 64,000 characters

Number       Numeric data                                                               1, 2, 4, or 8 bytes (16 bytes
                                                                                              for ReplicationID and
Date/Time    Dates and times                                                            8 bytes

Currency     Monetary data, stored with 4 decimal places of precision                   8 bytes

AutoNumber   Unique value generated by Access for each new record                       4 bytes (16 bytes for
Yes/No       Boolean (true/false) data                                                  <1 bit/FONT>

OLE Object   Pictures, graphs, or other ActiveX objects from another Windows-based      Up to about 1 gigabyte
Hyperlink    A link "address" to a document or file on the World Wide Web, on an        Up to 2048 characters
                   intranet, on a local area network (LAN), or on your local computer
             Fields properties
Field Property   Purpose/Note or Example
Field size       Specifies amount of storage for data in field
                 Note: Test maximum field size = 255 characters
Format           Predefined formats for field data
                 Ex: Short date =11/25/97
Input Mask       Formatting mask for user data input
                 Ex:Phone number input mask = (XXX)-XXX-XXX
Default Value    Specifies default value in field
                 Note: Speeds up data entry.
Required         Specifies that a value must be entered in field

Indexed          Tells Access to create an index for this data field
                 Note: Speeds up data searches based on this field
     Setting a Primary Key
What is a primary key?
– Main index for table
– Must be unique for each record in table
    • Example: Product number, Employee number, etc.
– If no such field exist, create a new field with the data
  type ―Autonumber‖ and specify it as the primary key
    • Access will automatically create unique numbers for this
Assigning a field as the primary key
– Select field
– Click on Primary Key button on toolbar (or use ―Edit
  /Primary Key‖)
    • A key symbol will appear next to selected field
      Saving the table
– Select ―File/Save‖ or click on the close
  button for the Table Design View
– Access will prompt you to enter a name
  for the new table
  • Table name can be up to 64 characters
    Entering data into a Table
• Entering data using Datasheet view
    – From the Access database window, select the table of interest
    – Click on Open
         » The table will be displayed in datasheet view
    – Type new data directly into fields on datasheet
• Adding/Deleting records
    – Add new records in empty record at bottom of datasheet
    – Delete records by selecting record, then use Edit/Delete Record
• Sorting records
    – Click on data field to be sorted
    – Click on sort buttons on toolbar (Ascending and Descending)
• Disadvantages of datasheet data entry
    – Clumsy to enter data into tables with large number of fields
    – Forms are usually a better choice
      Working with tables
Adding records – Insert / New Record
Modifying the table design – Format / Data Sheet
Finding and editing records – Edit / Find
Deleting, adding, and copying records and values – Edit /
Cut, Copy, Paste
Filtering and sorting – Records / Filter
              Creating Forms
   Form advantages
    – Simplifies data entry process
    – Able to display many entry fields on one
   Most commonly used form creation
New form options           Description
Design View                Manual form creation (maximum
Form Wizard                Automated form creation
Auto form                  Automatically creates form
                           without user intervention
Creating forms with Form
– Using Form Wizard
  • From the Access database window, click
    on the Forms tab, then click on New
     – The New Form dialog box will be displayed
  • Select the name of the table or query to be
    used to create the new form
– Form creation steps
  • Field selection
  • Form layout
  • Style
                   Form layout

Field selection

         Using the New Form
     – Form navigation
         • The form wizard displays the new form at the end of
           the form creation process
         • The contents of the first record are displayed
     – Record navigation controls(lover left corner of

Record navigation control   Action
            |<              Move to first record
            <               Move to previous record
            >               Move to next record
            >|              Move to last record
            >*              Move to new blank record
      Creating queries
– What is query?
  • Structured request for specific ingomation
    from database
  • Created in Access using New Query
     – Query created with graphical tools(Query by
     – Underlying code produced in SQL
– Creating a query in design view
  • From Access database window, select
    Queries tab, then click on New
  • Select Design View
– Specify fields to show
    • Select the fields to be displayed by clicking on the Show
– Specify criteria
    • Enter the criteria expression for the desired field in the
      query grid
    • In this example, we want to select all products whose
      price is less than$4.75
        – We will set the criteria for the Price column to ―<4.75‖
    • if multiple fields have criteria set, they must all be
      satisfied to select record (logical AND)
– Viewing SQL
    • Use View/SQL to see a listing of the SQL statements
      produced by the design
      Creating Reports
Using the Report Wizard
– From the Access database window,
  click on Reports tab,then click
Report creation steps
– Field selection
– Sort order
– Layout
– Style
– Title/preview
If you have any questions, please feel free to
        contact Academic Computing
Jolanta Soltis, IT Consultant (973) 596-2925

Description: Microsoft Access 2003 Property Management Database Template document sample