Form field populted by 2 form droplist to cross reference Query

[williamson1979]

I’ve completed everything else I needed too being it was yes/no, equations, simple inventory tracking and combo box charts. But I’ve spent the past week trying to work the math on the chart. I found every multiplier but could not come up with a way in one equation using just the temperature, specific gravity and fixed multiplier to stay in tolerance at the highest temps. Off as much as 0.0006 which is huge in this application.

[williamson1979]

Attached is what I have so far. My biggest issue is what I posted prior. The only other thing would be the aggregate boxes. It would be nice to use combo boxes to select what is actually being used or added but I could not figure out how to make it track totals to the report that way. Sorry it took me awhile to find how to attach files.

This is my first Access use so please bear with me. Thanks

[PhilOfWalton]

I see what you mean about the conversion factor going "off" at the higher end of the temperature scale. It is worst at an S.G of 80, and becomes very small at an S.G if 105. I originally only had temperatures from 220 to 250F°.

I have jiggled the factor, and in the worst case scenario, get an error of 0.0007 in the last decimal place.I have been having another look at trying to calculate the factors from the S.G & temperature.
Depending on the accuracy required, in the worst case scenario, I get an error of 0.017%. This means there may occasionally be an error of 0.01 in the weight of aggregate produced.

Attached is a spreadsheet for you to have a look at and make any observation on the accuracy. (If anyone else following this thread can come up with anything better, I'm sure we would be grateful).

We will look at your tables, relationships and forms later.

Phil

[williamson1979]

Hey Phil, it really needs to be exact. As posted earlier I spent a week trying to take the avg factor and include extra math to compensate but my math skills aren't good enough. I was only able to get about 1/2 to .0000 error by using rounding and a percent by each degree increase.

Overall did I use tables and queries correctly?


I'll look at your math but I really think I'll have to intersect values or maybe its possible to link each multiplier to the temp its associated with similar to SG query? I have excel spreadsheet started with this information so if possible I could just paste them into a table.


Can I put a combobox on the bottom aggregate section rather than 10 spots because it could be 5 or could grow beyond 10. But the problem is in the table its linked to agg1 agg2 and so on. Being my first time at this I don't know how or if I can link a choice in a combo to a specific field in the table.

Thanks

[williamson1979]

Here is where I got. Right of is each correction is its multiplier. On the far right was my attempt at only 1 multiplier with some extra math but it still gets off.

[Rabbit]

There is a known formula for temperature adjustments of specific gravity measurements.

CG = corrected gravity
MG = measured gravity
TR = temperature at time of reading
TC = calibration temperature of hydrometer

CG = MG * ((1.00130346 – 0.000134722124 * TR + 0.0000020405259 6 * TR^2 – 0.0000000023282 0948 * TR^3) / (1.00130346 – 0.000134722124 * TC + 0.0000020405259 6 * TC^2 – 0.0000000023282 0948 * TC^3))

However, it doesn't seem to produce the same results as your chart. That may be because the above formula is for hydrometers calibrated on water. It's possible your hydrometers are calibrated on a different liquid or gas and would require different constants for it to work.

[williamson1979]

This mentions a weight using 8.328 ppg water but is not related to the chart but rather overall gallons but requires the SG's above to convert to 60F.

Net weight in pounds (x), divide 8.328 (pounds per gallon of water, which has a specific gravity of 1.0) = y
Divide y by the specific gravity (z)
Assume the following: x= 66,920, z= 1.0273
Example: 66,920 divide 8.328= 8,035.54 (y)
8,035.54 divide 1.0273 (z)= 7,822 gallons at 60 degrees Fahrenheit

[williamson1979]

But in the excel sheet I uploaded I found all the multipliers. But each temp has 6 possible multipliers depending on SG. So Id still have to cross reference somehow as if I just cross referenced the chart anyway if I'm not mistaken. Is there no easy way to just intersect the temp with the SG in a query to fill in the correction to a text box? If I can do that the rest of the math is in the form.

[Rabbit]

You can using the DLookup function.

What we're trying to get to is finding the underlying formula that the lookup chart is created with so that you don't have to use the lookup function and so you don't have to store a lookup table in the first place. Basically simplify the whole thing by removing the middle man. If you can find the constants for the formula above, you can greatly simplify the whole thing without having to do a lookup. But if you don't have the constants, then the fallback is to use the DLookup function.

[williamson1979]

What about VBA code, something like:

if [nPg1Temp]=220 and [nPg1S]=0.80
0.9234
else if [nPg1Temp]=222 and [nPg1S]=0.80
0.9224

And just repeat for all temp and SG combos and it load the result to Correction text box for the calculations? I tied this but got compile errors

[williamson1979]

Thanks for the reply Rabbit. I'd love to do as you say but I spent a week on it. With the correction multipliers going up and down I couldn't figure out the formula. All I found was 1+((60-current temp)*multiplie r).

[williamson1979]

Maybe this can help

Some formulas here that might help. At 60 degrees 0.80-1.05 are 1.000

[PhilOfWalton]

I have modified your Db and added tables TblCorrections (exactly as your initial table including the error in SG80, temperature 240) and TblCorrections.

Also added Form FrmCorrections. This is purely to get the corrections working correctly. which I think I have achieved.

If you are happy with this, I think you should have a look at your tables & relationships, which leave lots to be desired. In particular, it is very very rare to store calculated values in table. The sort of calculation you are doing should be done in a query that is the recordsource of your forms & reports.

Phil

[williamson1979]

I'll take a look Phil. I'm sure there are inconsistencies on how it should be done but this is my fist attempt at access. My question then is if I don't store the calculations to the table how do I have a record to go back to in the future when I need to retrieve said calculations? Relationship wise I think I only had to add 2 to get the desired result but that said it doesn't mean its correct. Honestly until I ask for help here everything else was just trial and error trying to convert from a excel sheet add reduce risk of errors on my part.

[williamson1979]

Phil I looked at your file, its just Form FrmCorrections with a table.