I am assuming that I am in need of a link table, but I do not know enough about how they work to properly implement it.
My current situation is this:
I have a nice production dashboard that I currently built. The basic flow is this (Tables)
Job Head-
Job Assembly-
Job Operations-
Labor Details
Labor Detail records only exist for jobs/asm/operations that have been worked on. It records the time ticket.
I am trying to create a Past 11 month WIP table that shows jobs that are currently in progress. For this I created a field in Labor detail that puts the labor into monthly buckets from Current to 11 months prior and over.
I also need to add an additional table that is Part transaction to get other costs for material and for sub contracts operations. In this table I also created the monthly buckets.
My current issue is that I now need to combine the monthly bucket values so that I can use this as a dimension to get all the costs over time for the jobs.
The issue that I am running into is that I would connect the Part tran table to the labor detail table, but then I am only getting part tran costs for jobs that have time worked. I essentially need the part tran table to connect to job operations to return all costs even if there is no work. Labor details also needs to connect to job operations due to the data being used other places in the dashboard.
Any one know of how I can accomplish this?
Edit: Part tran also does not contain the operation field, so this table needs a different link (Job/Asm) than the labor detail table (Job/asm/op)
