Form field populted by 2 form droplist to cross reference Query

[williamson1979]

Hello,

I'm working on a access 2016 DB. I have a combo box "Temp", a combo box "Specific Gr" and textbox "Conversion ". I have a query "TempConversion Chart".

So it looks roughly like this but overall is 91 rows x 7 columns. The corrections are more complicated but similar.

F 085 090 095 100 105
300 0.0001 0.0002 0.0003 0.0004 0.0005

302 0.0011 0.0012 0.0013 0.0014 0.0015

goes on for 89 more rows.

What I want to do is use a combo box to show "F" values. A combo box to show the columns "085", "090", "095", "100" and "105". I want the text box to display the intersection of the 2 combo box selections, Example 302 is selected and 095 is selected displaying the result 0.0013 in the text box so the rest of the form can calculate the corrected values.

[PhilOfWalton]

Welcome to Bytes.

What you are looking to achieve is comparatively simple, but before proceeding, I want to make absolutely sure that the Correction Values can't be calculated from Specific Gravity field, and the other field (what does this number represent and what is it's name?).

If this is the case, you are going to be amused for hours entering about 500 values.

As a general point, try to give all field names in tables a meaningful name and the same applies to controls on forms. Also don't have spaces in control names, field names, table names or query names. If you do, you will have to put square brackets round the name [Specific G]

Have a look at https://en.wikipedia.org/wiki/Leszyn...ing_convention which is the convention that many programmers use. "CboSGravit y" actually tells you somthing about what the control is for, "Temp" tells you nothing.

Phil

[williamson1979]

Hey Phil,

Every 3rd value is slightly different. I spent hours trying to figure out the math behind the corrections but was unable too. Maybe it is a simple equation but it evades me and no one I know knows the math. My guess is every 3rd value is different based on rounding though.

It will be simple at this point to remove spaces from names. I didn't have spaces in table, query or macro names but I guess I dropped the ball on controls.

[williamson1979]

But you are correct in that I need to correct before getting to far. This is my first attempt at anything like this so I'm sure its flawed but I have figured out some basic stuff but I'm sure about 99% I have not.

[williamson1979]

So what it does is take the specific gravity to determine what correction is needed. Once that is determined the current temperature is used to show what its weight is at 60 degrees.

((GallonsUsed*C onversion)*Weig htPerGallon)/2000
((2000*.9905)*8 .38)/2000 results 8.30 Tons

[PhilOfWalton]

If you've got all the values already entered into the table, and would care to share your Db with me, I'll see if I can spot a pattern.

You haven't answered my question about what the field containing 300 and 302 is called and what is it for.

I appreciate you are new to this forum, but please try to answer all the questions

Phil

[williamson1979]

The only other var I know of is 60 degrees which equals 1.0000, so 60F correction would be 1.0000 on all corrections regardless of specific gravity.

Temp Specific Gravity Columns
F° 0.80 0.85 0.90 0.95 1.00 1.05
220 0.9234 0.9322 0.9395 0.9458 0.9511 0.9556
222 0.9224 0.9313 0.9388 0.9451 0.9504 0.9551
224 0.9215 0.9305 0.9380 0.9444 0.9498 0.9545
226 0.9205 0.9296 0.9373 0.9437 0.9492 0.9540
228 0.9195 0.9288 0.9365 0.9430 0.9486 0.9534
230 0.9186 0.9279 0.9357 0.9424 0.9480 0.9528
232 0.9176 0.9271 0.9350 0.9417 0.9474 0.9523
234 0.9166 0.9262 0.9342 0.9410 0.9468 0.9517
236 0.9157 0.9254 0.9335 0.9403 0.9461 0.9512
238 0.9147 0.9245 0.9327 0.9396 0.9455 0.9506
240 0.9128 0.9236 0.9317 0.9389 0.9449 0.9501
242 0.9128 0.9228 0.9312 0.9383 0.9443 0.9495
244 0.9118 0.9219 0.9304 0.9376 0.9437 0.9489
246 0.9108 0.9211 0.9297 0.9369 0.9431 0.9484
248 0.9099 0.9202 0.9289 0.9362 0.9425 0.9478
250 0.9089 0.9194 0.9281 0.9355 0.9419 0.9473

[williamson1979]

300, 302 and so on is current temperature of the media. 60 is temperature it all must be converted back too. Depending on the medias specific gravity the correction is different to accomplish that.

[PhilOfWalton]

I am getting somewhere with doing a calculation to get the values.

On the data you sent me, comparing your data with my calculations, I get 5 errors, none of them more than 0.0002 difference between them.
If this is acceptable, I will see if I can get any further.
As your S.G. is in multiples of 5 (5%) of the range, I would guess that an accuracy of 0.02%, my guess is that the calculated correction figure is very acceptable.

Williamson.xlsx

The table on the left is your original data, and the one on the right my calculations. The bottom row is the "seed" for the rows above. At the moment I am stuck on turning the temperature (220) and the S.G. (80) into the start correction value of 0.9089.

Phil

[williamson1979]

Hey Phil,

I've been working on the math as well since my last post. So the formula seems to be ((60 - Temperature)*0. 0004806)+1 roughly on the 0.0004806 though. That's just using trail and error as I'm yet to figure out how to add the Specific Gravity "0.80, 0.85 etc" into the math. I'll look over your spreadsheet.

[PhilOfWalton]

Well done.

I suspect if you use 0.0004792 instead, you will get a result where in less than half the results, the error is only 0.0001, and in the other results, there is no error.

At the moment, I can see no consistency between the S.G. & correction. While the S.G. goes up neatly in steps of 5, the corrections are all over the place.

Phil

[williamson1979]

Good deal I will try that number. But if no consistency I'll still end up having to use my Temp and SpecficGr combo boxes to intersect the correction factor in a query. I tried using the cross tab wizard but had zero luck. I'm fine typing the commands except I do not what to type.

[PhilOfWalton]

Good news. You only need to hold 6 value in your Db, the rest can be calculated

Here is the form that is used for inputing the SG and temperature. I see no point in using a Combo Box for the temperature because by the time you have scrolled to the correct one, it will be quicker just to enter the value.

I will let you have a crack at doing it yourself, but if you get really stuck,I will give you a copy of my effort. Note, it needs refining because I was only interested to see if the principal worked.



Phil

[williamson1979]

Thanks Phil,

I really appreciate you help and the extra effort. Once I see how its done hopefully I'll get it. I'll follow up.

Thanks

[williamson1979]

Hey Phil. So using calculations fail, the error increase greatly as it approaches the upper end of the chart.
I’ve spent much time trying to figure out a correction but no luck as the individual corrections seem inconsistent. I believe my best bet is going to be... ComboA (Temp “60 to 400”as Row) intersect with ComboB (Correction Columns “0.80-1.05”). ComboA “220” and ComboB “0.80” would intersect at 0.9234

Is this possible with access?
Thanks