CIS214: Access — Week 9: Reports

Instructor: Charles Ott

Lecture Outline

Text: Reports Chapter 4, Exports Chapter 8.

1. Parameter Queries
2. Report Design.
3. Mailing Labels
4. HTML and XML Exports

We will use these tools this week:

Parameter Queries

A parameter query is a select query that asks the user to enter the selection criteria when the query is run. We have put it off until now because parameter queries are especially useful for reports.

Here's a simple query that selects Customers from the state of Indiana.

To change this to a parameter query, just replace the criterion with a question in square brackets as shown. When this query is run, a dialog box pops up and asks the user "Please enter the state:". This query can then be used to select Customers from any state.

We will use parameter queries as the basis for reports that can be run for particular individuals.

Reports

Reports are designed to be printed or to be viewed on screen. The important thing for a report is that it presents information in a way that is easy to understand. It's not helpful to say that the information you need is "on there somewhere". A well-designed report makes the information you want obvious.

Reports use a banded design, which means that they are divided up in a series of horizontal bands with these names:

1. Report Header: The title of the whole report. This appears once, on the first page.
2. Page Header: A band which appears at the top of each page. Usually, the page header holds the column headings.
3. Group Header: A band that identifies each group, and appears once per group. Examples: for a list of books by author, the group header would be the author's name. For a list of sales by sales rep, the group header would be the sales rep's name. For a list of sales by week, the header would identify the week.
4. Group Detail: The actual data under each group header.
5. Group Footer: An optional band at the end of each group that may be used to count or summarize the data.
6. Report Footer: A band at the bottom of the page, which usually includes the name of the report and the page number.

Controls in a Report

The various elements that make up a report are called controls and are similar to controls in a form. Once you open a report in Design view, they can be moved and resized using the same methods you have seen.

Reports Based on a Parameter Query

Use a parameter query when you want a report on just one subject. For example, this report (based on database for a publisher) lists all the books for one author.

Create a parameter query like the one shown above. When it runs, it will prompt the user with a dialog box as shown. Once the user enters a last name, the report will include only books by that author.

Mailing Labels

To create a mailing label report, start by clicking the Mailing Labels button in the Create ribbon, which opens a dialog like this:

Mailing label reports are based on precut, sticky-back label sheets produced by various companies. These can be printed (one sheet at a time) in laser or ink-jet printers. In real life, you must start with the kind of labels you have (or go to the store and buy some), then follow the prompts in the Label Wizard to set up labels for that brand and number. The best-known brand of labels is Avery, which are identified by a number. Many other manufacturers make labels, and they often reference the Avery number for labels that are compatible in size and arrangement to the Avery models.

Exporting Your Data to HTML, XML and Other Formats

Access keeps its data in its own format, which is not a problem until you need to share that data with other people in electronic form. We have already imported data in CSV format, but you will be called upon to export your data. This often happens when programmers in your company need to use your Access data in other databases, or when your data needs to be published on the Internet.

The External Data ribbon is used to export or import data. Here's a partial view of the export menu:

"HTML" stands for "Hypertext Markup Language" and is the underlying code used to create all Web pages. An Internet browser, such as Microsoft Internet Explorer, can read HTML code and generate a page.

XML is "Extendable Markup Language" and is used for a number of purposes, but the most common purpose is to transfer information between computers and programs. Many programs can read XML data, so if you need to transfer information out of Access and into another database (or any other type of program), you may be asked to put it in XML format. Browsers can also read XML files.

XML pages usually start as queries. Create a query that delivers the information you need to export, then use the "XML File" menu as shown. IMPORTANT: The file must be saved with the extension ".xml". XML files can be opened in a browser (Internet Explorer, Firefox or others) just like web pages.

HTML pages usually start as reports. Create the report you want, then choose it and click "HTML Page". Save the page with the extension ".html". When this file is put on a web server, it will appear on the World Wide Web.

Week 9 Homework

Turn in four printed pages:

1. Create a parameter query based on your existing Plumb Bob query. The query will need to include at least the Customer.LastName, SaleOrder.SaleDate and Product.Name fields. Then generate a report which will show the products purchased by one customer at a time, sorted by purchase date. Print this report.
2. Export this report as an HTML page called "Week9.html". Open this page in Internet Explorer or another browser, not in Access, and print only the first page from the browser.
3. Export the query as an XML file called "Week9.xml", open it in a browser and print the first page only.
4. Use the Plumb Bob Customer table to create a mailing label report in Avery 5160 format. Print one page of this report.