How to Create Report with a Link to Another Report

Report links to another Report

The purpose for this How To is to create a summary report that has a linked field to open a detail report of that summary field. Example below will show a report summary of total sale for each month. There is a link on the total field that will open all list of sale for that month. This How To will provide step by step how to create these two reports.

Creating Queries

Step #1 We need to create a query that make a summary of sale for each month. We need information from the order date and total amount from a table tblAllTransaction. We also need to make this query as a Select query which is group a total same by the month order.

• Crate new query and insert table tblAllTransaction
• Create new field called “MonthYear” and format the Order Date as “mmm-yyyy” (Jan-2015)
• Create new field called “MonthSort” and format the Order Date as “mm-yyyy” (01-2015). This field will be used to sort the month in report rather than sorted by the month alphabet.
• Select “Group By” for new fields: MonthYear and MonthSort on the line Total
• Insert a field “Total” from the table and select “Sum” on line Total
• Save query as “TotalOrderByMonth.” This query will be used for the summary report by month, see picture below:

View of this query for a total of order each month below. You can see it is sorted by the MonthSort field, not the MonthYear field.

Step #2 We need to create a second query to list all orders on a report that link from the summary report. We also need to create a new field “MonthYear” on this query in order to link to the summary report by Month and Year.

View of this query for all orders below.

Creating Reports

Step #1 Create a summary report for the total order by month by steps below:

• Click on query “TotalOrderbyMonth”
• Click on the Auto Report icon to generate a report

• Right hand click on the report name on left top corner of report
• Select a Design View
• Click on field “SumOfTotal” under the Detail section
• Double click field to open the Property Sheet
• Under On Click event, click on three dots (…) and choose Macro Builder

• Set TempVar “tempMonth” for field MonthYear
• Add Open Report macro to open Alltransaction report
• Set Where Condition = [MonthYear] = Tempvars!tempMonth

• Under the Format tab of Property Sheet, change “Is Hyperlink” to “Yes”

• View Total Order by Month report. The total number of all orders for each month will display as a hyperlink blue with under line. After clicking on the link it will open a report “AllTransaction”
• If the report is opened from a click button or link, make it open view as Report. View report mode will not display a Print Preview button. We can add a separate Print button to print this report. Set a Property format of “Display When” to Screen Only. By this way, a Print button will not print on the paper.
• Even though we sort data by MonthSort field in query, it will not sort it in a report. We have to sort it again on report at the bottom by clicking “Add a Sort” then select field “MonthSort” as shown on picture below

• If click on the total $48.25 for Apr-2015, it will open the report for all orders in April 2015 shown below

Step #2 Create a detail report for all transaction and set record source to query “AllTransaction”. Repeat the same step as creating a total order by month report.

View All Transaction report. Also put the Print button on this report.

It dilates blood vessels and slows the heart to reduce blood pressure and the pain soft tab cialis of angina. Last longer erection time maintained erects with Penegra single dosages up to 3 uk tadalafil to 6 hours. There are certain symptoms that can help in boosting the testosterone levels in males and regain cialis discount generic natural youthfulness to enjoy intimate moments with her. Just log in to the viagra in india site, fill up a form through online.