I have two tables: Time and Item
table.Time is the amount of [Days] associated with a [Step] based on cost identified in a range (Fields for [Cost Min]/[Cost Max])
table.Item is a list of [Item]s with it's current [Cost] and [Step].
I need to return the current and future [Step]s and their respective [Days] from table.Time within a cost group ([Cost Min]/[Cost Max]) based on the [Cost] and [Step] of the item selected in table.Item. The [Step]s I need to return are the selected one, and all that come after in that group (potentially determined by [UN] range? (unique identifier for table.Time)
table.Time:
| Time |
| UN | Cost Min | Cost Max | Step | Days |
| 1 | 0 | 10 | Step 1 | 4 |
| 2 | 0 | 10 | Step 2 | 3 |
| 3 | 0 | 10 | Step 3 | 2 |
| 4 | 0 | 10 | Step 4 | 1 |
| 5 | 11 | 20 | Step 1 | 8 |
| 6 | 11 | 20 | Step 2 | 7 |
| 7 | 11 | 20 | Step 3 | 6 |
| 8 | 11 | 20 | Step 4 | 5 |
| 9 | 21 | 30 | Step 1 | 12 |
| 10 | 21 | 30 | Step 2 | 11 |
| 11 | 21 | 30 | Step 3 | 10 |
| 12 | 21 | 30 | Step 4 | 9 |
table.Item
| Item |
| Item | Cost | Step |
| A | 10 | Step 1 |
| B | 12 | Step 2 |
| C | 30 | Step 4 |
Examples of returns when specific items are selected in table.Item:
| Return When Item A Selected |
| Step 1 | 4 |
| Step 2 | 3 |
| Step 3 | 2 |
| Step 4 | 1 |
| | |
| Return When Item B Selected |
| Step 2 | 7 |
| Step 3 | 6 |
| Step 4 | 5 |
| | |
| | |
| Return When Item C Selected |
| Step 4 | 9 |
Ultimately, in the exmaples above, [Days] needs to be a date in the future calculated off TODAY() adding the number in [Days]. I also am not sure the best visual to return this to.
Edit*** I realized my initial presentation with colors in place of [Step]s was unclear so I changed the data to explain better.
