I have two tables like this:
Runners: Name|Date |FromDist|ToDist|RaceN Al |1/1/2017|1 |9 |axe CC |1/1/2017|11 |12 |axe Bm |2/1/2017|6 |2 |Charity RaceStandards: RaceN |Date |FromDist|ToDist|Direction|Rank axe |1/1/2017|1 |10 |+ |1 Charity|2/1/2017|10 |4 |- |2
The race standards table gives the direction and distance ranges for each race. The runners table gives the distances ran by each runner.
Question/Issue/What I want to do:
1. Get the direction
2. If its +, range should be >=FromDist and <=ToDist; If its -, range should be <=FromDist and >=ToDist
3. Compare the distance range of Runners FromDist and ToDist based on steps 1 and 2. Assign the Rank number of that race if they fall in range, otherwise assign 0
4. If Runner is out of Range the value can be zero
So the end result would be something like this:
Runners: Name|Date |FromDist|ToDist|RaceN |Rank Al |1/1/2017|1 |9 |axe |1 CC |1/1/2017|11 |12 |axe |0 Bm |2/1/2017|6 |2 |Charity |2
There would be hundreds of rows in each table so I would want it dynamic. There could be overlaps and stuff but I just need a headstart for now, once I get a base code I can try to expand from there. I would appreciate any help. I tried to give as much detail, if more is required please ask.