MS Access: Adding Parameter Queries to a Report

The easiest way to add a query to a report is to:

• Design a query that restricts the type or category of information you want the report to display. Test the query and save it. The query is now in your database object inventory. (Note: You may want to use an existing query you designed for a form. See the Bright Hub Article “Adding Parameter Queries to a Form.”)

• Reopen the query in the datasheet view and automatically generate a report based on the query, using the following steps:
  • Click on the “Create” tab. Select “Report” from the Reports section. The report will open automatically.
  • Click on the design view icon to edit the report design. (See the screenshot on the right.)

♦ Use a select query to display the report showing the same restricted category of information each time it opens.

Advantage: Reports will be consistent and automatic.

Disadvantage: Using select queries exclusively tends to fill up the database object inventories with multiple queries and clutter up the database.

♦ Use a parameter query to display a dialog box that will prompt the user to enter a desired category of information for the report to display.

Advantage: Parameter queries remain static as database grows. Dialog-box prompts are easier to deal with than trying to remember which select queries were programmed. Parameter queries also keep down the number of queries accumulated in the database.

Disadvantage: Parameter queries an become somewhat intrusive and annoying when viewing and printing multiple reports. Also, typographical errors in the dialog box will result in empty or failed reports.

The screenshots below illustrate the effect and outcome of applying a parameter query to a report:

About.com:Databases: “Creating a Simple Query in Microsoft Access”

Microsoft Office Online: “Use a query as the record source for a form or report”

Bright Hub Article: "The Economy of Parameter Queries"