Access 2002

Document Sample
Access 2002 Powered By Docstoc
					   Access 2002


What you will do:         When you need a flexible system for organising and analysing a large amount of
                          data, it’s a good idea to build a database. Microsoft® Access 2002 adds valuable
                          capabilities to the proven Access database technology. It offers new ways to view
     Explore the
                          data, along with compatibility and integration with other programs.
      features of
      Access 2002
                          This chapter will show you how to use Access 2002 to:
     Create a
      database                   Build a database. Plan and create a database; create a table; add
     Create a table              fields and data to the table.
     Add data to a
      table                      Manage data. Sort records; use filters to sort data; create and run a
                                  query.
     Sort records
     Use filters and            Analyse data. Create and use forms to access and analyse information
      queries                     in a database; create Microsoft PivotTable® and Microsoft PivotChart®
     Create a form               dynamic views.
     Add data through
      the Web                    Report findings. Create a report based on an aspect of the data
                                  collected.
     Create PivotTable
      and PivotChart      For example, if you’re beginning an assignment on the history of shipwrecks in
      views               the Australia, and you need to obtain data from various sources and use a filter
     Create a report     to sort data, it makes sense to store your records in a database. You can use
                          Access 2002 not only to store all kinds of information, but also to analyse the
                          data and create professional reports.




Access 2002                                                                   5.1 In and Out of the Classroom with Office XP
                             New for Access 2002
                             Access 2002 has several new features that make it even easier to view and
                             share information from a database. These features include:
  An Access project
  does not contain any              PivotTable and PivotChart views. If you’re already familiar with
  data; it’s a file that             PivotTable and PivotChart reports in Microsoft Excel, you’ll be happy to
  contains code-based
  database objects                   know that they are now available in Access 2002. With PivotTable and
  stored in a SQL Server             PivotChart views, you can dynamically change the types of data that you
  database.                          can compare and analyse in a chart or table format.

                                    Compatibility with Access 2000. You can work with and modify
                                     Access 2000 files in Access 2002 without converting the file format.

                                    Conversion error logging. If you encounter problems when converting
                                     from Access 95 and other versions prior to Access 2000, Access 2002
                                     will create a table that lists information about each error, making it much
                                     easier to identify and solve problems.

                                    Multiple undo and redo. You can now undo or redo multiple actions in
  New keyboard                       Design view in all objects (such as tables, queries, and reports) in your
  commands in
  Access 2002 include:
                                     Access databases.
     F8, in form or
      report Design                 XML support. You can easily import and export XML data between
      view, displays the             Access databases and other databases, such as Microsoft SQL Server™.
      field list.
     ENTER, after
      selecting a field in          Extended property support with Microsoft SQL Server
      the field list in              2000. Integration of Access 2002 and SQL Server 2000 includes
      form or report                 support for extended SQL database properties from within an Access
      Design view,
      automatically
                                     project. Using extended properties makes it easy to save column widths,
      adds the field to              row heights, and fonts from one Access project to another. You can also
      the form or report             use extended properties to migrate your education data applications
      design surface.                from Access databases to Access projects connected to SQL Server.
     CTRL+TAB moves
      the focus from a
      form or report                Improved accessibility features. There are new keyboard commands.
      section to a                   In addition, two powers (1,000 percent and 500 percent) have been
      subsection.                    added to the Zoom option in Print Preview.




                             Touring Access 2002
                             Before you begin to use Access 2002 in the classroom, you should be familiar
                             with the basic terminology of the program. A database consists of a collection of
                             tables, queries, forms, reports, and pages that you use to manage and present
                             data. These components are also called database objects. When you build a
                             database, you create as many of these objects as you need, and Access 2002
                             stores them in one database file. Each object you create in the database is
                             dependent on other objects. That is, a query, form, or report draws information
                             from tables, so changing data in any one of these objects changes the data in all
                             of these objects.




2 In and Out of the Classroom with Office XP                                                                       Access 2002
              There are seven main elements of a database in Access 2002:

                     Tables. Use tables to store data.

                     Forms. Use forms to enter or edit the data in your tables. Forms let you
                      view one record at a time.

                     Queries. Filter data so that you retrieve selected records or fields from
                      the database.

                     Data access pages. Create HTML pages from a database quickly and
                      efficiently.

                     Reports. Use reports to deliver a professional presentation or written
                      report.

                     Macros. Automate tasks that you perform on a regular basis in a
                      database.

                     Modules. Automate a group of related procedures in Access 2002.

              You create and open database objects from the Access 2002 database window,
              which lists the objects in the left pane of the window. When you click one of the
              first five items, the right pane gives options for creating the object, and it shows
              all existing objects of that type in the database. You can open an object by
              double-clicking it.




Access 2002                                                         5.3 In and Out of the Classroom with Office XP
                                  Exploring tables

                                      In Access, each row of a table is called a record. Each column of a table is
                                      called a field.


                                                                                                                           Field

                         Record




  There are two ways to
  access the list of
  views from the
  database window. You
  can click View on the               There are four ways to view a table in Access 2002. When you double-click a
  standard toolbar, or                table in the Access 2002 database window, it opens in Datasheet view. You
  you can click the                   use Datasheet view when you want to view, add, or delete data from a table,
  arrow next to the View
  button.
                                      or when you want to reformat your table to change the size and order of its
                                      fields. Use Design view when you want to change the structure of a table.
                                      You use this view to add or delete fields, change a field caption or size, or
                                      select the type of data you plan to enter into a field. You use PivotTable and
                                      PivotChart views when you want to analyse the data by making a table or
                                      chart.




                                  Planning Your Database
                                  Because the objects in a database are related to one another, building a
                                  database requires careful planning.
  During the planning
  phase, create a
  worksheet to map out
  what your database
                                  Determining the purpose of the database
  will look like.
                                      Brainstorm with your class about the questions that you would like the
                                      database to answer, and the reports that you would like the database to
                                      produce. For example, you might want to look at the causes of shipwrecks
                                      and whether those causes changed over the years as ship construction
                                      evolved, leading to a report on how future wrecks can be prevented. Or, your
                                      students can gather data first and then look for patterns in where, when, or
                                      how shipwrecks occur. Think about the information sources that you want to
                                      use to obtain data.


                                  Mapping the structure of the database

                                      As you determine what information you want from the database, you can
                                      decide what facts you need to store in the database and what subject each
                                      fact belongs to. You might decide, for example, to create multiple tables. Or,
                                      with an assignment like the one for shipwrecks, you may need only one table.
                                      Each record within the table can contain information about an individual
                                      ship.




4 In and Out of the Classroom with Office XP                                                                           Access 2002
                                 Each field in your database represents a fact about a particular subject. For
                                 example, certain facts about shipwrecks are historically important, such as
                                 name of ship, type of ship, dimensions, date built, date of first voyage, date
                                 of sinking, cause of sinking, location, cargo aboard, number of crew, and
                                 number of passengers. You might want to create a separate field for each of
                                 these. Here are some guidelines for determining the fields you need:

                                    Include all of the information you will need.

                                    Store information in the smallest logical parts. For example, if you
                                     believe that the majority of shipwrecks have been caused by storms, and
                                     you want to examine which months have the highest concentrations of
                                     sinkings due to storms, you should create separate fields for months and
                                     years.

                                    Create fields for data that contains only one item, not lists of multiple
                                     items. For example, instead of creating one field for ship dimensions,
                                     create separate fields for length, width, and weight. To ensure that you
                                     can sort and filter data effectively, make sure your fields contain discrete
                                     data types.

                                    Don’t include derived or calculated data—for example, a field that
 If it’s logical to divide
                                     multiplies the values of two other fields—as you might in an Excel
 your database into                  spreadsheet.
 multiple tables, you
 need to think about                Create fields that are different from each other in a way that is relevant,
 relationships between
 common fields among                 even if the type of data you will enter under them will be similar. For
 the tables.                         example, instead of creating fields like Date 1, Date 2, and Date 3,
 Establishing these                  create self-explanatory fields for dates: Year Built, Year of First Voyage,
 relationships will help             Month Sunk, and Year Sunk.
 you bring related
 information together
 in meaningful ways.             After you determine the purpose and structure of the database, study your
 For example, you may            plan and look for potential flaws. Although you can modify and extend your
 eventually want to use          database at any time after it has been created, it is easier to change your
 the Cause of Sinking
 field to list all ships         overall design in the planning phase than it is after the tables are filled with
 that sank due to                data.
 storms.




                             Creating Your Database
                             To get started with Access 2002, you can create a database to store information
 Use the Database            about ships that sank in the Great Lakes. Students can gather this information
 Wizard when you want        from scientific and anecdotal data found on the Web and in other sources, like
 to model your
 database on one of
                             film documentaries. They can then use Access 2002 queries to study trends and
 the templates that          analyse the data.
 Access 2002
 provides. The               There are two ways to create a database. You can use the Database Wizard to
 templates are
 generally business          create the required tables, forms, and reports for the type of database that you
 related and include         choose. Or, you can create a blank database and then add the tables, forms,
 Expenses, Inventory         reports, and other objects later.
 Control, and Order
 Entry, though you can
 change them to suit
 your needs. The
 options for accessing
 templates are
 available under in the
 New File task pane,
 under New from
 template.




Access 2002                                                                          5.5 In and Out of the Classroom with Office XP
                             To create a blank database
                                      1. Open Access 2002.

                                      2. On the File menu, click New.

                                      3. In the New File task pane, under New, click Blank Database.

                                      4. In the File New Database dialog box, browse to the location where
                                         you want to save the database, type Shipwrecks as the file name,
                                         and then click Create.




  Among the options for
  creating a table, the
  Table Wizard can be
  helpful because it
  provides sample
  tables and sample
  fields, though these
  samples are geared                 When you click Create, the Shipwrecks database window opens. You can
  toward business or                 now create and store tables, forms, queries, and reports in the database.
  personal use.




6 In and Out of the Classroom with Office XP                                                                     Access 2002
                         Adding objects and data to your database

                            The first object to add to your database is a table. For example, after you
                            create the Shipwrecks database, you can create one table; each record in
                            the table will store data about one specific ship. It’s easy to create a table in
                            Design view.


                         To create a table in Design view
 A data type is the              1. In the Shipwrecks database window, click the Tables button.
 characteristic of a
 field that determines
 the type of data the            2. Double-click Create table in Design view.
 field can hold. Some
 of the more common              3. In the first row of the Field Name column, type the first field (for
 data types are:
 Text. Use for fields               example, Ship Name) that will appear on a table in your database.
 where you plan to
 enter text.                     4. Click the Data Type column. The default data type of Text
 Date/Time. Use to
 indicate the date.
                                    automatically appears.
 Currency. Use to
 prevent rounding off                 Note In this case, the default data type works well. If you wanted
 during calculations.                      to change the data type, you could do so by placing your
 Hyperlink. Use to
 add a hyperlink.                          cursor in the Data Type column, clicking the arrow that
                                           appears, and then clicking the data type in the list.

                                 5. Type all the other fields in the Field Name column, and select the
                                    corresponding data type for each.




                                 6. Click the field that you want to be the primary key, and then on the
                                      toolbar, click the Primary Key button       .

                                 7. Click the Save button. In the Save As dialog box, type the name of
                                    the table (for example, All Ships), and then click OK.




Access 2002                                                                   5.7 In and Out of the Classroom with Office XP
                                      8. If you want to create additional tables for your database, you can
                                         do either of the following:

                                                If the tables will have significantly different fields, repeat this
                                                 procedure.

                                                If the tables will contain the same fields (for example, if you
                                                 were making a shipwrecks table for each of the five Great
                                                 Lakes), you can use your first table as a template for the others.
                                                 In the database window, right-click the table you want to copy,
                                                 and then click Copy. Right-click the white background area of
                                                 the window, click Paste, type a table name, click Structure Only,
                                                 and then click OK.

                                      9. Close the table in Design view. You can see that the table is
                                         contained in the database window.


                             Adding data
  For now, don’t worry
  about the order of the         After you create your tables and the fields within those tables, you are ready
  records you intend to
  add. You can                   to add data.
  organise them later.


                             To add data to a table
                                      1. In the database window, double-click the table. The table opens in
                                         Datasheet view.

                                      2. Type data—for example, Sygna—in the first field of the first record.
                                         As soon as you begin to enter a value in the first field, Access 2002
                                         automatically adds another record to the table.

                                      3. Press TAB or the RIGHT ARROW key to move to the second field,
                                         and type the data. For example, type Carrier under the Type of Ship
                                         field. If you need to change data that you already typed, you can
                                         move to the left by pressing SHIFT+TAB or the LEFT ARROW key.

                                              Note Unless a field is required, you can leave it empty.

                                      4. Add data in the remaining fields in the record.

                                      5. When you reach the end of the first record, press TAB or the RIGHT
                                         ARROW key to move to the next record in the table.

  To quickly move from
                                      6. Continue to add records and data.
  record to record, you
  can use the buttons                         Note The following illustration shows a sample database about
  at the bottom of the                             shipwrecks in Australia. It is not complete or comprehensive,
  table window in
  Datasheet view.                                  but provides enough data to show the capabilities of Access
                                                   2002. The procedures in this chapter that refer to the All
  To delete an entire                              Ships table refer to this table exactly as it is.
  record, select it by
  clicking the bar on
  the left, press
  DELETE, and then
  click Yes.




8 In and Out of the Classroom with Office XP                                                                           Access 2002
                                                                New record
                                             Next record

              First record Previous record                 Last record


                                       7. When you finish adding data, if you close the table, changes are
                                          saved automatically.


                           Finding records in a table

                                 At times, you may want to quickly make changes to a record, but because
                                 there are so many records in your table, the record you want isn’t
                                 immediately visible when you open the table. If you know the number of the
                                 record you want to see, you can double-click the number in the record
                                 number box (in the lower-left corner in Datasheet view), type the number of
                                 the record you want, and then press ENTER.

                                 Another way to quickly find a record—or to find information within a record—
                                 is by using the Find tool.




Access 2002                                                                     5.9 In and Out of the Classroom with Office XP
                             To find a record
                                      1. Open the table, and then activate the field for which you plan to
                                         enter a value. For example, if you want to find the record for the
                                         ship Loch Ard, click Ship Name so that the whole column is
                                         selected.

                                      2. On the Standard toolbar, click the Find button       .

                                      3. In Find and Replace dialog box, in the Find What box, type what
                                         you are looking for. For example, type Loch Ard

                                      4. In the Look in box, Ship Name is automatically entered. In the
                                         Match box, Whole Field is automatically entered. Because, in this
                                         case, you are searching for a whole entry in the Ship Name column,
                                         the information in these boxes is correct. Click Find Next.




                                      5. When you find the item, click Cancel to close the Find and Replace
                                         dialog box.




10 In and Out of the Classroom with Office XP                                                                 Access 2002
              Managing Data
              After you add data to your database, you can use the sort, filter, and query
              features in Access 2002 to organise the data in more meaningful ways.


              Sorting data

                  Access 2002 displays information in a table in alphabetical or numerical
                  order based on the primary key. However, you can sort information so that it
                  appears in an order that works best for you. For example, if you want to
                  organise ships alphabetically, you can select the Ship Name field in your All
                  Ships table and then click the Sort Ascending button.

                  In Datasheet view, you can sort all of a table’s records in ascending or
                  descending order, but you cannot use both sort orders on more than one
                  field. When you sort in Design view, PivotTable view, or PivotChart view, you
                  can sort records in ascending order by some fields and in descending order
                  by others.


              To sort records in Datasheet view
                       1. With the table open in Datasheet view, click the header of the field
                          to sort so that the whole column is selected.




                            Note If you select multiple columns, Access sorts records starting
                                 with the leftmost selected column.

                       2. On the Datasheet toolbar, click the Sort Ascending button          or
                          the Sort Descending button       .




Access 2002                                                      5.11 In and Out of the Classroom with Office XP
                             Using filters

                                 A filter is a set of criteria applied to data in order to display a subset of the
                                 data or sort the data. In general, you use a filter to temporarily view or edit
                                 records that contain a specific item. The rest of the records are then hidden
                                 from view. For example, if you want to find out how many ships sank in in the
                                 1870’s, you can use a filter to sort shipwreck data by location and
                                 chronologically at the same time.

                                 In Access 2002, you can filter records in four ways:

                                    Filter By Form. Use this option when you want to choose the values
                                     you’re searching for from a list without scrolling through all the records,
                                     or when you want to specify multiple criteria at once.

                                    Filter By Selection. Use this option when you can easily find and select
                                     the value you want the filtered records to contain.

                                    Filter For Input. Use this option when the focus is in a field and you just
                                     want to type in place the exact value you’re searching for or the
                                     expression whose result you want to use as your criteria, or when you
                                     want to specify multiple criteria at once.

                                    Advanced Filter/Sort. Use this option to search for records that meet
                                     multiple criteria, search for records that meet one criterion or another
                                     criterion, or enter expressions as criteria.


                             To filter by form
                                      1. With the table open in Datasheet view, on the toolbar, click the
                                           Filter By Form button       to switch to the Filter by Form window.

                                      2. Click the field in which you want to specify the criterion that records
                                         must meet to be included in the filtered set of records, and then in
                                         the pull-down list that appears, click the criterion. For example,
                                         click the Type of Ship field, and then click Passenger in the list.

                                      3. On the toolbar, click the Apply Filter button       .




12 In and Out of the Classroom with Office XP                                                                        Access 2002
                                4. When you want to view the full table again, on the toolbar, click the
                                   Remove Filter button.

                                    Note When you run a filter, the Apply Filter button becomes the
                                         Remove Filter button.




                         To filter by selection
                                1. With the table open in Datasheet view, click the item that you want
                                   to use to filter data. For example, if you want the table to show only
                                   those ships that carried passengers as cargo (so you can see what
                                   else those ships might have in common), click Passengers in any
                                   record in the table within the Cargo field.

                                2. On the toolbar, click the Filter By Selection button      .

                                3. When you want to view the full table again, on the toolbar, click the
 To use the other two              Remove Filter button.
 methods of filtering,
 see Access 2002
 Help.




Access 2002                                                              5.13 In and Out of the Classroom with Office XP
                             Using queries

                                 Queries are great for viewing only certain data contained in a table. You
                                 establish a set of criteria when you create the query. Then, when you run the
                                 query, Access 2002 returns only those fields or records that are of interest to
                                 you. The results appear in a table in Datasheet view.

                                 There are several types of queries in Access. A select query is the most
                                 common type. It does not change the data that it retrieves from tables, but
                                 after its results are displayed, you can update the records. You can also use
                                 a select query to group records and calculate sums, counts, averages, and
                                 other types of totals.

                                 Select queries are similar to filters in that you can use either to:

                                    Retrieve a subset of records from an underlying table or query.

                                    Produce results that can be used as the source of data for a form or
                                     report.

                                    Sort records.

                                    Edit data if editing is allowed.

                                 Whether you choose to use a query or filter depends on how you want to use
                                 the records that are returned. In general, you use a query if you want to do
                                 any number of the following:

                                    View the subset of records without first opening a specific table or form.

                                    Choose the tables containing the records you want to work with and add
                                     more tables at a later date if necessary.

                                    Control which fields from the subset of records appear in the results.

                                    Perform calculations on values in fields.

  The design grid is the         You can filter records first, and then save the filter as a query. Using this
  grid that you use to           method, you can bypass the query’s design grid altogether (unless you want
  design a query or
  filter in query Design         to make additional changes to the query). Even so, when you open the query
  view. For queries, this        in Design view, you’ll see how Access filled in the design grid by using the
  was formerly known             information from the filter. You can then use this information as a guide for
  as the QBE grid.               making additional changes.




14 In and Out of the Classroom with Office XP                                                                      Access 2002
                 Suppose you want to view a simplified version of your All Ships table to
                 compare only certain types of information, such as the causes of shipwrecks
                 and types of ships over time. The following procedure shows you how to
                 create a select query to obtain this information.


              To create a select query by using a wizard
                      1. In the Shipwrecks database window, under Objects, click Queries,
                         and then double-click Create query by using wizard.

                      2. In the Simple Query Wizard dialog box, select the correct table or
                         tables in the Tables/Queries box. For example, if you want to base
                         your select query on the All Ships table, select that from the list. By
                         default, Access 2002 will have selected the All Ships table as it is
                         the only existing table at this time.

                      3. For each field that you want to include in the query, click the field in
                         the Available Fields box, and then click the forward arrow          to
                         move the field to the Selected Fields box. For example, add the
                         Ship Name, Type of Ship, Year Sunk, and Cause of Sinking fields.
                         Click Next.




                      4. You will now be prompted to choose the level of detail you wish to
                         view using the query. Select Detail and click Next

                      5. In the appropriate box, type the name of the query (for example,
                         type Shipwreck Causes) or accept the name that Access 2002
                         suggests. Click Open the query to view information, and then click
                         Finish.




Access 2002                                                     5.15 In and Out of the Classroom with Office XP
                                      6. The query opens in Datasheet view. If you want to look at the
                                         design grid, switch to Design view by clicking the arrow next to the
                                         View button, and then clicking Design View.




                                     After you create a query and save it in the database, you never need to
                                     create that query again. When you change or add new records, the new
                                     data automatically appears in the query the next time you run it.
                                     Likewise, whatever changes you make in the query appear in the full
                                     table.


                             To run a query
                                      1. In the database window, under Objects, click Queries.

                                      2. Double-click the query you want to run. Access 2002 runs the query
                                         and displays the results in Datasheet view.


                             SQL queries

                                 An SQL query is a query you create by using a Structured Query Language
                                 (SQL) statement. You can use SQL to query, update, and manage relational
                                 databases such as Access 2002.
  For more information
  about creating SQL
  queries, see
                                 When you create a query, Access automatically constructs the equivalent
  Access 2002 Help.              SQL statements for you. If you want, you can view or edit the SQL statement
                                 in SQL view. However, after you make changes to a query in SQL view, the
                                 query might not be displayed the way it previously was in Design view.




16 In and Out of the Classroom with Office XP                                                                   Access 2002
              To view a query in SQL view
                       1. Run the Shipwreck Causes query. The results appear in Datasheet
                          view.

                       2. On the toolbar, click the arrow next to the View button       , and
                          then click SQL View. The query is now in SQL view.




              Using Forms
              Forms make it easy for you and your students to access information in a
              database. You can use a form to focus on one record at a time for viewing,
              adding, and deleting data. In addition, you can customise a form to view and print
              information in a style that you like—you can even add pictures to make the form
              more visually appealing. If you want to organise fields into subcategories, you can
              create a multipage form.


              Creating forms

                  The easiest way to build a form is by using the Form Wizard. The Form Wizard
                  helps you choose the layout of records in the form and also the background,
                  color, and format of the display. You can also preview the layout and style
                  options when you create a form by using the Form Wizard.

                  A form can be based on a table or a query. For the shipwrecks assignment,
                  you might decide to base the form on the full table so that students can
                  enter data in all the fields. After you create a basic form, you can customise it
                  in Design view.




Access 2002                                                       5.17 In and Out of the Classroom with Office XP
                             To create a form
                                      1. In the Shipwrecks database window, under Objects, click Forms,
                                         and then double-click Create Form by using wizard.

                                      2. In the Tables/Queries list box, select the table or query from which
                                         you want to create the form. For example, select the All Ships table
                                         from the list.

                                      3. Select the fields that you want your form to include. To base the
                                         form on the full All Ships table, select all the fields by clicking the
                                         double forward arrow        . Click Next.




                                      4. Click the layout that you want or that is most appropriate for the
                                         database. For example, to make sure that the form has enough
                                         room to display all field names, click Columnar. Click Next.




                                      5. Click the style you want, and then click Next.




18 In and Out of the Classroom with Office XP                                                                      Access 2002
                   6. In the appropriate box, type the name of the form or accept the
                      name that Access 2002 suggests. Click Open the form to view or
                      enter information, and then click Finish.

              The form opens in Form view, which is the default view when you open a
              form. You can enter information in a form through Form view or Datasheet
              view. Any changes you make to a record in a form automatically appear in
              the table that the form draws information from.




Access 2002                                               5.19 In and Out of the Classroom with Office XP
                             To customise a form
                                 You can customise the form in a number of ways. For example, because
                                 there is a finite number of causes of sinking, you can change the text box
                                 under the Cause of Sinking to a combo box to make entering data in that
                                 field faster (and to minimise possible data-entry errors).

                                 The following procedure shows a couple of the possibilities for customising
                                 the form for the Shipwrecks assignment.

                                      1. In the open form, on the toolbar, click the arrow next to the View
                                         button, and then click Design View.

                                      2. To change a text box to a combo box:

                                               Under Detail, right-click the text box in the Cause of Sinking
                                                field, point to Change To, and then click Combo Box.

                                               On the toolbar, click the Properties button     .

                                               In the Combo Box: Cause of Sinking dialog box, on the Data
                                                tab, click the Row Source Type box. In the list that appears,
                                                click Value List. In the Row Source Box, type
                                                Storm;Collision;Crew Error;Attack;Unknown and then close
                                                the dialog box.

                                      3. To insert a picture in the form:


                                               Click the Toolbox button      .

                                               In the toolbox, click the Image button  , and then in the area
                                                under Form Footer, drag the mouse to create the image size
                                                that you want.

                                               In the Insert Picture dialog box, locate the image file that you
                                                want to insert, and then click OK.

                                                Switch to Form view to see how the image looks in the form. To
                                                further customise, switch back to Design view.




20 In and Out of the Classroom with Office XP                                                                      Access 2002
                           Adding Data Through the Web
 A data access page
 gets its data from an
 Access database or        Your students do not need to use the classroom computer where the database is
 from an SQL Server        housed to add data. In fact, they can easily view and add information to a
 database. However,
 Microsoft Office Web      database from your school intranet, from home, or from anywhere in the world.
 Component controls        You accomplish this by creating a data access page, which is a Web page
 on the page can           published from Access.
 display data from
 other sources, like an
 Excel spreadsheet or      When you create a data access page, add it to the database, and save the
 chart. Microsoft Office   database to a network server, your students can enter data directly into the page
 Web Component             while viewing it in Microsoft Internet Explorer 5 or later. This view is called Page
 controls are
 interactive
                           view. Everything students add to the Web page is immediately saved in the
 components, such as       database.
 worksheets and
 charts on Web pages,      In addition to helping you manage records, data access pages encourage
 that facilitate data
 analysis. For more        collaboration by providing an efficient way for students to contribute to a single
 information, see          assignment. For example, you can give a homework assignment in which
 “About data access        students use the Web to add data to the table in the Shipwrecks database.
 pages” in
 Access 2002 Help.
                           Designing a data access page does not require any programming knowledge.
                           From Access 2002, you can create the page in Design view, or you can use the
                           Page Wizard.


                           To create a data access page by using the Page Wizard
                                     1. In the Shipwrecks database window, under Objects, click Pages.

                                     2. Double-click Create data access page by using wizard.

                                     3. In the Tables/Queries list box, select the table or query from which
                                        you want to create the form. For example, select the All Ships table
                                        from the list.




Access 2002                                                                    5.21 In and Out of the Classroom with Office XP
                                      4. Select the fields that you want your data access page to include. To
                                         base the data access page on the full All Ships table, select all the
                                         fields by clicking the double forward arrow. Click Next.




                                      5. On this page of the wizard, you can create a hierarchy that groups
                                         records from general categories to specific details. Click Next.

                                          Note If you wanted to set up a hierarchy, you would use the
                                               buttons next to the list of fields to add and prioritise
                                               grouping levels. For an example of what grouping levels look
                                               like, go to the topic in Access 2002 Help titled “About
                                               grouping records,” and then click On a data access page.

                                      6. Select a sort order for the fields. For the Shipwrecks database, a
                                         logical way to order records is alphabetically by ship name. To do
                                         this, in the first list box, click Ship Name (the Ascending button is
                                         selected by default). Click Next.




22 In and Out of the Classroom with Office XP                                                                    Access 2002
                     7. Type a title for the page, click Open the page, and then click Finish.

                         The database appears in Page view.




                Although you create the data access page in Access 2002, it is not stored in
                the database. Data access pages are saved as HTML files in one of the
                Windows desktop folders; Access 2002 then adds a shortcut to the file in the
                database window.


              To save a data access page
                     1. In Page view, on the File menu, click Save.

                     2. Browse to the folder where you want to store the HTML file.

                     3. Click Save.




Access 2002                                                   5.23 In and Out of the Classroom with Office XP
                             PivotTable and PivotChart views

                                 Access 2002 offers PivotTable and PivotChart views of tables, queries, views,
                                 stored procedures, functions, and forms. To understand the technology that
                                 these views are based on, it is helpful to know what PivotTable and
                                 PivotChart reports are.

                                 In a spreadsheet program like Excel 2002, you can use a PivotTable report to
                                 highlight data that meets specific criteria, while hiding the remainder.
                                 PivotTable reports make this especially easy when you are analysing large
                                 quantities of data that contain both numeric and nonnumeric data; for
                                 example, shipwreck location, year, and cause. After all the data is collected
                                 in a spreadsheet, the organisation of the data can be manipulated by
                                 rearranging, or pivoting, the layout based on specified row and column
                                 headings.

                                 Based on the shipwrecks example, an Excel 2002 user might want to further
                                 explore the data to see if more ships wrecked in a certain area because of
                                 fire or grounding. The layout can be changed at any time by adding or
                                 removing headings to find these trends or exceptions, while the original data
                                 set remains intact. The data analysis can then be graphically expressed as a
                                 PivotChart report. This type of comparative analysis is where Access can help
                                 students get beyond just reporting on information, and dig into rich analysis.

                                 With Access 2002, you can perform data analysis and build rich PivotTable
                                 and PivotChart views more quickly than ever before. For example, you can
                                 open a query in PivotTable view or PivotChart view to analyse data. You can
                                 rearrange row headings, column headings, and filter fields until you achieve
                                 the desired layout. Each time you change the layout, the form immediately
                                 recalculates the data based on the new arrangement. PivotTable and
                                 PivotChart views can be saved as data access pages that can be viewed by
                                 anyone who has Internet Explorer 5 or later.




24 In and Out of the Classroom with Office XP                                                                     Access 2002
                        To create a PivotTable view
                              1. In the Shipwrecks database window, under Objects, click Queries,
                                 and then double-click the query you want to open. For example,
                                 open the Shipwreck Causes query.
 You can use the same
 procedure to create
 PivotTable views for         2. On the toolbar, click the arrow next to the View button, and then
 other objects, like             click PivotTable View.
 tables and forms.
                                  You see a blank PivotTable view without any fields or data; you now
                                  must add the fields from the PivotTable Field List dialog box, which
                                  appeared on the right side of your screen.

                                  Note You can open and close this dialog box by clicking the Field
                                       List button   on the toolbar.




                              3. In the PivotTable Field List dialog box, drag each field to the place
                                 where you want it to appear in the PivotTable view. For example, if
                                 you want to compare types of ships and causes of sinking, drag the
                                 Type of Ship field to the area for column fields, and drag the Cause
                                 of Sinking field to the area for row fields.




                                   Note    The next procedure is for creating a PivotChart view from
                                           the same query.




Access 2002                                                           5.25 In and Out of the Classroom with Office XP
                             To create a PivotChart view
                                      1. On the toolbar, click the arrow next to the View button, and then
                                         click PivotChart View. You see a PivotChart view with some of the
                                         fields and data added from the PivotTable view that you created.

                                      2. Drag Count of Type of Ship to the area for data fields.

                                      3. To customise the appearance of the PivotChart view, on the
                                         toolbar, click PivotChart. For example, click PivotChart, and then
                                         click Show Legend. Your PivotChart view is now a bar chart that
                                         compares types of ships with causes of sinking.




                                      4. To save the object with the new PivotTable and PivotChart views, on
                                         the toolbar, click Save.




                             Creating Reports
                             After you and your students add information to the Shipwrecks database, you
                             may want your students to report on some aspect of the data they’ve collected.
                             Reports let you customise the display of information from the database. You can
                             select the data you want to include—for example, a particular query—and then
                             select the report layout from a variety of design and format options. You can
                             insert pictures; add headers, footers, and page numbers; group the information;
                             and change the background colors, among other things.

                             You can create different types of reports quickly by using wizards. For example,
                             the Label Wizard creates mailing labels, the Chart Wizard creates charts, and the
                             Report Wizard creates a standard report. For your first report, use the Report
                             Wizard. The Report Wizard asks you a series of questions to help you design the
                             data exactly as you want. After you create the report, you can open it in Design
                             view to modify its structure.




26 In and Out of the Classroom with Office XP                                                                    Access 2002
                         To create a report
                               1. In the Shipwrecks database window, under Objects, click Reports.

                               2. Double-click Create report by using wizard.
 This procedure is the
 quickest way to
 access the Report             3. In the Tables/Queries list box, select the table or query from which
 Wizard. To see the               you want to generate a report. For example, select the Shipwreck
 other report options,
 follow step 1. Then,
                                  Causes query from the list.
 on the database
 window toolbar, click         4. Select the fields that you want your report to include. To base the
 the New button.                  table on the full Shipwreck Causes query, select all the fields by
                                  clicking the double forward arrow. Click Next.

                                   Note If you want to include fields from multiple tables and queries
                                        in your report, don’t click Next after selecting the fields from
                                        the first table or query in the Report Wizard. Repeat the
                                        steps to select a table or query, and pick fields that you
                                        want to include in the report, until you have selected all the
                                        required fields.




                               5. Create a hierarchy for the records, if appropriate, and then click
                                  Next.

                               6. Select a sort order for the fields. For the Shipwrecks database, a
                                  logical way to order records is alphabetically by ship name. To do
                                  this, in the first list box, click Ship Name (the Ascending button is
                                  selected by default). Click Next.

                               7. Click the option buttons to view the different options for the layout
                                  and orientation of your report. Decide which ones you want, and
                                  then click Next.

                               8. In the list, click the style that you want for the report, and then click
                                  Next.

                               9. Type a title for the report, click Preview the report, and then click
                                  Finish.


Access 2002                                                               5.27 In and Out of the Classroom with Office XP
                                 The report window opens in Print Preview. To modify the design—for example,
                                 to add a picture—switch to Design view.




28 In and Out of the Classroom with Office XP                                                                  Access 2002
              Taking It Further
              As you explore the new features of Access 2002, you will continue to think of new
              ways to manage information. You can try the following activities to experiment
              further:

                     Survey students at a certain grade level about the people they admire,
                      and use Access to store that information. You can continue this process
                      for several years to track how attitudes change over time.

                     A department head can create an Access database to track class
                      resources, like books, videos, and video equipment.

                     In a social studies class, convert an existing Web page that contains
                      local census data to a data access page. Students can then use the data
                      access page from their Web browsers to find information and add it to a
                      database they are building.

                     Students in an economics class can use some of the information
                      contained in a large database to create a PivotChart view of stock
                      market upturns and downturns over a period of time.

                     Students in a chemistry class can draw correlations between tables in a
                      multi-table database. They can use the primary key field to view common
                      properties between compounds made from metals and compounds
                      made from nonmetals.




Access 2002                                                     5.29 In and Out of the Classroom with Office XP

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:11
posted:6/20/2012
language:English
pages:29