Nested Subreports and "Many to Many" Queries

[Guest]

I am having a heck of a time trying to create several nested subreports in a
main report. I think my main problem may be because I have two nested
queries because of a “many to many†relationship in my database. Let me
describe my tables and their relationships.

The main table is Allotments representing land parcels in a water district.
The second main table is ShareHolders representing Allotment owners.
The third main table is Shares which acts as a “go-between†between
Allotments and ShareHolders.
The fourth table is a three field table containing ShareHolderContactComments.

One Allotment can have many ShareHolders (i.e. the land parcel has been sold
several times.)
One ShareHolder can have many Allotments.

To solve this, I created this "many to many" relationship:

Allotments Shares ShareHolders
ShareHolderContactLog
AllotmentIDNo-->AllotmentIDNo /--ShareHolderIDNo-->ShareHolderIDNo
Allot. Address ShareHolderIDNo<-/ ShareHolderAddr Contact Date
SharePurchaseDate
ContactComments
ShareSellDate

So the Share table has multiple records containing the same AllotmentNo and
multiple records containing the same ShareHolderIDNo.

I am trying to prepare a report that looks like the following:

Allotment Information
Address, acreage, croptype, etc.

Subreport ShareHolder and Shares Information
ShareHolder Address Share Purchase Date
Share Sell Date

SubsubreportShareHolderContactLog
Contact Date Contact Comments

The Subreport ShareHolder and Share Information should generate1 to 4 times
for each Allotment depending on how many ShareHolders have bought the
Allotment. The ShareHolderContactLog subsubreport will generate from 0 to
the total number of times a contact record for the ShareHolder was entered in
the table.

My main report is sourced on just the Allotment table. My subreport is
sourced on a “many to many†query containing fields from Allotments, Shares,
and ShareHolders. The subsubreport is sourced on just the
ShareHolderContactTable.

The main report and subreport generate fine. When I tried to insert the
SubsubreportShareHolderContactLog into the SubreportShareHolder and Share
Information, I get the dreaded message box “Enter parameter value†asking for
a value for “shareholdersâ€.

The linkage between my subreport and subsubreport is as follows:
Link Child Fields ShareHolderIDNo
Link Master Fields ShareHolders.ShareHolderIDNo

The relationship between the ShareHolder table and the ShareHolderContactLog
table have been identified in my relationships.

Does anyone know of a way to get around this? Or am I making things too
confusing? Or am I simply on the wrong path?

Thanks for whatever help you folks can provide!

Jonathan Mulder
Department of Water Resources
Red Bluff, CA

 

[Guest]

To paraphrase The Firesign Theater Comedy Group from the sixties,
"Everything I know is WRONG!"

I was able to get the subreport and subsubreports to work perfectly!
I was making the queries too convulted. It pretty much amounts to a "drill
down" kind of process without having to invoke a "many to many" nested query.

Jon Mulder
Department of Water Resources
Red Bluff, CA