Access 2002

Access 2002

When you need a flexible system for organizing and analyzing 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 data, along with compatibility and integration with other programs.

This chapter will show you how to use Access 2002 to:

  • Build a database.Plan and create a database; create a table; add fields and data to the table.
  • Manage data.Sort records; use filters to sort data; create and run a query.
  • Analyze data.Create and use forms to access and analyze information in a database; create Microsoft PivotTable® and Microsoft PivotChart® dynamic views.
  • Report findings.Create a report based on an aspect of the data collected.

For example, if you’re beginning an assignment on the history of shipwrecks in the Great Lakes, and you need to obtain data from various sources and use a filter to sort data, it makes sense to store your records in a database. You can use Access2002 not only to store all kinds of information, but also to analyze the data and create professional reports.


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:

  • PivotTable and PivotChart views.If you’re already familiar with PivotTable and PivotChart reports in Microsoft Excel, you’ll be happy to know that they are now available in Access 2002. With PivotTable and PivotChart views, you can dynamically change the types of data that you can compare and analyze in a chart or table format.
  • Compatibility with Access 2000.You can work with and modify Access2000 files in Access 2002without 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 Design view in all objects (such as tables, queries, and reports) in your Access databases.
  • XML support.You can easily import and export XML data between Access databases and other databases, such as Microsoft SQL Server™.
  • Extended property support with Microsoft SQL Server 2000.Integration of Access 2002 and SQL Server 2000 includes support for extended SQL database properties from within an Access project. Using extended properties makes it easy to save column widths, row heights, and fonts from one Access project to another. You can also use extended properties to migrate your education data applications from Access databases to Access projects connected to SQL Server.
  • Improved accessibility features.There are new keyboard commands. In addition, two powers (1,000 percent and 500 percent) have been 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.

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.

Exploring tables

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

There are four ways to view a table in Access 2002. When you double-click a table in the Access 2002 database window, it opens in Datasheet view. You use Datasheet view when you want to view, add, or delete data from a table, 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 analyze 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.

Determining the purpose of the database

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.

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 multiplies the values of two other fields—as you might in an Excel spreadsheet.
  • Create fields that are different from each other in a way that is relevant, even if the type of data you will enter under them will be similar. For example, instead of creating fields like Date 1, Date 2, and Date 3, create self-explanatory fields for dates: Year Built, Year of First Voyage, Month Sunk, and Year Sunk.

After you determine the purpose and structure of the database, study your plan and look for potential flaws. Although you can modify and extend your database at any time after it has been created, it is easier to change your overall design in the planning phase than it is after the tables are filled with data.

Creating Your Database

To get started with Access 2002, you can create a database to store information about ships that sank in the Great Lakes. Students can gather this information from scientific and anecdotal data found on the Web and in other sources, like film documentaries. They can then use Access 2002 queries to study trends and analyze the data.

There are two ways to create a database. You can use the Database Wizard to create the required tables, forms, and reports for the type of database that you choose. Or, you can create a blank database and then add the tables, forms, reports, and other objects later.

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.

When you click Create, the Shipwrecks database window opens. You can now create and store tables, forms, queries, and reports in the database.

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

  1. In the Shipwrecks database window, click the Tables button.
  1. Double-click Create table in Design view.
  2. In the first row of the Field Name column, type the first field (for example, Ship Name) that will appear on a table in your database.
  3. Click the Data Type column. The default data type of Text automatically appears.

NoteIn this case, the default data type works well. If you wanted to change the data type, you could do so by placing your cursor in the Data Type column, clicking the arrow that appears, and then clicking the data type in the list.

  1. Type all the other fields in the Field Name column, and select the corresponding data type for each.
  1. Click the field that you want to be the primary key, and then on the toolbar, click the Primary Key button .
  2. Click the Save button. In the Save As dialog box, type the name of the table (for example, All Ships), and then click OK.

  1. 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.
  1. Close the table in Design view. You can see that the table is contained in the database window.

Adding data

After you create your tables and the fields within those tables, you are ready to add data.

To add data to a table

  1. In the database window, double-click the table. The table opens in Datasheet view.
  1. Type data—for example, Edmund Fitzgerald—in the first field of the first record. As soon as you begin to enter a value in the first field, Access2002 automatically adds another record to the table.
  2. Press TAB or the RIGHT ARROW key to move to the second field, and type the data. For example, type freighter 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.

NoteUnless a field is required, you can leave it empty.

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

  1. 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.
  2. Continue to add records and data.

NoteThe following illustration shows a sample database about shipwrecks in the Great Lakes. It is not complete or comprehensive, but provides enough data to show the capabilities of Access 2002. The procedures in this chapter that refer to the All Ships table refer to this table exactly as it is.

  1. 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.

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 Lady Elgin, click Ship Name so that the whole column is selected.
  1. On the Standard toolbar, click the Find button .
  2. In Find and Replace dialog box, in the Find What box, type what you are looking for. For example, type Lady Elgin
  3. 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.

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

Managing Data

After you add data to your database, you can use the sort, filter, and query features in Access 2002 to organize 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 organize 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.

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

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

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 Lake Superior in the 1900s, 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: