Chapter 7: Pivot Tables and Data Access Pages



Chapter 7: Pivot Tables and Data Access Pages

Learning Objectives

This chapter covers features that allow you to use the more specialized objects of Access 2003. At the completion of this chapter, you should have acquired the knowledge and skills to:

• Understand the relationship between pivot tables and data cubes.

• Understand the process to design a pivot table

• Create a pivot table and view a pivot table as a pivot chart.

• Understand conceptual issues important to the development of Data Access Pages.

• Use the Page Wizard, the Auto Page, and the Design view to create Data Access Pages.

• Understand the importance of relative links to Data Access Pages.

Overview

In other lab chapters you learned how to create the objects of a database application system. You created tables, queries, forms, and reports. This chapter shows you how to build and customize a few specialized database objects. You will create a pivot table to demonstrate how Access supports decision making with multidimensional data. Pivot tables provide a more flexible interface for multidimensional data than crosstab queries presented in Chapter 6. Next, you will gain experience with creating Data Access Pages, a tool for publishing live data on the Web. You will learn how to create a Data Access Page using the Page Wizard, the Design view, and the Auto Page features of Access. You will gain experience with Data Access Pages for both data retrieval and update.

7.1 Pivot Tables

Data warehouse processing requires a multidimensional data representation that is convenient to business analysts. Data cubes, a widely accepted representation of multidimensional data, support operations for decision support processing by business analysts. In a data cube, cells contain numeric data called measures while rows and columns contain dimensions to organize the cells. Textbook Chapter 16 provides detailed conceptual material about data cubes and other aspects of data warehouses.

In Access, pivot tables and pivot charts provide a convenient and flexible interface for manipulating data cubes. Pivot tables display data in rows and columns and allow convenient rearrangement of the row and column headings. Pivot charts display numerical data graphically to provide insights to business analysts. Pivot tables support dynamic manipulation of the row and column headings as compared to the static representation of crosstab queries presented in Chapter 6. Pivot tables were first available in Access 2000, but Excel 2000 was needed to use them. In Access 2002 and 2003, Excel is not needed to use pivot tables. Pivot charts were new in Access 2002.

This section provides practice with pivot tables and pivot charts. Because the terminology of pivot tables is somewhat different than the terminology of data cubes in textbook Chapter 16, the first section presents background on pivot tables and pivot charts. The second section provides practice with creating pivot tables and viewing a pivot table as a pivot chart. The third section extends the pivot table presented in the second section and discusses additional pivot table features.

7.1.1 Background about Pivot Tables

To explain the Access terminology, Figure 1 presents a pivot table for parts used data along with annotations to depict the terminology. Table 1 provides explanations for the annotated parts of Figure 1 along with some additional terms.

[pic]

Figure 1: Example Pivot Table

Table 1: Access Terminology for Pivot Tables

|Term |Meaning |

|Filter Field (1) |Restricts data that appears in the pivot table. City is a filter field. |

| |The user can choose to display pivot table data for selected cities by |

| |choosing a value for the City filter field. |

|Row Field (2) |A field appearing in the row area of a pivot table. PartDesc and TimeRecvd|

| |are row fields. |

|Column Field (3) |A field appearing in the column area of a pivot table. Make and Model are |

| |column fields. |

|Detail Field (4) |A field appearing in the cell area of a pivot table. The numeric values in|

| |the cells are the sum of the parts used for the combination of the row and|

| |the column values. For example, $50 is the sales for gear boxes repaired |

| |in BMWs. |

|Field drop-down arrow (5) |The arrow at the right side of each field. The user can select values to |

| |display or hide by clicking the arrow. |

|Item |A data value within a field. Expanding a field displays its items, while |

| |collapsing a field hides its items. In Figure 1, PartDesc and Make are |

| |expanded, while TimeRecvd and Model are collapsed. |

Beyond understanding the Access terminology, you should understand the correspondence between data cubes as presented in textbook Chapter 16 and pivot tables. Table 2 summarizes the correspondence between the terminologies. If you are unfamiliar with the data cube terminology, you should review textbook Section 16.2.

Table 2: Terminology Correspodence between Data Cubes and Pivot Tables

|Data Cube Term |Pivot Table Term |

|Dimension |Row field, Column field |

|Measure, Measure value |Detail field, Detail item |

|Dimension hierarchy |Custom groups of a row field or column field. Date/Time fields have |

| |predefined groups. Dimension hierarchies can also be implemented using |

| |adjacent row or column fields. |

|Member |Row item, Column item |

Before creating a pivot table, you should carefully plan its components and formulate a query to retrieve the data contained in the pivot table. In date cube terminology, you should determine the dimensions, dimension hierarchies, and measures. For each measure, you should determine whether it is a basic or a derived measure. For each derived measure, you should determine whether it is a summary value (computed from groups of rows) or a row value (computed from individual rows). Pivot tables containing row data are more flexible that pivot tables with only summary data although row data consumes more space than summary data. After determining the components of a pivot table, you should formulate the query to derive the data used in the pivot table. The query should contain all fields of the pivot table although derived measures can be calculated by either the pivot table or by the query. Beyond these general guidelines, here are some specific points to consider when designing pivot tables:

• With pivot tables, an important way to represent dimension hierarchies is using adjacent row or column fields. Typically, geographic dimension hierarchies such as country/region/state/city can be represented as adjacent fields.

• If the pivot table only contains summary measures, the underlying query can contain a GROUP BY clause and aggregate functions to compute the summary measures. However, defining the underlying query to retrieve row data instead of summary data provides more flexibility for modifications to the pivot table.

• If the pivot table contains at least one row measure, the underlying query should not have a GROUP BY clause. In addition, summary measures are computed by the pivot table, not by the query.

7.1.2 Creating Pivot Tables

Before discussing the steps to create pivot tables, the design of the pivot table example is presented. The pivot table should contain part descriptions and the makes and models of vehicles as dimensions. For measures, the amount paid for the repair along with the sum of the amount paid are the measures. For comparison purposes with the crosstab query in Chapter 6, we use RptQuery4 (shown in Figures 2 and 3) as the underlying query for the pivot table. The next section extends this simple pivot table design with additional fields and other pivot table features.

[pic]

Figure 2: Design View of RptQuery4

[pic]

Figure 3: Datasheet of RptQuery4

The following steps demonstrate how to create a pivot table using the PivotTable Wizard. After creating the pivot table, you will be instructed to modify it and manipulate its data.

1. Open the PivotTable Wizard: Return to the Forms section in the Database window and click the New button to begin. Select “PivotTable Wizard” in the New Form window. Then select “RptQuery4” in response to choosing a table or query. Click OK to continue.

• An alternative method is the “AutoForm: PivotTable” choice in the New Form window. The wizard is the preferred approach as the autoform tool does not allow the choice of fields from the chosen data source.

2. The First Wizard Window: The first window (Figure 4) gives a description of a pivot table. When you are finished viewing the remarks, click the Next button to continue.

3. Select Fields to Include: Click the > button to move the PartDesc, Model, Make, and Amount fields shown in the left side (Available Fields) to the right side (Fields Chosen for Pivoting). Uncheck the Show Introductory Page box. Figure 5 shows the selected fields. Click Finish to continue and a PivotTable design window opens along with a Field List window containing the fields necessary for laying out your pivot table (Figure 6). Notice on the PivotTable design window that the top and left sides are already divided and labeled to assist with the field layout.

4. Place Fields on the Diagram: To place the fields onto the diagram, you may drag them from the Field List window into the designated areas of the design window. Alternatively, you may select them in the Field List window and choose the area where they belong from the drop down menu at the bottom of the dialog box by clicking the Add to button (Figure 7). Until you are familiar with the design window’s labeled areas, it is suggested that you use the drop down menu choices as follows: place the PartDesc field in the row area (Figure 8), the Make and Model fields in the column area (Figure 9), and the Amount field in the data area[1] (Figure 10).

[pic]

Figure 4: Introductory Window of the PivotTable Wizard

[pic]

Figure 5: Window to Choose Fields in the PivotTable Wizard

[pic]

Figure 6: Window to Arrange Fields in the PivotTable Wizard

[pic]

Figure 7: Pivot Table Field Box

[pic]

Figure 8: PartDesc Field after Adding it in the Row Area

[pic]

Figure 9: Make and Model Fields after Adding them in the Column Area

[pic]

Figure 10: Amount Field after Adding it in the Data Area

5. Format Sum of Amount field: To change the properties for the Sum of Amount field, right click on the field title (“Sum of Amount”). Then, choose the Properties item to open the Properties window (Figure 11). In the Captions tab, change the name to “Parts Used Sales” (Figure 12). In the Format tab, change the Number area of the window to “Currency” in the drop down menu (Figure 11) if there is another format specified. In the diagram, the Sum of Amount field has changed to Parts Used Sales (Figure 13).

[pic]

Figure 11: Field Properties Window

[pic]

Figure 12: Changed Title Field Name

[pic]

Figure 13: Title Changed in the Pivot Table DesignWindow

6. Modify the Pivot Table Properties: To modify the pivot table properties such as the Record Source and Caption, you need to use Design view. You can select Design view using the View toolbar or the View menu. In Properties window for the form, select either the Format tab or the All tab and locate the Caption property. Change the caption to “Parts Used Pivot Table” (Figure 14) and close the Properties window. Although the title on the form will be unchanged until it is saved, you can return to the PivotTable view and see that the title is changed. Now, return to the Design view and close it. When prompted to save changes, click Yes and type in the Form Name “PartsUsedPivotTable”.

7. View the Pivot Table as a Pivot Chart: To view the pivot table as a pivot chart, you need to use the PivotChart view available in the View menu or View toolbar. Switch to PivotChart view to see the pivot table displayed graphically as a pivot chart (Figure 15). You can use the drop down arrows to restrict the items summarized in the pivot chart. After experimenting with the pivot chart, return to the PivotTable view. Note that changes made in PivotChart view may affect the PivotTable view. For more details about pivot charts, you are encouraged to study the Access help documentation.

[pic]

Figure 14: The Pivot Table in Form Design View

[pic]

Figure 15: The Pivot Table in PivotChart View

7.1.3 Extending the Pivot Table

The pivot table in the previous section is somewhat limited because it lacks a time dimension, an important element of most data cubes. In addition, the pivot table lacks a filter field to restrict the data displayed. In this section, we add these elements to the previous pivot table and then demonstrate some additional features of pivot tables.

To extend the pivot table of the previous section, we first extend the underlying query and then create another pivot table with the additional fields. Follow these steps to create the new query and the pivot table.

1. Modify the Underlying Query: Add the Customer table to the Query Design window as shown in Figure 16. Then, add the RepairOrder.TimeRecvd field and the Customer.City field to the query result. Save the query as “RptQuery4Revised” when you finish with the changes.

2. Define a New Pivot Table: Use the PivotTable Wizard to create a new pivot table with RptQuery4Revised as the underlying query.

3. Choose Fields: In the first wizard window, choose the fields shown in Figure 17. Note the addition of the City and TimeRecvd fields to the ones chosen for the previous pivot table. Click the Finish button to see the skeletal pivot table.

4. Create Pivot Table Fields: Drag PartDesc and then TimeRecvd from the field list to the row area of the pivot table. Then, drag Make and Model from the field list to the column area. Finally, drag City to the filter area. Figure 18 shows the resulting pivot table.

5. Create the Detail Fields: Drag Amount from the field list to the detail area. Then right-click on the “Amount” field heading and select AutoCalc(Sum from the menu. Figure 19 shows the resulting pivot table.

6. Rename the Calculated Field: Change the name of the calculated field from “Sum of amount” to “Parts Used Sales” by using the Caption tab of the Property window for the field. Then, right-click the Amount field heading and select Hide Details from the menu. Figure 20 shows the resulting pivot table.

7. Compact the Pivot Table: Remove the members of the TimeRecvd and Make fields using the Collapse menu item. To remove the TimeRecvd items, right-click the PartDesc field heading and select Collapse from the menu. Similarly, to remove the Model items, right-click the Make field heading and select Collapse from the menu. Figure 21 shows the compressed pivot table after collapsing both fields.

8. Filter the Pivot Table: To see the effect of filtering, use the drop-down arrow of the City field. Uncheck all members except “Seattle”. Figure 22 shows the pivot table after filtering for Seattle customers.

9. Save the Pivot Table: Change the caption of the pivot table to “Part Used Pivot Table 2” using Form Properties window in Design view. Then save the pivot table as “PartsUsedPivotTable2”. Close the pivot table when you are finished.

[pic]

Figure 16: Revised Query for the Modified Pivot Table

[pic]

Figure 17: Window to Choose Fields for the Modified Pivot Table

[pic]

Figure 18: Pivot Table after Dragging Fields to the Row, Column, and Filter Areas

[pic]

Figure 19: Pivot Table after Dragging Amount to the Detail Area

[pic]

Figure 20: Pivot Table after Hiding the Amount Field

[pic]

Figure 21: Pivot Table after Collapsing Fields

[pic]

Figure 22: Pivot Table after Filtering on Seattle

Access also provides the ability to group row and column fields. For date/time fields, Access provides predefined grouping levels using the Filter and Group tab of the Properties window. For the TimeRecvd field, you can group on predefined intervals such as year, quarter, and month. Grouping to a finer or coarser level is comparable to drilling down or up as described in textbook Chapter 16. You can determine groups for fields without predefined groups by selecting items and using the Group menu item.

7.2 Data Access Pages

Data Access Pages (DAPs) are Web pages that dynamically interact with an Access database. DAPs can also interact with other data sources such as SQL Server databases although this topic is outside the scope of this lab book. DAPs were first available in Access 2000 although there were many limitations on the available database objects and actions supported. The most significant limitation in Access 2000 was no update operations in DAPs. Access 2002 provided support for DAPs with update capabilities and provides powerful tools for creating DAPs and saving tables, queries, forms, and reports as DAPs. Access 2003 continues with the DAP support provided in Access 2002.

Although DAPs are easy to create in Access 2003, publishing them on a server is a challenge. On the client side, DAPs require the Internet Explorer 5 (SP2) or higher and the Microsoft Office XP Web Components. Because of these client requirements, DAPs are suited primarily for intranets. On the server side, DAPs require the Microsft Internet Information Server 4 or higher along with the Microsoft Data Access Components. In addition to these requirements, there are a number of other complicating issues about publishing DAPs. If you intend to publish DAPs, you should review details on the Microsoft Developer Network site (MSDN) in the topic “Deploying Data Access Pages over the Internet or an intranet”.

If you can live with pages containing static data instead of dynamic data, you can use the File(Export… command to convert database objects into eXtensible Markup Language (XML) pages. To export to XML, you need to choose “XML Document” as the file type in the Export Form window. Database objects exported as XML can be viewed by most browsers used on the Internet. Access 2003 provides a number of enhancements for exporting, importing, and transforming XML data. For more information about the new Access 2003 XML features, you should search the MSDN website in the topic “New XML Features in Microsoft Office Access 2003”.

In this section, you will create Data Access Pages (web pages) for the Auto Repair database using the Page Wizard and the Design View window. Both tools support grouped data similar to the reports presented in Chapter 6. As shown in Chapter 6, grouping improves the visual appearance of the data for decision making. You will also create DAPs using the AutoPage feature that does not support grouping of data. The tools for DAP creation are similar to the Form Wizard and the Report Wizard described in Chapters 4 through 6.

As an alternative to using the DAP creation tools presented in this section, you can convert existing database objects into DAPs. To save a database object as a DAP, you should select the database object and then use the File(Save As… command selecting Data Access Page as the type. This conversion feature works well on simple database objects such as forms without subforms. However, this method does not provide satisfactory results on complex objects such as hierarchical forms and reports with many grouping levels. You are encouraged to experiment with the conversion feature to see when you should use the DAP creation tools directly rather than use other creation tools and convert them to DAPs.

7.2.1 Creating a Data Access Page Using the Page Wizard

The Page Wizard enables you to create a DAP from single table or multiple tables. It also allows you to utilize a grouping process for viewing data similar to the Report Wizard. Your goal for this exercise is to generate a Data Access Page similar to the repair order reports that you created in Chapter 6. The steps are as follows:

1. Open the Page Wizard: Select the Pages section on the left side of the Database window. Then, click the New button at the top of the Database window and select “Page Wizard” in the New Data Access Page window. You may also click on the sentence in the database window that says, “create data access page by using page wizard” to activate the page wizard. Select PartsUsed from the drop-down menu at the bottom (Figure 23). Click OK to continue.

2. Select Fields to Include: Click the >> button to move all of the fields shown in the left side (Available Fields) to the right side (Selected Fields).

• In the same wizard window, go up to the Tables/Queries selection box where the PartsUsed table name appears and select Customer. Select the LastName field and click the > button to move it to the right side (Selected Fields).

• In the same wizard window, again go up to the Tables/Queries selection box and select the Part table. Select the PartDesc field and click the > button to move it to the right side (Selected Fields).

• For the final time, go up to the Tables/Queries selection box and select the RepairOrder table. From the RepairOrder table select the TimeRecvd and the TimeFinish fields and move them to the right side. Figure 24 shows all of the selected fields on the right side. Click the Next button when finished.

3. Add Grouping Levels: Select and add OrdNo, LastName, and then TimeRecvd using the > button. However, TimeRecvd must be changed because it defaults to TimeRecvd by Month instead of “by Day”. To correct this, perform the following:

• Click the Grouping Options button at the lower left of the window (Figure 25). Note that the Grouping intervals column for the TimeRecvd field is set to “Month”. Change the grouping interval to “Day” for the TimeRecvd field. Before clicking the OK button, the Grouping window appears as in Figure 25. Click the Next button to continue.

[pic]

Figure 23: New Data Access Page Window

[pic]

Figure 24: Initial Page Wizard Window Showing All Selected Fields

[pic]

Figure 25: Grouping Window and Grouping Interval Window

4. Sort Order: Select “None” and click Next.

5. Title the Page: Type “Part Report”, choose “Modify the page’s design”, check “Do you want to apply a theme to your page?” (Figure 26), and click Finish.

6. Choose a Theme: In the Theme choice box (Figure 27), choose “Blends” and click OK.

7. View the Page: The new page opens in page design view (Figure 28), but you can toggle to page view on the left side of the toolbar as you would do for viewing forms and reports. Page view lets you see how the page will appear in the Internet Explorer (5 or higher).

• How Data Are Grouped: The data are grouped by OrdNo, LastName, and TimeRevcd by Day.

• Navigation Bars: At the bottom of the page are navigation bars to locate the appropriate records within a given group. Using the navigation bar follows a pattern similar to its use for Access datasheets.

• Figures 29 through 31 show the page in its group modes. Just keep clicking the + or - symbols to expand or hide the grouped items. Then use the arrows on the navigation bars to locate a particular record.

8. Modify the Page: As you can see, some modification remains to be done on this page. Proceed to the next section and follow the directions given. If you must close the page, it will be saved automatically and if you get a connection string message box, just click OK. More about how DAP files are saved is discussed at the end of the next section in case you want to read ahead.

[pic]

Figure 26: Final Wizard Window

[pic]

Figure 27: Theme Selection Window

[pic]

Figure 28: Design View of the Part Report Data Access Page

[pic]

Figure 29: Page View of the Part Report Data Access Page (First Group)

[pic]

Figure 30: Page View of the Part Report Data Access Page (Second Group)

[pic]

Figure 31: Page View of the Part Report Data Access Page (Third Group)

7.2.2 Modifying the Part Report Data Access Page

The previous section demonstrated two views for DAPs that are somewhat different than the views of other database objects. You worked with Design view (Figure 28) and Page view (Figures 29 to 31). A third view, Web Preview view, is the same as page view, but it is viewed in Microsoft Explorer. Take a few minutes and toggle through these views using the icons/arrow at the left corner of the toolbar as you have already done for the form and report chapters.

Return to design view, and notice that a DAP is divided into a number of sections similar to a report (Figure 32). As you can see from Figure 32, the header sections are named according to the grouping priorities you assigned in the Page Wizard. Under the gray header sections are the individual items in that group. Following the header sections are the navigation bars that are used for locating and sorting records to be viewed in the related header section. Next, notice that the gray headers have arrows. When the arrows are clicked, you will see a selection of options that may appear on the page with that particular header, along with a choice for Group Level Properties that concern how the items grouped with the header will appear on the page.

[pic]

Figure 32: Sections of a Data Access Page

In this section, you will make text changes to the header section items and the navigation bars, and add some captions above the headings. So, open the page in design view, and let’s get started.

1. Type a Page Title: Scroll to the top of the page (Figure 33). Replace “Click here and type title text” with “Part Report”.

2. Changes to Header: RepairOrder-OrdNo: Slowly double-click the label under the header that says “OrdNo” and the cursor point will appear inside (Figures 34 and 35). Delete the current caption and type “Repair Order Number”. Do not worry about the text flowing onto two lines or the position of the label.

3. Changes to Header: RepairOrder-LastName: Slowly double-click the label under the header and the cursor point will appear inside. Delete the current caption and type “Customer’s Last Name”. Do not worry about the text flowing onto two lines or the position of the label.

4. Changes to Header: RepairOrder-TimeRecvd by Day: Slowly double-click the label under the header and the cursor point will appear inside. Delete the current caption and type “Day Vehicle Received”. Do not worry about the text flowing onto two lines or the position of the label.

5. Changes to Header: PartsUsedWiz: Slowly double-click each label inside the header and make the changes shown in Table 3.

6. Add Caption: PartsUsedWiz: Click the arrow on the gray header and click Caption and a caption section appears above the header. Place a label in the caption section and type “Parts Information”. After typing it, select it and make it Bold. Figure 36 shows the completed text changes and additions to the labels in design view.

Table 3: Label Changes

|Old Label Text |New Label Text |

|PartNo |Part Number |

|QtyUsed |Quantity Used |

|PartDesc |Part Desciption |

|TimeRecvd |Time Received |

|TimeFinish |Time Finished |

[pic]

Figure 33: Design View Showing Where to Type Title Text

[pic]

Figure 34: Design View Showing a Selected Label Control (Single Mouse Click)

[pic]

Figure 35: Design View Showing a Selected Label Control (Double Mouse Click)

[pic]

Figure 36: Design View of Completed Text Changes

7. Changes to the Navigation Bars: Each navigation bar on a page corresponds to a particular field on the page. Navigation bars allows a user access to a field’s data while toggling through records by clicking on the directional arrows on the bar. Therefore, deleting a navigation bar keeps the user from acccessing records by means of that particular field. The instructions below will guide you to delete two navigation bars and make text changes to the remaining two navigation bars.

• PartsUsedWiz Bar: Under the navigation bar, right-click the field label and then select Object Properties from the menu. Select the Data tab containing the RecordSetLabel property. Delete “PartsUsedWiz” (leaving in “(0 of (…”) and replace it with “Parts per Vehicle” (Figure 37). Note that the label on the design view page may not reflect this change immediatley.

• RepairOrder-TimeRecvd by Day Bar: Delete this navigation bar since it will not be used. To do this, go up to the section header titled “RepairOrder-TimeRecvd by Day” and click the arrow and then click on Record Navigation to unselect it (box is no longer checked, Figure 38), and this navigation bar will disappear.

• RepairOrder-LastName Bar: Delete this navigation bar since it will not be used. Go to the section header titled “RepairOrder-LastName” and click the arrow and then click on “Record Navigation”, and this navigation bar will disappear.

• RepairOrder-OrdNo: Under the navigation bar, right-click the field label and then select Object Properties from the menu. Select the Data tab containing the RecordSetLabel property. Delete “RepairOrderNo” (leaving in “(0 of (…”) and replace it with “Repair Order Number”. Note that the label on the design view page may not reflect this change immediatley.

Align Remaining Navigation Bars Together: Click once on the light gray “Repair Order Number” bar to select it while holding the mouse button and drag the bar to the right so it is aligned under the “Parts per Vehicle” bar (Figure 39). This action will make the two bars more attractive on the page

8. Set Group Level Properties: So far when you view the DAP in Page view or Web Preview view you must click on the + symbol to display the next lower group items. Since this may be tedious for a user, you can set a property that allows all of the groups to be displayed at once on the page. To make this modification, click the arrow on each section heading, beginning with the first heading RepairOrder-OrdNo, and at the bottom of the menu, choose Group Level Properties →ExpandedByDefault. Click in the property box and use the arrow to change the value from “False” to “True” (Figure 40). Continue doing the same to the other headings. However, on the lowest heading group (PartsUsedWiz), you cannot change the value because it is already the lowest group on the page.

9. View the Part Report Data Access Page: Toggle to page view. Your completed page should resemble Figure 41. Try clicking on a field to activate its corresponding navigation bar at the bottom of the pate. Click on the bar’s bold directional arrows to scroll through the records. When you are finished, close your page and Access will prompt you for the file name and the location. The file will be saved with the “.htm” extension, the standard extension for web pages. Access automatically creates a subdirectory in the My Documents folder named according to your DAP. In this case, the file name will be “Part Report_ Files” when you save your Part Report. Later, if you want to open the file outside of the Database window, remember the file name and the location.

[pic]

Figure 37: Design View Showing a Selected Navigation Bar and RecordsetLabel Property

[pic]

Figure 38: Design View Showing Unselected Navigation Section

[pic]

Figure 39: Design View Showing the Aligned Navigation Section

[pic] [pic]

Figure 40: Group Level Property ExpandedByDefault

[pic]

Figure 41: View of the Completed Part Report Data Access Page in Page View

7.2.3 Creating a Data Access Page in Design View

Design view provides another way to creating DAPs with grouped data. You will create a Customer Repair Report in Design view to familiarize yourself with the features of DAP Design view.

1. Open Design View: Open Design view by choosing it in the New Data Access Page window (Figure 23) or you may click on the sentence in the database window that says, “create data access page by using Design view”. Using either method, the Design view window will open with a field list pane on the right side (Figure 42). From here you may select fields from a table or query from the drop-down list by clicking the “+” signs”. Note that the related tables are also listed with each table. If the field list does not appear, click View ( Field List.

2. Draging Fields to the Page: When you drag fields to the page, the table name will become the heading group with the desired fields located underneath. Each time you drag a field from another table, the page will create a blue square and ask you if you want that table’s group to be above or under the current heading group. After you create each group as instructed below, you can check Figure 44.

• Create the First Heading Group: Click the + symbol next to the Customer table to display the fields. Since we want all of the fields in that table, select the Customer table name and drag it to the left side of page. When you release the mouse, a Layout Wizard window will appear and choose “Columnar” (Figure 43). The fields will appear on the page under the Customer heading, along with a corresponding navigation bar.

• Create the Second Heading Group: Since the Vehicle table is listed as related to the Customer table, the next fields will come from that table, so click the “+” to display its fields (or do this with the Vehicle table already listed at the end of the filed list pane). Select the fields: Year, Make, and Model (select each one while holding down the shift key) and drag them to the page. Before releasing the mouse, notice the blue square that appears saying, “create new section below Customer”. Continue draging the fields until you are in that area below the Customer section, towards the left side, and then release the mouse. When the Layout Wizard window appears, again choose “Columnar”. Now you have a second heading group called Vehicle along with its fields and navigation bar.

• Create the Next Lower Heading Groups: Repeat the procedures described above with the following tables and fields. Each time, drag the fields to the lowest level, towards the left side, and choose “Columnar” from the Layout Wizard window:

▪ From RepairOrder table: OrdNo, TimeRecvd, and TimeFinsh (select each one while holding down the Ctrl key)

▪ From PartsUsed table: QtyUsed (drag over to far left side)

▪ From Part table: PartDesc (since the PartsUsed table and the Part table are related, you can drag the PartDesc field just to the right of the QtyUsed field in the same section.

When you are finished with the above additions, your page should appear as Figure 44 below. You may close and save this page as “Customer Repair Report”, or keep it open and perform modifications to the page as instructed below.

[pic]

Figure 42: DAP Empty Design View Page with Field List Displayed

[pic]

Figure 43: Layout Wizard Window

[pic]

Figure 44: Individual Controls and Groups Dragged from the Field List Pane

7.2.4 Modifying the Customer Repair Report Data Access Page

A few simple modifications to this page still need to be made, so have the Customer Repair Report page open in Design view:

1. Set Group Level Properties: For each group heading, click the arrow and choose Group Level Properties. For each group level, set the DataPageSize to “1”, and the ExpandedByDefault to “True” except for the lowest, PartsUsed, level. With this modification, each level will appear in page view once for each record.

2. Add Page Title: At the top of the page, click in the title area and type the title “Customer Repair Report”. At the end of the title, hit the delete key once or twice to decrease the space between the title and first group level heading. To center the title, select it , View→Properties→Format Tab→TextAlign Property, and set it to “left”. Next, place the cursor at the beginning of the title and space the title toward the middle until it is centered over the report.

3. Add Section Captions: For each group level, starting with the first one, click the arrow next to the heading and choose Caption. The caption area appears above the heading. Click the label tool on the toolbox and drag a label control to the caption area. Type caption texts in the label controls as follows, then select it and make bold.

• For the Customer Caption: Type “Customer Information”;

• For the Vehicle Caption: Type: “Vehicle Information”;

• For the RepairOrder Caption: Type: “Vehicle Received and Finished Times”;

• For the PartsUsed Caption: Type: “Part Information”.

• Decrease and Align Caption Area: The area surrounding the captions is too large (Figure 45). Select each section caption area (handle bars appear) and point the cursor at the bottom, middle bar until a double arrow appears. At this point, hold the mouse button down and drag the caption area up to the bottom of the label to decrease the caption area space. While the bars are still showing, click the bar on the left side and when the double arrows appear, drag the caption area so it is lined-up with the section above it. Then, drag the bar on the right side so it is in-line with the section above it (Figure 47). Next, select the label and center it, along with aligning it in the center of the vertical and horizontal area of the caption space (Figure 46).

4. Align Fields to Center of Page: Using skills already acquired in previous lab chapters, select the fields in each group level section and drag to align them evenly under the caption, except for the last, PartsUsed, section (Figure 47). Also at this time, make adjustments to any fields where the label or textbox overlap, or the space between fields are uneven.

5. Change Label Text: In the Customer section, change the label saying CustNo to “Customer Number”, and add a space where needed between the other field names. In the RepairOrder section, change the three labels to: “Order Number”, “Time Received”, and “Time Finished”. In the Part section, change the two labels to: “Quantity Used”, and “Part Description” (Figure 44).

[pic]

Figure 45: Caption Area before Modification

[pic]

Figure 46: Modified Caption Area and Centered Label Control

[pic]

Figure 47: Modified Caption Areas in Design View

[pic]

Figure 48: Modified Report in Page View

6. Modifications to the Navigation Bars: The navigation bars need the text changed to correspond with the caption names above them (Figure 49). You can accomplish this change the same way as when the Part Report was modified.

• PartsUsedWiz Bar: Under the navigation bar, click the label on the light gray bar and then right-click Object Properties and select the Data tab containing the RecordSetLabel property. Delete “PartsUsedWiz” (leaving in “(0 of (…”) and replace it with “Part Information”. Next, go to the Format Tab and and change the TextAlign property to “right” so the text aligns to the right side.

• Change Text on the Remaining Navigation Bars: Following the instructions given above, change the following navigation bars as follows, (leaving in “(0 of (…”, and changing the TextAlign property to “right”): RepairOrder to “Vehicle Received and Finished Times”, Vehicle to “Vehicle Information”, and Customer to “Customer Information”. Figure 50 shows the navigation bars after the text changes.

7. Create Background: You will use the “Blends” background the same as the other DAP and other objects of the Auto Repair database: Format→Theme→Blends→OK. Figure 51 shows the completed Customer Repair Report in Page view.

[pic]

Figure 49: Navigation Bars, Text before Changes

[pic]

Figure 50: Selected Navigation Bar and Text after Changes

[pic]

Figure 51: Completed Customer Repair Report in Page View

7.2.5 Creating a Data Access Page Using the AutoPage Feature

Using the AutoPage feature to create a DAP is very simple compared to the Page Wizard and Design view. However, AutoPage only supports columnar-style pages from tables and queries that display one record at a time. Also, AutoPage has no grouping features. However, AutoPage will create an updatable page. You will use AutoPage to create a simple page for the Customer table.

1. Open the AutoPage Feature: After selecting the Pages section in the Database window, click the New button and select “AutoPage: Columnar” in the New Data Access Page window. Select Customer in response to the table question and click OK.

2. View the Customer Page: The page opens on the screen in Page view (Figure 52). The AutoPage feature created a columnar view of the Customer table similar to the Customer form you created in Chapter 4.

3. Modify the Page: Toggle to Design view and notice that although AutoPage does not have grouping capabilities, the page still appears as a grouped page, but with only one group level and one section containing fields. There is only one navigation bar that corresponds to the single heading. However, note that the field list pane still appears on the screen. This is because that while in Design view, you can continue to select and drag further fields to the page you have already created in AutoPage resulting in additional group levels as you have done previously in Design view.

• Change the Report Title: In the area indicated, type “Customer Report”.

• Change Label Text: Change the CustNo field label to “Customer Number” and then for the remainder of the fields, put spaces between the words where necessary.

• Align Fields if Necessary: If the labels are not aligned or are overlapping the textboxes, fix this also. Also, if the texboxes are not showing all of the record data in Page view, inccrease the size.

• Set the Theme: Set the theme by clicking Format ( Theme and choosing “Blends” in the Theme window. Toggle back to page view and your completed page should appear as in Figure 53.

• Close the Page: When you are finished viewing, close the page. When prompted, save it as “Customer Report”.

[pic]

Figure 52: Page View of the Customer Report Created with AutoPage

[pic]

Figure 53: Page View of the Completed Customer Report Data Access Page

7.2.6 Making a Data Access Page Updatable

Similar to a form, a DAP may be updatable if its underlying queries is updatable. The updatability of DAPs adheres to the rules for updatable queries presented in textbook Chapter 10. For a DAP with an updatable underlying query, the group level properties allow a user to set a default sort order, edit records, delete records and add records, as well as, to protect specific fields from editing. Also, the buttons on the navigation bars allow for adding records, deleting records, undoing a last editing operation, and sorting/filtering records. Also similar to a form and a report, you can add a calculated field to display a total using values in other fields in the DAP.

For the purpose of this lab, you will sort and filter records, add and delete a record, edit a record, and protect a field from editing. When making records on a DAP updatable, you need to set certain group level properties to “true” to activate the buttons on the corresponding navigation bar in Page view to accomplish the desired actions. In Page view, when you put the cursor under a navigation button, a textbox will identify the button.

Although the Part Report you have previously created has group levels, that DAP cannot be editable or updated since it already contains aggregate or other calculated fields as a result of its underlying query. Therefore, to keep things simple, you will use the Customer Report DAP that has already been created. To begin, open the Customer Report DAP in Design view:

1. Setting Group Level Properties: The first step to making a DAP editable or updatable is to set group level properties. Click the arrow next to the heading and choose Group Level Properties. The following properties should be already set to “true” (the default, but if not, set them to “true”): AllowAdditions, AllowDeletions, and AllowEdits (Figure 54). As long as these properties are set to “true”, the corresponding navigation bar buttons will be active in Page View. Consequently, if these same properties are set to “false” the navigation bar buttons will not be active, which protects fields from being edited, updated or records added to the database.

2. Using a Navigation Bar: As long as the properties discussed above are set to “true”, then the navigation bar for the Customer group will have those buttons active (Figure 55) to perform the actions below (note that you must click in the desired field to activate certain buttons on the navigation bar):

• Adding a Record: Regardless of the location of the cursor, click the New [pic] icon on the navigation bar and the fields change to blanks for the addition of new records. Add your name, address, etc. to the records fields and to become the next customer number. During the entering process, If you make a mistake and want to revert back to the original record fields, then click the Undo [pic] icon. If you are satisfied with the entered values, then click the Save [pic] icon.

• Deleting a Record: Scroll through the records to locate the record that you want to delete. For this example, it will be the record you just added above. Click the Delete [pic] icon and click “Yes” to the dialog box asking if you are sure about deleting the record since it cannot be undone. Now the record is gone!

• Editing a Record: Put the cursor in any field that you want to edit or changes. When you are finished, click the Save icon. This process does not require any buttons on the navigation bar, but the AllowEdits group level property must be set to “true”.

3. Sorting and Filtering Records: The buttons on a navigation bar for sorting and filtering are always active because there are no properties to set that would turn these features on or off.

• Sorting Records: Change the sort order for the Customer Number field from ascending to descending. This change is done by clicking on the navigation bar’s Sort Descending icon (Z to A) and watch the Customer Number field change. Now when you use the bold directional arrows on the bar to scroll through the records, they will now be in descending order. To change the sort order back to ascending, click on the Sort Ascending icon (A to Z) and the sort order reverts back.

• Filtering Records: Use the filtering feature to locate all of the customers who live in Seattle. Put the cursor in the State field textbox and click the filter icon [pic] (Filter by Selection) on the navigation bar. The navigation bar changes to indicate that there are 8 customer records (1 of 8). Use the bold directional arrows to scroll through the filtered records to see that they are the Seattle customers. When finished, click on the unfiltered icon [pic] (Filter Toggle Button-turns white) and the records become unfiltered.

[pic]

Figure 54: Group Level Editing Properties

[pic]

Figure 55: Navigation Bar with Active Buttons

7.2.7 Using Relative Links for Data Access Pages and Database Connections

This section closes with a discussion about links to DAPs and database connections used in DAPs, important issues for maintaining and publishing DAPs. DAPs are stored as files separate from the Access database file, while the other database objects are stored inside the database file. The Pages tab of the Database window contains links to DAPs, not the actual DAPs. In addition, each DAP contains a connection string that contains a link to the database used by the DAP.

If you intend to relocate your database to another location (local folder or server), you should use relative paths for links, not absolute paths. An absolute path includes the full path starting from the drive (such as C). A relative path indicates the path relative to the current object. Relative paths provide flexibility because you can move your database to another location without changing the links as long as you maintain the same relative folder structure. Assume that the root folder of your application system is “Application”, the Access database for your application is stored in the folder “Database” under “Application”, and the DAPs are stored in the folder “DAP” under “Application”. If you use relative links for the DAPs and the database connection strings, you can move your application to a new computer, folder, or server as long as you maintain the old folder structure under the new root folder.

Although Access 2003 does support relative links, you need to specify them separately after you have created a DAP. By default, Access uses absolute links to DAPs and database connections. You must override the default absolute paths that Access uses. The confusing point is that relative links must be specified in two places. If you do not provide relative paths in both places, your DAPs will fail when your database is moved.

To change the link to the DAP, you use the Page properties window for the DAP. To open this window, right-click on the DAP in the Page tab of the Database window and choose Properties from the menu. Figure 56 shows the Properties window for the Part Report DAP. The path specification, “..\DAP\Part Report.htm”, is a relative path to the file containing the DAP. The path is relative to the location of the database. The symbol “..” in a relative path indicates the parent folder. In Figure 56, the DAP file (Part Report.htm) is located in the “DAP” folder that is found in the parent folder of the database.

[pic]

Figure 56: Page Window showing a Relative Path

Besides changing the link to the DAP, you must also change the database connection string contained in the DAP. The database connection should be a relative path, not an absolute path. If you understand the DAP coding details, you can edit the DAP file directly to change the database connection string. If you do not understand the DAP coding details, you can change the database connection string using Design view for the DAP. You can change the database connection in the Data Link Properties window.

To open the Data Link Properties window inside Design view, right-click on the page title bar and select the Page Connection… command from the menu. In the Connection tab, you should change the value under the label “Select or enter a database name” as shown in Figure 57. After you change the database connection, open the Field List (View(Field List) to see that the database connection contains a relative path[2] as shown in Figure 58. The database connection string specifies the location of the database relative to the location of the DAP. In Figure 57, the database file is located in the “AutoRepAccess2003LabDatabases” folder that is found in the parent folder of the DAP file (Part Report.htm).

[pic]

Figure 57: Data Link Properties Window showing a Relative Path for the Database Name

[pic]

Figure 58: Field List showing a Relative Path for the Database Connection

Closing Thoughts

This chapter has covered two specialized database objects, pivot tables and Data Access Pages (DAPs). Pivot tables provide a convenient interface for manipulating multidimensional data, a common format for business analysis. You learned about the Access vocabulary for pivot tables and the steps in designing a pivot table as well as you gained practice using the PivotTable Wizard to create pivot tables. The practical material presented in Section 7.1 complements the conceptual material in textbook Chapter 16.

Data Access Pages are Web pages providing dynamic interaction with Access databases. You used the Page Wizard, the Design view, and the AutoPage to create DAPs. You learned to manipulate grouping levels and to update and edit fields on a page. Because of the client requirements, DAPs are suited primarily for intranets, not for use in the public Internet. If you intend to publish DAPs to an intranet, you should consult a Web developer or read details available in the Microsoft Developer Network site about deploying DAPs. There are a number of details that must be carefully considered before publishing DAPs.

This chapter concludes the study of individual database objects for application development. Chapter 8 integrates the material from other chapters in the coverage of user interface development for Access databases.

Chapter Reference

The chapter reference section summarizes procedures that you practiced. For wizards discussed in the chapter, the procedures highlight important parts of the wizards but do not list all of the steps.

Procedure 1: Creating a Pivot Table (Section 7.1.2)

|Before creating a pivot table, you should determine the dimensions and measures of the data cube that it |

|supports. |

|The underlying query for the pivot table should include all fields in the pivot table except for fields that |

|are computed in the pivot table. |

|Use the PivotTable Wizard to choose the row fields, column fields, filter fields, and detail fields from the |

|fields in the underlying query. |

|Use PivotTable view to change the properties of individual fields in the pivot table. |

|Use Design view to change the pivot table properties such as the Caption and Record Source. |

Procedure 2: Use the Page Wizard to Create a Data Access Page (Sections 7.2.1)

|Select the Pages section in the Database window. Click the New button and select “Page Wizard” in the New Data |

|Access Page window. Select the table or query in response to the table question. Click OK to continue. |

|Click the >> or > button to move desired fields shown in the left side to the right side (Selected Fields). |

|Click the Next button when finished. |

|Select and add desired grouping levels by selecting the field and using the > button. Make corrections to a |

|group option using the Grouping Options button at the lower left of the window. |

|Choose the sort order and type a title for the page. Apply a theme to the page if desired and click Finish. |

|Modify the page by slowly double-clicking labels and textboxes to change text. Single-click labels and |

|textboxes to reveal the crosshair cursor to move or the arrow cursor to resize. Select navigation bars and |

|change text in the RecordSetLabel property (in its Properties window). |

|You can delete navigation bars that will not be used by clicking on the arrow next to the heading, and then |

|close their empty sections by selecting the section head and dragging it toward the next section head. |

|When you save the page, Access will create a special Pages folder for it in the My Documents folder and for any|

|other related files. |

Procedure 3: Use the Design View Feature to Create a DAP (Section 7.2.2)

|After selecting the Pages section in the Database window, click the New button and select “Design view” in the |

|New Data Access Page window. Select a table in response to the table question and click OK. |

|The page opens on the screen in design view along with a field list pane on the left of the screen. |

|To create a page, table names or fields to the page and place them according to the desired levels as referred |

|to in the blue square. toggle to design view. In the area indicated, type a page title. |

|Set the theme by clicking Format ( Theme and choose a theme from the Theme window. Toggle back to page view to |

|see your completed page. |

|When you are finished viewing the page, close and save the page. When you save the page, Access will create a |

|special Pages folder for it in the My Documents folder and for any other related files. |

Procedure 4: Use the Auto Page Feature to Create a DAP (Section 7.2.3)

|After selecting the Pages section in the Database window, click the New button and select “AutoPage: Columnar” |

|in the New Data Access Page window. Select a table in response to the table question and click OK. |

|The page opens on the screen in page view. The AutoPage feature creates a columnar view of the table similar to|

|the forms in Chapter 4. |

|To modify a page, toggle to design view. In the area indicated, type a page title. Set the theme by clicking |

|Format ( Theme and choose a theme from the Theme window. Toggle back to page view to see your completed page. |

|You can drag addition fields to the page from the field list pane. Adding these new fields give you the option |

|to create grouped levels for data display. |

|When you are finished viewing the page, close and save the page. When you save the page, Access will create a |

|special Pages folder for it in the My Documents folder and for any other related files. |

Procedure 5: Editing Features of a DAP (Section 7.2.6)

|DAPs with updatable underlying queries can support the addition, modification, and deletion of records. The |

|sort and filter features still apply to updatable DAPs. |

|Set group level properties to “true” for the AllowAdditons, AllowDeletions, and Allow Edits properties. This |

|activates the buttons on the corresponding navigation bars on the page. |

|Click the desired field and then on the navigation bar button for the action to be performed. Otherwise for |

|adding and deleting records, just click the appropriate navigation bar button and a record may be added or |

|deleted. |

Additional Practice

The following problems provide additional practice with the extended auto repair database as well as the textbook databases.

Part 1: Pivot Tables for the Extended Auto Repair Database

1. Create a pivot table to support decision making about the labor costs of repairs. Your pivot table should support a data cube with dimensions of make, model, labor code, and starting repair date and measures for the actual labor cost, the standard labor cost, and the labor cost difference (actual labor cost minus standard labor costs). You need to write the underlying query and then create the pivot table.

2. Create a pivot table to support decision making about part expense for vehicles. Your pivot table should support a data cube with dimensions of make, model, cylinders, starting repair date and measures for the total part expense and the number of repairs. You need to write the underlying query and then create the pivot table. The pivot table should also have the vehicle state as a filtering field.

Part 2: Data Access Pages for the Extended Auto Repair Database

1. Create a read-only DAP for the Labor Usage Report presented in Problem (1) of Chapter 6. You should create the DAP in two ways: (1) using Page Design view as described in Section 7.3 and (2) using the File(Save As… command (choose Data Access Page as the type) on the report that you previously created. After creating the DAPs, experiment with them to see which creation method produces the result that you prefer.

2. Create an updatable DAP to display customer data along with the related vehicle data. Make the page look like a hierarchical form in which one record displays in the main form and the related records display in the subform. You should create the DAP in Page Design view as described in Section 7.3. Note that you cannot convert the existing Customer form using the File(Save As… command because the conversion does not support hierarchical forms.

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

[1] The difference between the detail area and the data area is not explained in the Access help documentation. From my experimentation, it appears that the data area should be used for summary measures and the detail area for row measures.

[2] For some reason, the Data Link Properties window reverts to an absolute path after you specify a relative path. The Field List displays the relative path, however. This behavior seems to be a bug in Access 2002.

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

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

Google Online Preview   Download