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.
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.
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.
I add the fields from the table to the report to display the Subtotal, Status, Total Due, OrderDate, etc..
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.
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.
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.
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.
In the next screen I select the field to join the Main Report with the Subreport data using the Key PurchaseOrderID.
I click to Finish
I add some Headers outside of the Subreport and make the Border style Transparent for the Subreport.
I can then run the report and display the multiple rows of Line Items with a single Subtotal, Tax Amt, Freight, and Total Due.
