I have 3 tables. Properties (parent to PO's), PO's (child to Properties and Subcontractors), Subcontractors (parent to PO's). I have created a 4th table called Vendor rating.
A subcontractor can have several associated PO's for several addresses. The goal is once a property status changes, a list of all Subcontractors is available for that particular property and the Project Manager will then go through the rating system for that vendor for that property.
I'd like either to create a report that lists all Subcontractors used per property but I can only create a summary report in the PO's table to have the Subcontractor Name pull up once if they have multiple PO's for the same property. Ideally, I'd like to create an action that when the PO is created it creates a record for that property and that subcontractor in the Vendor Rating table. However, I only want ONE Subcontractor Name to be in the Vendor Rating table per property, not multiple times if there are multiple PO's for that Subcontractor. Then we should be able to look at all Subcontractors records and see their ratings per property on an embedded report from the Vendor Rating table. Not sure how I can do this.
Any thoughts about what I can do to make this work?