Nested Sub-Report Query

[Spenney]

Background...

I have to work with some data regarding company 'controllers' Eg Company A owns Company B who in turn owns Company C etc.

I have a request to show for a given company(A) all of the companies(B) they control and any company controlled by a company that controls company(A).

For example ABC Ltd controls XXX Ltd and YYY Ltd. ABC Ltd are jointly controlled by DDD Ltd and EEE Ltd. As well as controlling ABC Ltd, DDD Ltd also controls ZZZ Ltd. EEE Ltd do not control any other entity.

So I have a report that shows ABC Ltd with a sub-report that shows the controlled entities XXX and YYY.

Another sub-report shows the contollers DDD and EEE. Within this second sub-report is a further sub-report to show that ZZZ is contolled by DDD.

This works satisfactorily with one exception - in this example EEE do not control any other entity so I do not want to show them as a controller of ABC, but I'm struggling with how to hide this record on the basis of it's sub-report having no data.

Any ideas ?

Any help greatly appreciated.

 

[lynchg]

The query that populates the subreport with DDD and EEE on it must be based on a relationship between ABC and both DDD and EEE. Somewhere you must have something like a parentID field, probably in a table that holds the many-to-many relationship between parent companies and their children. You can add to your criteria the need for the parent to have more than one child.

Something like:

Having Count(XXXX) > 1

It may be hard to see what I mean but without knowing how your data is structured it is hard to know exactly how you would need to structure your query.

 

[Spenney]

Lynchg...

Thanks for the reply. I do see what you mean. I'll take a look and see if it suits my situation.