Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


MS Access Report - Subreports

MS Access Subreports in Reports

This article details the steps to creating an Access report using subreports. The details include linking the main report to the subreport, formatting the main and subreport, and presentation of the detail data for each item in the subreport. The data used is from the AdventureWorks database using linked tables from SQL Server 2012 in MS Access 2013. Note: Datatypes have changed in MS Access 2013 from MS Access 2010 - Text is now Short Text, Memo is now Long Text, etc.

Recordset Data From AdventureWorks Sql Server 2012

The main MS Access report will use data from a linked table called Purchasing.PurchaseOrderHeader in SQL Server 2012. THe data has high level info about the purchase order such as order dates, shipping dates, totals, etc. I want the report to also show the Purchasing.PurchaseOrderDetail data next to the Header data. The relationship between the 2 tables is one to many. By using a subreport, I can display each order header information with the multirow data in a single detail row in the report. This first image is of the OrderHeader table.

PurchaseOrderHeader Table

I am using a query for the PurchaseOrderDetail data to join with the table Production.Product to get the product name for my report. It is more descriptive to have the ProductName in addition to the displayed quantities, prices, and line totals.

PurchaseOrderDetail in Query

Setting Up the Main Report

By right clicking the properties for the report, I can set the Main Report to use the Purchasing.PurchaseOrderHeader table for its RecordSource.

Report Properties Set RecordSource

I add the fields from the table to the report to display the Subtotal, Status, Total Due, OrderDate, etc..

DesignView of Report

I set a formula using an IIF statement for the Status field which is a Number (Integer) that will not mean very much by itself. I display what each of the numerical values represent as the Status of the Purchase Order: Pending, Approved, Rejected, or Complete.

Function to set Status

Running the report shows the data for the Header, but we now need to add the PurchaseOrderDetails to the report using a Subform to provide all of the information.

Print Preview without Details

Creating and Setting Up the Subreport

I create a 2nd report using the Query of Products and ProductPurchaseOrderDetail data as the RecordSource. I put the information in a single row in the Detail section of the report.

Subreport DesignView

I add a Subreport object in the Main Report and select Use an Existing report of form and select my new subreport that I just created.

Subreport Setup

In the next screen I select the field to join the Main Report with the Subreport data using the Key PurchaseOrderID.

Subreport Linking

I click to Finish

Subreport Finalize

I add some Headers outside of the Subreport and make the Border style Transparent for the Subreport.

Subreport Formatting

I can then run the report and display the multiple rows of Line Items with a single Subtotal, Tax Amt, Freight, and Total Due.

Final Access Report Product Using Subreport