I have a job tracking table [Main Page] which has multiple codes that need to be associated with it. The [Main Page] draws a contract name from table [Contracts]. The code table [Synthetics] also has codes with associate Contract name from [Contracts]. It also contains Description, Unit and Price.
The [Synthetics] table can have the multiples of the same [Synthetics] code number with a different price associated with it which is based on the [Contract].
In the [Main Page], I need to be able to select the relevant [Synthetics] in a drop down based on the [Contract] that has been allocated to the job. This will then show the code number, and the Description, Unit and Price, together which an open field to add the quantity of each [Synthetics] code.
I have created an [Estimates] table which shows on the [Main Page] which looks at the [Synthetics] table and I can add multiple [Synthetics] codes against the individual job on the [Main Page] but the [Synthetics] list lists all of the codes, not just the ones based against the contract allocated.
It feels that I almost need some sort of formula lookup stating "List [Synthetics].[Code] WHERE [Synthetics].[Contract]=[Main Page].[Contract], but am unsure how this would work.
I hope this makes sense and someone can help.