How to set up schema for a report involving two children tables

[21st Century Man]

Hi all, and happy holiday season.

 

I'm stuck how to set up the schema so I can create a report involving two child tables connected to a parent table. (I guess things around here have changed; my existing account is gone and I can't upload an image or file so I'll have to describe my request textually.)

The three tables are Grant (parent), Participant (child), and Vendor (child). Grant pushes data (in a one-two-many) relationship to each child table.

I want to print a report where it summarizes participants by vendor, so each vendor will have a list beneath it of related participants through Grant. How do I set that up...or can I?

 

Cheers,

Rich

[comment]

Something seems to be missing from your description:

If all you have is the two relationships, with Grants being the parent of both Participants and Vendors, then a participant is not associated with any particular vendor. In order to fulfill your requirement to "summarize participants by vendor" there must be another relationship that links the two tables.

Unless you want your report to repeatedly list all participants of a grant under each vendor?

 

[21st Century Man]

...so I take it that I need to create a join table between Participants and Vendors? That _would_ make sense, but I'm wondering how would I go about populating the join table if Participants and Vendor are being populated separately via separate portals in Grants. (They're each on their own tab in a tabbed layout.) 

>"Unless you want your report to repeatedly list all participants of a grant under each vendor?"<

Good question--I'll have to ask the administrator who wants this report created.

[comment]

17 minutes ago, Richard Stuart said:

...so I take it that I need to create a join table between Participants and Vendors?

I don't think so, but I don't really know what these entities represent in real life. A join table would be appropriate if a participant can belong to more than one vendor (and vice-versa). I get the impression that's not the case, and that the actual structure is parent -< child  -< grandchild, where each participant belongs to one vendor and each vendor belongs to one grant.

If you prefer to assign a participant to a grant first, that's fine - but then you also must assign one of the grant's vendors to the participant. Unless again, all vendors of a grant are equally related to each participant of the same grant - and in such case the term "participants by vendor" has no meaning.

 

Edited December 5, 2024 by comment