I attach a simple sample application that contains the following data structure:
- For every "Movements" record I have one "Items" record as you can see
- The "Movements" record contains the quantity field "Qty" and the "Items" record contains 20 different "price" fields (10 including tax and 10 excluding) e.g. "Price1Excl", "Price1Incl", "Price2Excl" etc.
- Additionally, I have a separate table "PriceHeadings" which contains the descriptions of each of these prices - for example "PriceDesc1" contains the text "RETAIL", "PriceDesc2" the text "WHOLESALE" etc.
The requirement is to produce a table such as the one below where the last column "Value" will be the value based on the "Price" selected from a drop down list that will contain the prices with their descriptive names - e.g. RETAIL, WHOLESALE etc ..
In the example below I am showing Value (which is "Movement" x Price) using the "Price1Excl" price as an illustration.
Hope the above makes sense - I attach the sample application with some data...
I would appreciate your assistance on this as it's been troubling me a lot