Center for Academic Computing:



Alternative Format Statement

This publication is available in alternative media upon request.

Statement of Non-Discrimination

The Pennsylvania State University is committed to the policy that all persons shall have equal access to programs, facilities, admission, and employment without regard to personal characteristics not related to ability, performance, or qualifications as determined by University policy or by state or federal authorities. The Pennsylvania State University does not discriminate against any person because of age, ancestry, color, disability or handicap, national origin, race, religious creed, sex, sexual orientation, or veteran status. Direct all inquires regarding the nondiscrimination policy to the Affirmative Action Director, The Pennsylvania State University, 328 Boucke Building, University Park, PA 16802-5901; tel. (814) 865-4700; TDD (814)863-1150.

Table of Contents

Objectives 2

Adding a Report Header 3

Adding a Report Footer 5

Conditional Formatting 9

The KeepTogether Property 10

Adding Group Footers 11

Forcing a New Page 13

The HideDuplicates Property 14

DateDiff Function 16

IIF Function 18

Working with Subreports 19

Embedding Subreports 19

Viewing and Troubleshooting Report Results 21

Independent Practice Activity 21

Additional Resources 22

Objectives

• Create customized headers and footers

• Set properties to group data and modify a report’s appearance

• Use functions to add calculated values in a report

• Apply conditional formatting

• Embed a subreport in a main report

• View and troubleshoot report results

Adding a Report Header

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

Adding a Report Footer

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

Conditional Formatting

You can draw attention to specific data in a report by using conditional formatting. This feature applies formatting to the value of a field only if specified criterion is met.

[pic]

[pic]

[pic]

[pic]

The KeepTogether Property

The KeepTogether property ensures that a complete section of a report is always printed on one page.

[pic]

[pic]

[pic]

Adding Group Footers

Group footers are used to group a report based on a given field. You can add information such as totals or group names in the Group footer section.

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

Forcing a New Page

To print each set of related data on a separate page, you can use the ForceNewPage property. This property can be used in all sections of a report EXCEPT the page header and footer sections. You can insert pages before a section, after a section, or both.

[pic]

[pic]

[pic]

The HideDuplicates Property

To display only the unique values in each field of a report, you can use the HideDuplicates property.

[pic]

[pic]

[pic]

[pic]

DateDiff Function

The DateDiff function is used to calculate the difference between two dates.

DateDiff (“interval”, [date1],[date2]) is the syntax for the function.

“Interval” refers to whether the calculation is the difference between days (d), months (m) or years (y).

[date1] and [date2] refer to the beginning and end dates used in the calculation.

Both fields must have Date/Time data types in order for this function to work properly.

.

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

IIF Function

The IIF function is used to evaluate a condition. If a condition is true, the IIF function returns one value. If it is false, it returns a different value.

IIF (condition, value_if_true, value_if_false) is the syntax for the function.

Condition means the condition you want the function to evaluate.

Value_if_true is the value to be returned if the condition is true.

Value_if_false is the value to be returned if the condition is not true.

[pic]

[pic]

[pic]

Working with Subreports

A subreport can be used to display data from two different reports. A subreport is embedded in a main report. Generally, the main report and subreport share a common field by which they are linked.

Embedding a Subreport

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

View the subreport results.

Viewing and Troubleshooting Report Results

If you have a blank page after every page that contains data, it could be that the left and right margins are greater than the paper size specified in the Page Setup dialog box. Go to File > Page Setup and click the Margins tab. Adjust the left and right margin values, then click OK.

Independent Practice Activity

Open Advanced_reports.

Display the total quantity sold for each product line in the Transaction_details report by adding a group footer based on Product_ID. (Hint: Use the Qty_sold field in the expression.)

Display the data in a different format whenever the Qty_sold is greater than 200 by adding conditional formatting to the Qty_sold field.

Update the report.

Switch to Print Preview.

Close the report.

In the Transaction_dates report, add a text box control to display the difference between Order_date and Required_date.

Update the report.

Switch to Print Preview to view the report.

Close the report.

Close the database.

Additional Resources

MS Access 2002 Help

Help menu (Contents, Answer Wizard, Index tabs)

F1

Ask a Question box

Office Assistant

Books

Special Edition Using Microsoft Access 2002 by Roger Jennings

The Complete Reference: Access 2002 by Virginia Andersen

Mastering Access 2002 by Alan Simpson and Celeste Robinson

Teach Yourself Microsoft Access 2002 Visually by Ruth Maran

Instant Access Databases by Greg Buczek

Microsoft Access 2002 Bible by Cary N. Prague and Michael R. Irwin

Sams Teach Yourself Microsoft Access 2002 in 10 Minutes by Faithe Wempen

Microsoft Access 2002: At A Glance by Perspection

Microsoft Access 2002: Step By Step by Catapult, Inc.

Running Microsoft Access 2002 by John Viescas

Microsoft Pocket Guide to Microsoft Access 2002 by Stephen L. Nelson

Websites























Phone Numbers

Help Desks (215 Computer Bldg 863-2494 and 2 Willard Bldg 863-1035)

Seminar Line 863-9522

[pic]

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

ITS@PennState

224B Computer Building

University Park, PA 16802

seminars@psu.edu



Information Technology Services

MS Access: Using Advanced Report Features

Go to the View menu and click on Report Header/Footer to turn on the header and footer sections.

Go to the Reports database object button, then open Customers_by_city in Design view.

Expand the Report Header section by holding down your mouse and dragging downwards. Click on the Image Control Wizard button in the toolbox and draw a box in the Report Header section.

Once the box is drawn in the Report Header section, an Insert Picture window opens so that you can navigate to the image file you wish to import. Click on the image file to highlight it, then click Ok.

It should look like this when you are finished.

Now, select the Label Control Wizard from the toolbox and add a label to the Report Header. Click and hold down your left mouse button while you draw a box inside the Report Header. Type, “Customers by city” inside the label.

Your Report Header should now look like this in Design view. Click on the Print Preview button [pic]

and observe.

Observe the changes you’ve made.

Click on the Reports database object button. Select the Orders report and click on the Design action button to open the report in Design view.

Expand the Report Footer section by clicking and holding down your left mouse button on the bottom of that section and dragging downwards.

Click on the Text Box Control Wizard in the toolbox and drag a box in the Report Footer section.

The Report Footer should now look like this.

Right-click on the Label control and choose Properties from the drop-down menu.

Click on the All tab. In the Caption window, type, “Total amount.”

Right-click on the Text Control and choose Properties from the drop-down menu.

Click on the All tab. Click inside the Control Source window, then click on the ellipses (…) to the far right of the Control Source window. This will activate the Expression Builder.

In the upper window of the Expression Builder, type:

=Sum([Amount])

then click the Ok button.

Observe the formula in the Control Source window.

Now click inside the Format window and choose Currency from the drop-down menu.

Finally, click inside the Decimal Places window and select 2 from the drop-down menu. Close the Property window by clicking on the red X.

Switch to Print Preview and observe your changes.

Switch back to Design view. Select the Amount text control.

Go to the Format menu and select Conditional Formatting from the list.

Under Condition 1, select Field Value Is from the drop-down menu.

In the window to the right of Field Value Is, select greater than from the drop-down menu.

In the far right window under Condition 1, type 400.

Now, click on the Bold button and choose yellow as a background color.

Change the font color, then click Ok.

Switch to Print Preview and see the conditional formatting results.

Verify that the Reports database object button is selected. Choose the Order-details report, then click on the Print Preview action button.

You may have to zoom in to see the details of the report.

Right-click on the Detail section and choose Properties from the drop-down menu.

Select the All tab. Click inside the Keep Together window. From the drop-down menu, choose Yes.

Close the Property window by clicking on the red X.

Switch to Print Preview and navigate through the report pages. Observe the results.

Open the Order report in Design view.

Go to the View menu and select Sorting and Grouping.

In the Sorting and Grouping dialogue box, verify that Product_name is selected.

From the Group Footer list, select Yes.

In the Keep Together box, verify that Whole Group is selected.

Close the dialogue box.

Add a text box control to the Product_name Footer section.

Right-click on the Label control and choose Properties.

Select the All tab.

In the Caption window, type, “Total Sales.”

Close the Properties window by clicking on the red X.

Right-click on the Text Box control and choose Properties. Select the All tab.

In the Control Source window, type:

=Sum([Amount])

In the Format window, select Currency from the drop-down menu. In the Decimal Places window, select 2 from the drop-down menu. Close the Properties window by clicking on the red X.

Switch to Print Preview and see the Group Footer results. Products are grouped by Product Name and Total Sales is calculated under each Product grouping.

Open the Order_details report in Print Preview.

Observe that there are many orders listed on each page and they run together so that it is difficult to distinguish when one order ends and the next one begins.

Switch to Design view.

Right-click on the Detail section of the report and choose Properties from the menu.

Select the All tab.

In the Force New Page window, choose After Section from the drop-down menu.

Close the Property window by clicking on the red X.

Switch to Print Preview and observe the results.

Now, each order is listed on a separate page.

Open the Orders report in Print Preview. Notice how the Product Name is repeated multiple times in each grouping.

Switch to Design view.

In the Detail section of the report, right-click on the Product_name text box control and select Properties from the menu.

Select the All tab.

In the Hide Duplicates window, choose Yes from the drop-down menu.

Close the Property window by clicking on the red X.

Switch to Print Preview.

Observe the results. Each Product Name is listed only once. The duplicates are now hidden.

Open the Order_details report in Design view.

Expand the Detail section by dragging the bottom of the section downwards.

Next, add a Text Box control to the Detail section.

Right-click on the text box label and choose Properties. Select the All tab. In the Caption window, type, “Difference:” Close the Property window.

Now, right-click on the Text Box control and choose Properties.

Select the All tab.

Change the Name to read Diff.

In the Control Source window, type:

=DateDiff (“d”, [Order_date], [Shipped_date])

Close the Property window.

Now, select the Label control and while holding down the Shift key, also select the Text Box control.

With both controls selected, right-click and select Properties.

Select the All tab.

From the Border Style list, choose Solid.

Close the Property window by clicking on the red X.

Switch to Print Preview and view the results. The difference between the Order date and the Shipped date has been calculated in the new text box.

Switch back to Design view and expand the Detail section.

Add another Text Box control to the Detail section.

Delete the Label control that was created with the Text Box.

Right-click on the Text control and select Properties.

Select the All tab.

In the Control Source window, type:

=IIF([Diff]>3,”Late”,”On time”)

In the Border Style window, choose Solid from the drop-down menu.

Close the Property window.

Switch to Print Preview. Observe the results.

Open the Suppliers report in Design view, then extend the Detail section.

Click the Subform/Subreport Control Wizard in the toolbox, then click your cursor inside the Detail section.

This will activate the Subform/Subreport Wizard. Select “Use an existing report or form.”

Next, select Products from the list.

Click Next.

Select the first option to Choose from a list.

Then, select Show Products for each record in Suppliers using Supplier_ID.

Click Next.

Name the subreport Products and click Finish. Switch to Print Preview.

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

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

Google Online Preview   Download