Complex Subform Query

[^Nightwing^]

I will try to word this as best I can to explain my problem. I have a many-to-many-to-many database setup, where the important tables are the Companies and the Investors, with tables called Investments and Groups to act as go-betweens to enable the many-to-many relationship.

So I have a form for viewing the Investor information. Within this is a subform which displays in continuous form style all of the companies that the investor is linked to (has invested in) via the groups and investments tables. Now what I want to do is list within this subform, for each company (for each record in the subform basically), I want to see either all of, or in the best instance, the other investors who were part of the group that made up that investment.

Is this possible? I realise that subforms within subforms isn't allowed for continous forms (which sucks, why is that?). If I visualise it as a tree structure, I figure it should be possible, but please, how? Guidance appreciated!

 

[^Nightwing^]

Gosh, I am going to have to shamelessly bump this before bed.

 

[PierreR]

I really want to answer, but you are unclear on how companies, investors, groups and investments interact (in the real world.)

From there I would gladly work out a solution.

Pierre.

 

[^Nightwing^]

Well I'll try to explain then.

There are a series of companies, and a series of investors. Investors form a collective group, which then performs a single investment into a single company. So a single company has a series of investments, by different groups of investors. A single investor can be part of many groups and invest in many companies. Hence it is a complex many to many relationship. My current musings are available at www.srcf.ucam.org/~cmf40/HID.mdb which might help you to see the relational structure I have put in place.

 

[PierreR]

Nightwing,

I'm preparing to become a freelance Access db designer, and helping others is part of the process.

I struggled with your database for a while, and I think the problem is that you need an extra table for a many-to-many-to-many setup. As you have it at the moment, it is impossible to have multiple investors in a group. The way to do it is to place an extra table between Capital History and Group/Capital History Junction, both those tables having a secondary key called GroupID linked to its primary key.

I'm trying to work something out, but in my solution some of your old forms are not going to work.

(I'm in Austin, Texas, USA.)

Pierre.

 

[PierreR]

I just added another subform. But this is kind of spread out, especially if you want to have set groups doing several different investments. Maybe your relationships were good, for what you want to do. See the zipped file.

 

[^Nightwing^]

Thanks! You've basically done what I wanted in your version, so I've copied that across to my version and it all works nicely, thanks. Incidentally this is some freelance work that I am doing, only I'm learning on the job .

 

[PierreR]

I've got an interesting zipped alternative with all kinds of neat coding in it for you, Nightwing. Of course I picked up neat tricks from your database too. Thanks for that.

Pierre.

 

[PierreR]

... Except that I got the broker's fee and the price per share mixed up in the summing calculation! But you'd be able to see how it works anyway.