Calculating second field via related table
I tried searching for a solution, but wasn't even sure how to word my question....?
I'm developing a DB that creates running templates for our runners. Within my "templates" layout, I have a portal to a table called "Run". Within the portal, I select different running distances for different days of the week. The distances are currently pulled from a value list (1, 2, 3, 4, 5.... etc). Then I have a calculation field that adds all the mileage for the week. Everything works fine, except I also have some mile repeats in the value list. An example: On monday, run 3 x 0.5 miles. So the value list has "3x 0.5" as a value. This messes up my total mileage calculation, as it adds 3.5 to the total, instead of multiplying the 3 and 0.5 for a total of 1.5.
I figured I should go away from the value list, and make a table of mileage instead. My table has "ActualMileage" (actual mileage for the day, or "1.5" as per example) and "DisplayedMileage" (what the user will see on screen, or "3x 0.5" as per example) as fields. I just can't figure out how to make my total mileage calculation refer to the "ActualMileage" field when I select the "DisplayedMileage" value from a drop down list.
Any direction is greatly appreciated. Thank you.