Excluding parent report data/tuple if subreport has no data.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daz - 11 Mar 2007 15:17 GMT

Hi, I have a question that bears a resemblance to this previous thread

http://groups.google.com.au/group/microsoft.public.access.reports/browse_thread/
thread/9212dff2c013d9fe/7297047fa63029ec?lnk=st&q=ms+access+check+subreport+data
+set&rnum=2&hl=en#7297047fa63029ec

My scenario is this:

I have a report which lists a series of items in catergories, each of
these items can have several subreports which are bound to some strict
data constraints (date filters, $ value filters - also the subreports
data sources are not all the same). For instance, the setup is like
this

Category 1
----> Subreport 1
----->Subreport 2
----->Subreport 3
----->Subreport 4
Category 3
----> Subreport 1
----->Subreport 2
----->Subreport 3
----->Subreport 4

.. and so on throughout numerous categories.

What I want to do is hide, or make the category disappear from the
resultant report, if there is no data in the ANY of the subreports
under that category. ie. if the category has no useful subreport data
then we want to ignore it.

Been racking my brains for a few hours on how to do this in a simple
way. Any suggestions?

Regards
Darragh

Reply to this Message

Duane Hookom - 11 Mar 2007 19:04 GMT

Subreports are almost always linked by child/master properties. You should be
able to create totals queries similar to your subreport record sources that
Group By the linking field and count the number of returned records that
might show in your subreport. Add these totals queries to the record source
of your main report. This will allow your main report to understand the
number of records (or lack of) that would display in your subreports. Use
this to filter your main report.

Signature

Duane Hookom
Microsoft Access MVP

> Hi, I have a question that bears a resemblance to this previous thread
>
[quoted text clipped - 31 lines]
> Regards
> Darragh

Reply to this Message

Daz - 13 Mar 2007 05:55 GMT

Duane

Thanks for your response.

I utilised a variation of what you suggest. For those interested I did
the following.

Created count queries for the recordsources of the subreport.

Gathered the number of instances a parent property occured in the
count of the subreport recordsource. If count greater than 0, then
include that parent property constraint in the parent reports
recordsource.

A poor explanation I know, I will endevour to write up a better one
and post to the access section of my personal blog - http://www.darraghmurray.com

Regards
Darragh

On Mar 12, 4:04 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:

> Subreports are almost always linked by child/master properties. You should be
> able to create totals queries similar to your subreport record sources that
[quoted text clipped - 42 lines]
> > Regards
> > Darragh

Reply to this Message