Lowest Level Subquery:
Group by fields checked on this screen (calc field not checked)
Results (when switch to Top level query from inner subquery, switch back after)
You start with the PODtl, since that table holds the PartNum (which you need to group by so you can link it to its respective PO). I included the Company column for faster join time later on in the query.
Now that you have the PONum and the part it’s tied to, you can re-join this subquery to the POHeader and PODetail. Since there could be multiple PO Lines with the same part (multiple rows on the PODtl table per part), you’ll want to select distinct in your next subquerey (assuming the line prices are the same)
Then you can get the PO line for that part’s unit cost, and the order’s date
This will eventually be your second inner sub query, but I set to Top to verify results (then change back to inner subquery). You’ll want to account for the potential multiple PO lines related to the part (for example your last PO for a part actually has that part on 2 different lines, also assume the price would be the same) using the distinct setting on this second subqquery.
Results for second subquery:
Then you can finally join your second subquery in your top level query with the job mtl table!