Table1
Table1
ID | WeightStart | WeightEnd | Zone | Charge |
---|
1 | 0 | 0.5 | Zone 1 | 44.80 |
2 | 0 | 0.5 | Zone 2 | 54.8 |
3 | 0.5 | 1 | Zone 1 | 55.82 |
As said if you make your table like this
then your query is where the user supplied weight is > weightStart AND <= weightEnd and the Zone = "User Supplied Zone"
(This assume the user might have something weighing some other weight (.4kg for ex.)
This can be done without any vba if you want or at maximum a one line dlookup.
You can create this table in Excel very easily. Use the Paste Special Transpose option to get
Zone 1 | 44.8 | 55.82 | 64.02 | 72.22 | 82.12 | 88.08 | 94.04 | 100 | 105.96 | 111.92 | 114.58 | 117.24 | 119.9 | 122.56 |
Zone 2 | 54.82 | 69.35 | 82.17 | 94.99 | 108.55 | 114.77 | 120.99 | 127.21 | 133.43 | 139.65 | 144.39 | 149.13 | 153.87 | 158.61 |
Zone 3 | 55.52 | 69.99 | 82.83 | 95.67 | 108.67 | 115.61 | 122.55 | 129.49 | 136.43 | 143.37 | 148.04 | 152.71 | 157.38 | 162.05 |
Zone 4 | 58.49 | 71.21 | 83.67 | 96.13 | 109.75 | 120.45 | 131.15 | 141.85 | 152.55 | 163.25 | 168.93 | 174.61 | 180.29 | 185.97 |
Zone 5 | 60.68 | 74.27 | 87.11 | 99.95 | 114.92 | 126.73 | 138.54 | 150.35 | 162.16 | 173.97 | 178.83 | 183.69 | 188.55 | 193.41 |
Zone 6 | 72.33 | 89.55 | 105.65 | 121.75 | 142.01 | 155.07 | 168.13 | 181.19 | 194.25 | 207.31 | 213.81 | 220.31 | 226.81 | 233.31 |
Zone 7 | 107.84 | 134.55 | 157.95 | 181.35 | 209.61 | 228.51 | 247.41 | 266.31 | 285.21 | 304.11 | 314.65 | 325.19 | 335.73 | 346.27 |
Then cut and past the columns into this and add in the weight endpoints.
WeightStart | WeightEnd | Zone | Charge |
0 | 0.5 | Zone 1 | 44.8 |
0 | 0.5 | Zone 2 | 54.82 |
0 | 0.5 | Zone 3 | 55.52 |
0 | 0.5 | Zone 4 | 58.49 |
0 | 0.5 | Zone 5 | 60.68 |
0 | 0.5 | Zone 6 | 72.33 |
0 | 0.5 | Zone 7 | 107.84 |
0.5 | 1 | Zone 1 | 55.82 |
0.5 | 1 | Zone 2 | 69.35 |
0.5 | 1 | Zone 3 | 69.99 |
0.5 | 1 | Zone 4 | 71.21 |
0.5 | 1 | Zone 5 | 74.27 |
0.5 | 1 | Zone 6 | 89.55 |
0.5 | 1 | Zone 7 | 134.55 |
1 | 1.5 | Zone 1 | 64.02 |
1 | 1.5 | Zone 2 | 82.17 |
1 | 1.5 | Zone 3 | 82.83 |
1 | 1.5 | Zone 4 | 83.67 |
1 | 1.5 | Zone 5 | 87.11 |
1 | 1.5 | Zone 6 | 105.65 |
1 | 1.5 | Zone 7 | 157.95 |
Then import the new table into access.