Categories
Access database office pl/sql reporting sub-reporting

Access reporting using sub-reports

Access isn’t well regarded for its reporting. In my experience a lot of users use Excel as the Access reporting tool. But there is one category where Access excels above Excel. (nice pun eh?). That is sub reporting. This is where two separate pieces of information must be brought together on a Access report.

An example is the personnel assignment report below:

In this report there are three different pieces of information brought together.

  1. Individual basic details
  2. The professional qualifications
  3. Individual assignment history

This normally cannot be done in Access. So how does on accomplish this. With sub reports.

Now look at the same report in design view:

Its a bit messy – working with sub-reports always is. But here you can see that for each person in the detail section are two sub-reports. One is their profile, skills, wage number, education. And second their work assignment history. These sub-reports are always stored as separate reports within Access.

This does help make it easier to layout the reports.

Lets take a look at how the sub-reports are linked to the main report. There must be a unique way to join the two. otherwise the sub-reports will display either no data or all data. We want the sub-report to only show the related data. Lets take a look at the assignment history sub report.

To do this you must have the subreport item selected. Don’t click on the top left as that it is the report. When you’ve got it right you’ll see small resize handles around the sides of the subreport.

In this case you can see the sub-report field resourceID it linked with the main report field ID. This ensures you are reporting on the right data.

And you can carry this forward with sub-reports containing sub-reports. I’m not sure how far it goes as I’ve only gone to the sub-sub-report level myself.

This is just a sample of the project that my colleagues and I at chameeya take on on a regular basis.