Your luck. I love these puzzles!
I will explain the low tech solution which is probably good enough unless you have many simultaneous users hammering away at this. (In which case I have a slightly enhanced setup).
I call this my "shine the light" solution. We will shine the light across the relationship from left to right.
Make a single record in a single table called Focus Project. Create a numeric field. Set the table permissions so no one can add another record. The record will be record ID# 1
Make a formula field on the project to update that record with the project record ID# and then display the Focus project record ([Record ID #1])
(In a separate discussion I can help you with that if you need help.
Make a field on projects as a formula numeric formula field with a formula of 1 and make a relationship back to the Focus project record.
Lookup the focus project down to the projects record.
Enter a value in the focus Project record for a test project record ID.
Make a formula field on the Projects field called [Project is in Focus] with a formula of
if([record ID#]= [Focus project record ID#],true)
Check that the Focus Project record is "lit up", ie [Project is in Focus] is checked!
Lookup up that [Project is in Focus] field down to the Tasks records. The Tasks for the Focus Project are also now lit up!
On the Standard Assemblies table create a Summary field of the [Total # of Assemblies Required for Focus Project] for illuminated tasks, ie subject to the filter that [Project is in Focus] is checked. Each respective Std assemblies now know how many Assemblies are required for the Focus Project!
Look up the [Total # of Assemblies Required for Focus Project] down to the items Assignments.
On Item Assignments, make a calculated field of the [Total # items required] with a formula of [Total # of Assemblies Required for Focus Project] * [Items Req'd per Assembly]. Now the Item Assignments each know how many are required! but we have duplicate items.
Summary the Total # of items required up to Items. Now the Items know how many are required!
Lastly to make this elegant, on the Focus Project record table, make make a report link field to show all items on that record. The record ID is 1 =so just make a field on the Item table with a formula of 1.
Put the report link field on the Focus project record and show the embedded table on the form. Set up a report to filer where # items required >0!
So when you enter the Focus project record ID# and Save, the light will shine form left to right and then the record you are sitting on will display the items counts required as an embedded table for the focus project. You will probably also want to do a relationship between the focus Project record and Projects to bring across the Project name.
