Access 2002 - California State University, Bakersfield



Access 2002 :

• This is Microsoft Access assignment.

• You need to develop, manipulate, control, and manage a database by following below instruction.

• There is no significant differences between 2002 and 2003.

• Please try the online tutorial of Access from Microsoft if you are NOT familiar with Microsoft Access.

• The online tutorial of Access is available on the class website

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 Access 2002 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 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 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, 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.

[pic]

Exploring tables

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

[pic]

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.

[pic]

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.

5. Double-click Create table in Design view.

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

7. Click the Data Type column. The default data type of Text automatically appears.

Note   In 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.

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

[pic]

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

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

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

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

13. 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, Access 2002 automatically adds another record to the table.

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

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

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

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

17. Continue to add records and data.

Note   The 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.

[pic]

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

19. On the Standard toolbar, click the Find button [pic].

20. In Find and Replace dialog box, in the Find What box, type what you are looking for. For example, type Lady Elgin

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

[pic]

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

[pic]

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

23. On the Datasheet toolbar, click the Sort Ascending button [pic] or the Sort Descending button [pic].

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:

• 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 [pic] 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 Lake field, and then click Michigan in the list.

3. On the toolbar, click the Apply Filter button [pic].

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.

[pic]

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 sank due to storms (so you can see what else those ships might have in common), click storm in any record in the table.

2. On the toolbar, click the Filter By Selection button [pic].

5. When you want to view the full table again, on the toolbar, click the Remove Filter button.

[pic]

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.

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

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, enter 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.

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 [pic] 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.

[pic]

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

[pic]

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

25. Double-click the query you want to run. Access 2002 runs the query and displays the results in Datasheet 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 customize 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 organize 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 customize it in Design view.

To create a form

1. In the Shipwrecks database window, under Objects, click Forms, and then double-click Create Form by using wizard.

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

27. 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 [pic]. Click Next.

[pic]

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

[pic]

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

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

[pic]

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

To create a report

1. In the Shipwrecks database window, under Objects, click Reports.

31. Double-click Create report by using wizard.

32. In the Tables/Queries list box, click the table or query from which you want to generate a report. For example, select the Shipwreck Causes query from the list.

33. Select the fields that you want your report to include. To base the 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.

[pic]

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

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

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

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

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

The report window opens in Print Preview. To modify the design—for example, to add a picture—switch to Design view.

[pic]

-----------------------

What you will do:

✓ Explore the f慥畴敲⁳景䄠捣獥⁳〲㈰䌍敲瑡⁥⁡慤慴慢敳䌍敲瑡⁥⁡慴汢൥摁⁤慤慴琠⁡慴汢൥潓瑲爠捥牯獤唍敳映汩整獲愠摮焠敵楲獥䌍敲瑡⁥⁡潦浲䌍敲瑡⁥⁡敲潰瑲഍č䄍捁散獳瀠潲敪瑣搠敯⁳潮⁴潣瑮楡湡⁹慤慴※瑩玒愠映汩⁥桴瑡挠湯慴湩⁳潣敤戭獡摥搠瑡扡獡⁥扯敪瑣⁳瑳牯摥椠⁡兓⁌敓癲牥搠瑡扡獡⹥഍č不睥欠祥潢牡⁤潣浭湡獤椠捁散獳㊠〰′湩汣摵㩥䘍ⰸ椠潦浲漠⁲敲潰瑲䐠獥杩楶睥‬楤灳慬獹琠敨映敩摬氠獩⹴䔍呎剅‬晡整⁲敳敬瑣湩⁧⁡楦汥⁤湩琠敨映敩摬氠獩⁴湩映牯牯eatures of Access 2002

✓ Create a database

✓ Create a table

✓ Add data to a table

✓ Sort records

✓ Use filters and queries

✓ Create a form

✓ Create a report

[pic]

An Access project does not contain any data; it’s a file that contains code-based database objects stored in a SQL Server database.

[pic]

New keyboard commands in Access 2002 include:

• F8, in form or report Design view, displays the field list.

• ENTER, after selecting a field in the field list in form or report Design view, automatically adds the field to the form or report design surface.

• CTRL+TAB moves the focus from a form or report section to a subsection.

[pic]

There are two ways to access the list of views from the database window. You can click View on the standard toolbar, or you can click the arrow next to the View button.

[pic]

During the planning phase, create a worksheet to map out what your database will look like.

[pic]

If it’s logical to divide your database into multiple tables, you need to think about relationships between common fields among the tables. Establishing these relationships will help you bring related information together in meaningful ways. For example, you may eventually want to use the Cause of Sinking field to list all ships that sank due to storms.

[pic]

Use the Database Wizard when you want to model your database on one of the templates that Access 2002 provides. The templates are generally business related and include Expenses, Inventory 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.

[pic]

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 toward business or personal use.

[pic]

A data type is the characteristic of a field that determines the type of data the field can hold. Some of the more common data types are:

Text.   Use for fields where you plan to enter text.

Date/Time.   Use to indicate the date.

Currency.   Use to prevent rounding off during calculations.

Hyperlink.   Use to add a hyperlink.

[pic]

For now, don’t worry about the order of the records you intend to add. You can organize them later.

[pic]

To quickly move from record to record, you can use the buttons at the bottom of the table window in Datasheet view.

To delete an entire record, select it by clicking the bar on the left, press DELETE, and then click Yes.

[pic]

To use the other two methods of filtering, see Access 2002 Help.

[pic]

The design grid is the grid that you use to design a query or filter in query Design view. For queries, this was formerly known as the QBE grid.

Record

[pic]

This procedure is the quickest way to access the Report Wizard. To see the other report options, follow step 1. Then, on the database window toolbar, click the New button.

Field

First record

Previous record

Next record

Last record

New record

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download