424,294 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,294 IT Pros & Developers. It's quick & easy.

Form field populted by 2 form droplist to cross reference Query

P: 23
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 "TempConversionChart".

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.
1 Week Ago #1
Share this Question
Share on Google+
34 Replies


PhilOfWalton
Expert 100+
P: 1,427
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. "CboSGravity" actually tells you somthing about what the control is for, "Temp" tells you nothing.

Phil
1 Week Ago #2

P: 23
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.
1 Week Ago #3

P: 23
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.
1 Week Ago #4

P: 23
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*Conversion)*WeightPerGallon)/2000
((2000*.9905)*8.38)/2000 results 8.30 Tons
1 Week Ago #5

PhilOfWalton
Expert 100+
P: 1,427
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
1 Week Ago #6

P: 23
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
1 Week Ago #7

P: 23
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.
1 Week Ago #8

PhilOfWalton
Expert 100+
P: 1,427
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
1 Week Ago #9

P: 23
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.
1 Week Ago #10

PhilOfWalton
Expert 100+
P: 1,427
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
1 Week Ago #11

P: 23
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.
1 Week Ago #12

PhilOfWalton
Expert 100+
P: 1,427
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
Attached Images
File Type: png Corrections.png (14.3 KB, 92 views)
1 Week Ago #13

P: 23
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
1 Week Ago #14

P: 23
Hey Phil. So using calculations fail, the error increase greatly as it approaches the upper end of the chart.
Ive 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 400as 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
2 Days Ago #15

P: 23
Ive completed everything else I needed too being it was yes/no, equations, simple inventory tracking and combo box charts. But Ive 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.
2 Days Ago #16

P: 23
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
Attached Files
File Type: zip DBtest.zip (177.8 KB, 6 views)
2 Days Ago #17

PhilOfWalton
Expert 100+
P: 1,427
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
Attached Files
File Type: xlsx Williamson.xlsx (24.1 KB, 3 views)
1 Days ago #18

P: 23
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
1 Days ago #19

P: 23
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.
Attached Files
File Type: xlsx testMP.xlsx (29.1 KB, 3 views)
1 Days ago #20

Rabbit
Expert Mod 10K+
P: 12,303
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.00000204052596 * TR^2 0.00000000232820948 * TR^3) / (1.00130346 0.000134722124 * TC + 0.00000204052596 * TC^2 0.00000000232820948 * 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.
1 Days ago #21

P: 23
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
1 Days ago #22

P: 23
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.
1 Days ago #23

Rabbit
Expert Mod 10K+
P: 12,303
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.
1 Days ago #24

P: 23
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
1 Days ago #25

P: 23
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)*multiplier).
1 Days ago #26

P: 23
Some formulas here that might help. At 60 degrees 0.80-1.05 are 1.000
Attached Images
File Type: jpg formula.jpg (94.0 KB, 4 views)
1 Days ago #27

PhilOfWalton
Expert 100+
P: 1,427
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
Attached Files
File Type: zip Williamson.zip (30.9 KB, 4 views)
21 Hours Ago #28

P: 23
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.
21 Hours Ago #29

P: 23
Phil I looked at your file, its just Form FrmCorrections with a table.
21 Hours Ago #30

Rabbit
Expert Mod 10K+
P: 12,303
I solved for p1 and plugged that in to see if the numbers calculate correctly; It didn't.

That could mean one of 3 things: the numbers in the chart are incorrect, or the formulas in the picture aren't the ones they used for the chart, or I did my algebra wrong.

In any case, if we can't get our hands on the formula, then you'll have to use the DLookup function to find the correct intersection in your table.
21 Hours Ago #31

P: 23
Thanks Rabbit and Phil for all the help by the way. Yeah I tried as well Rabbit. The 0.80 matches the paper correction chart straight from the book.

Does DLookup take the input of Temp and SG to return the intersection of the 2 in a text field? If so how do I do that? I'm new but I did try Dlookup with no luck.
20 Hours Ago #32

Rabbit
Expert Mod 10K+
P: 12,303
The syntax for DLookup is
Expand|Select|Wrap|Line Numbers
  1. DLookup("field name", "table name" , "filter criteria")
Any of those strings can be replaced with a dynamic value, say from a drop down.
20 Hours Ago #33

P: 23
So if I want to use Temp box, SG box to intersect values Id use the above in the correction field? I tried before but all I got was a drop list in the query. Of course I wouldnt know what value to use without the paper chart so little difference. Im going to assume I did something wrong.
18 Hours Ago #34

PhilOfWalton
Expert 100+
P: 1,427
Grovelling apologies, I sent the wrong database

Try this instead.

Phil
Attached Files
File Type: zip Williamson.zip (149.5 KB, 2 views)
4 Hours Ago #35

Post your reply

Sign in to post your reply or Sign up for a free account.