How to Include Subqueries in an MS Access Report

By Darrin Koltow

Subqueries are SQL (structured query language) statements that database users nest within other SQL statements. Subqueries enable users to produce data for a report, often from at least one table that's different from the main query's table. For example, a query listing all store products that have reached their weekly sales quota may be written like this:

SELECT [Product], [SalesQuota]

FROM [ProductsTable]

WHERE [SalesQuota] < ALL

(SELECT [Sales] FROM [SalesAssociates]

WHERE [SalesAssociates].[Product]=[ProductsTable].[Product]);

Microsoft Access, via its subreports features, offers at least two methods for adding subqueries to reports. With subqueries, Access users can produce reports whose detailed data clarify the data in the main reports.

Open an Access database with at least one existing subquery and table.

Click the subquery in the "All Tables" pane at screen left, then click the "Report" icon on the Ribbon (Access' large, main toolbar at the top of the screen). This action will create a new report that uses the data from the subquery as its main source.

Drag a table icon from the "All Tables" pane anywhere onto the report. The report now includes data from a subquery and another data source, the table you just dragged onto the report. View the report by right-clicking its tab and selecting "Report View." The remaining steps will show you another approach for creating a report with a subquery.

Click the table that you want to base a new report on, then click the "Create" menu's "Report Wizard" icon.

Click the fields you want to include in the main report on the first screen of the wizard, then click "Next."

Click which field you want to organize your report's data under. For example, if your table is a list of students, you may choose to organize them by their GPA.

Select the field on which you want the report to sort the data on the "sort" screen of the wizard. Click "Next" to move to the layout portion of the wizard. After selecting the layout, select a style for the report (e.g. "Apex," "Northwind"), then click "Next."

Click "Modify the report's design" on the wizard's last screen, then click "Finish." This step completes the initial report, which you'll add a subquery to next.

Click the "Design" menu heading, then click the "Controls" panel's "Use Control Wizards" icon, if it's not already selected.

Click the "Subform" icon in the "Controls" panel, then click the place on your report (e.g. "Report Header," "Page Header," "Detail") where you want to display the subreport of the subquery. Access will open the SubReport Wizard for you to specify how the subquery's data will integrate with the report.

Click "Use existing Tables and Queries," then click the subquery from the "Tables/Queries" drop-down list on the next screen. Select the fields from the subquery you want to include in the report, then click "Finish."

Open the completed report with the subquery by right-clicking the report's tab and selecting "Report View."