Interconnecting many-many relationships and sum ?

[jcarson]

I have a database that has the following tables:

contacts

companies

contactcompanyjoin (contacts can actually be associated with several companies)

conferences

registrations (a join between contacts and conferences)

Contacts and Companies are joined through related ID fields in the contactcompanyjoin table.

Contacts and Conferences are joined through related ID fields in the registrations table.

Companies are related to a second occurence of the registrations table by both Company ID and Conference ID (so that I can see who is coming to each conference from each company in a portal on the Companies layout)

Each Registration Record has a cost field, which is a calculation based on the registration type field. ie Case (Registration Type = "Full"; $500; etc...)

Then I have a Total Cost Field in the Registration Table which is a calculation field: Sum (cost field). So in my layout for the company, I can see a portal with all the attendees based on the Conference ID I enter. And the total cost for those attendees shown.

It works fine for all of the companies that do not share contacts with other companies. But when a contact is associated with multiple companies, it adds up all of the costs for all of the registrations associated with any of the companies sharing a contact.

Anybody have any ideas on what relationships would need to be adjusted?

Thanks.

[RalphL]

You have a many to many to many relationship. You may want to try a star join, i.e., one join table that connects Company, Contact & Conference. See Using FileMaker 7 pp 149-151 & 176-178. Also look at the sample file TaskManagement.fp7 under Business - Projects.