I am building a little database to track companies we do business with – sort of a contact manager, but with more stuff in it (evaluation of proposals, reminder dates for contracts, etc).
Any given company can submit any number of proposals to us – so I have a table of companies with tombstone data, and a table of proposals that includes an “OrgID” field. Not all proposals will result in a contract – so I have a contract table that has a link back to the proposal through a “Proposal#” field. Both of those links work fine.
In our industry it is common to move proposals from a parent to a special purpose subsidiary to limit liability. So our proposal with XCo will result in a contract with YCo. I have put in a “stakeholders” table to track the actual ownership of a contract, as well as other stakeholders, like lenders, engineers, equipment suppliers and the like. I also have a table of affiliated companies that shows a parent company orgID and a subsidiary OrgID (there can be a many:many relationship between parents and subs – although that is unusual; most common is a one:many).
The result is that when our main “company form” is displayed it has a subform showing all the proposals originally presented to us by that company (including contract information if the proposal has gone that far) as well as any contracts held either by that company or by a subsidiary.
Whew – that was a long time to describe what is going right 
The problem is that there are sometimes more than one parent-sub link in the chain: CoX can own CoY, which owns CoZ, which owns….
Ideally, I would like to be able to bring up CoX on the main company screen and have the subform display the projects associated with CoY (it does this now) and CoZ (it doesn’t). At the moment, it does this by running a “union distinct” query to return the parent company OrgID as well as all the first-level subsidiary OrgIDs (the CoY, CoY1, CoY2… IDs). I then join the output from that query to the proposals table, to get all the proposals related to CoX and any direct subsidiary or CoX (all the CoYs). I could just add another layer to that query-string: – associate all CoZ IDs with the CoY, and then “step it back up” to Co X – but in theory there are an unlimited number of levels (and more to the point, an [bold]unknowable[/bold] number of them). It seems there should be a more general solution.
I apologize that this is not a very clear explanation – but if anyone has any suggestions, I would appreciate hearing (okay, reading) them.
