Microsoft Most Valued Professional MVP - Access Expert

mvp.png

  Boyd Trimmell, aka HiTech Coach
  Microsoft MVP - Access Expert


What is a Microsoft  Most Valuable Professional (MVP)?
Microsoft  awards the Most Valuable Professional (MVP) in recognition of significant contributions made to technical communities by sharing high quality, real world expertise. Microsoft Most Valuable Professionals (MVPs) are a worldwide network of exceptional technical community leaders.

"If technology doesn't work for people, then it doesn't work."
- Kim Vicente

Sub Report Tips and Tricks

Sub reports are great for showing data from a related table. This works just like a sub form, by setting the master/child linking fields.

What else can you use a sub report to print?

Other data that is not related to a specfic reord in the parent report. The trick is to not set the master/child linking fields. This allows you to print information in the report header or footer that is not "link" to a record.

Sub reports are great for sunmmizing the data in a report.

By basing an sub report's recprd source on the same record source as the parent report, you can show all the totals for each grouping (hiding the detail). You can also create a  totaling query based on the parent report's record source (query)


What if you want to place the report's total in the report header?

Use a sub report that cauclates and displays just the total.   

How do you print several different statistics that are caclulated with different queries?

Use sub reports. It is possible to have a parent report that has no record source, only sub reports.

How to print a header on each page when a sub report spans multiple pages?

Becuse page headers do not print from a sub report you have to use group header. This is done by creating a grouping that will group all record into a single group. You then can set teh group header to repeat.

1. Open the subreport in design view.
2. Open the Sorting and Grouping box.
3. In the Field column of the dialog, enter an expression that won't change, e.g.: =0 and set Group Header to Yes.
4. Right-click the new group header (grey bar on the report), and choose Properties. Set Repeat Section to Yes.

This gives you a group header that appears at the top of the subreport. It will repeat on each new page like the way you would excect the page header for the subreport.

Report Headers and Footers

Working with Report Header and Footer sections.

Print order for4 sections:

  • Pager Header - Prints at the top of each page.  This section can NOT be set to grow or shrink. The Page Header prints before the Report Header/Footer. It is possible to turn off the Page Header so it does not print with the Report Header/Footer
  • Report Header - prints once at the beginning. This section can be set to grow or shrink.
  • Group Headers - prints at the begining of each group This section can be set to grow or shrink. It can also be set to Repeat on each new page.
  • Group Footers - prints at the end of each group. This section can be set to grow or shrink.
  • Report Footer - prints once at the end. This section can be set to grow or shrink.
  • Page Footer - prints at the bottom of each page. This section can NOT be set to grow or shrink. The Page Feader prints after the Report Header/Footer. It is possible to turn off the Page Footer so it does not print with the Report Header/Footer

When a report is used as a Sub Report, the header and footer sections work a little different.

Print order for sections:

  • Pager Header - does NOT print. The parent report is responsible for the page header
  • Report Header - prints once at the beginning. This section can be set to grow or shrink.
  • Group Headers - prints at the begining of each group This section can grow or shrink. It can also be set to Repeat on each new page.
  • Group Footers - prints at the end of each group. This section can be set to grow or shrink.
  • Report Footer - pirnts once at the end. This section can be set to grow or shrink.
  • Page Footer - does NOT print. The Parent report is responsible for the page footer.

How to create something lke a  a Page Header in a Sub Report

You can create a dummy header .grouping by setting the group on to  1 (enter the digit 1). You can then set the section to repeat. Now you have a  group header that will print on every page.

Header and Footer Tips

Repeating Group Headers:

In the Group Header's prperties, set the Repeat property to Yes to get the header to print on every page if the group spans multiple pages.

Print Report Header without a Page Header/Footer:

In the Report's properties, set the Page Header property to Not With Rpt Hdr or Not With Rpt Hdr/Ftr depending on your needs.

Print Report Footer without a Page Footer/Footer:

In the Report's properties, set the Page Footer property to Not With Rpt Ftr or Not With Rpt Hdr/Ftr depending on your needs.