Form calculation from different table rows and columns?
I'm relatively new t calculations and am doing ok except when trying to do relational calculations.
I have 3 fields:
1. Base Weight (user entry field)
2. Destination (Drop down menu from "To" column of a linked FMDB)
3. Rate (This is an auto calculated field that I'm having difficulty with)
The linked DB "Freight_Rates" has 4 columns:
1. To (same one used for the drop down from "2" above)
2. Over 1000kg
3. 501 to 1000kg
4. 1 to 500kg
What I'm trying to do is calculate the rate based on Destination & Baseweight.
For example: User enters Base Weight of 550 and Destination of Sydney (DB=Freight_Rates colA row3)
Auto calculate to work out that the Base Weight is greater than 501 and less than 1000 and therefore uses column 3 (501 to 1000kg) and then relates it to row 3 (Destination::Sydney) = Rate for "501 to 1000kg" for destination "Sydney"
Thanks, my head's looping :|