100+
P: 161

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.
 
I'll try to explain the code, but if you want further help on the tables & forms, I think you should start another thread, as this one if getting too long. See https://bytes.com/newreply.php?do=postreply&t=972149 and any other articles you can find about Database Nomalisation.
To try to explain the code, the 2 update statements check here is a valid S.G. & Temperature.  Option Compare Database

Option Explicit


Function GetCorrection(Temp As Single) As Double


Dim MyDb As Database

Dim CorrectionSet As Recordset

Dim StrSQL As String

Dim TDF As TableDef

Dim Inti As Integer

Dim FieldName As String


Set MyDb = CurrentDb

Set TDF = MyDb.TableDefs("TblCorrections")


For Inti = 2 To 7 ' We don't want the CorrectionID or Temperature columns


Debug.Print TDF.Fields(Inti).Name

If TDF.Fields(Inti).Name = Me!CboSpecificGravity.Column(1) Then ' Field name matches CboSpecific Gravity

FieldName = TDF.Fields(Inti).Name ' Name of S.G. field

Exit For

End If

Next Inti


StrSQL = "SELECT DISTINCT TblCorrections." & FieldName & " FROM TblCorrections "

StrSQL = StrSQL & "WHERE Temperature = " & Temp & ";"

Set CorrectionSet = MyDb.OpenRecordset(StrSQL)


With CorrectionSet

Correction = .Fields(0).Value

.Close

Set CorrectionSet = Nothing

End With


End Function
Line 4 defines the function and says we will the temperature as single number, and the output as a double precision number.
Line 14 creates a TableDef, the same as Table TblCorections, but we can find the field names from this (SG80, SG85, ... SG105). Note that the fields are numbered from zero. What a coincidence ... they also happen to be the same names as appear in the S.G. Combo Box as the second column (Column(1) as the first column ( set to zero width, so that you can't see it is named Column(0)
Lines 16 to 23 scan the field names till they find the name as has been selected from the S.G/ Combo box. So for example, if we select SG90, we get all the values in the 5th. column of the table, and there is only a single column named "FieldName", but Access will equally be satisfied with Field(0).
So we build a simple query to find the correction corresponding to the temperature selected in the Temperature Combo Box.
This thread is far too long, so if you want talk about database structure ans table, I think you should start a new thread.
Phil
Share this Question
Expert 100+
P: 1,430

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
  100+
P: 161

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.
  100+
P: 161

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.
  100+
P: 161

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
  Expert 100+
P: 1,430

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
  100+
P: 161

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
  100+
P: 161

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.
  Expert 100+
P: 1,430

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
  100+
P: 161

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.
  Expert 100+
P: 1,430

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
  100+
P: 161

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.
  Expert 100+
P: 1,430

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
  100+
P: 161

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
  100+
P: 161

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.801.05”). ComboA “220” and ComboB “0.80” would intersect at 0.9234
Is this possible with access?
Thanks
  100+
P: 161

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.
  100+
P: 161

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
  Expert 100+
P: 1,430

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
  100+
P: 161

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
  100+
P: 161

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.
  Expert Mod 10K+
P: 12,366

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.
  100+
P: 161

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
  100+
P: 161

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.
  Expert Mod 10K+
P: 12,366

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.
  100+
P: 161

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
  100+
P: 161

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+((60current temp)*multiplier).
  100+
P: 161

Some formulas here that might help. At 60 degrees 0.801.05 are 1.000
  Expert 100+
P: 1,430

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
  100+
P: 161

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.
  100+
P: 161

Phil I looked at your file, its just Form FrmCorrections with a table.
  Expert Mod 10K+
P: 12,366

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.
  100+
P: 161

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.
  Expert Mod 10K+
P: 12,366

The syntax for DLookup is  DLookup("field name", "table name" , "filter criteria")
Any of those strings can be replaced with a dynamic value, say from a drop down.
  100+
P: 161

So if I want to use Temp box, SG box to intersect values I’d use the above in the correction field? I tried before but all I got was a drop list in the query. Of course I wouldn’t know what value to use without the paper chart so little difference. I’m going to assume I did something wrong.
  Expert 100+
P: 1,430

Grovelling apologies, I sent the wrong database
Try this instead.
Phil
  100+
P: 161

Thanks Phil,
Im looking at your code trying to understand how you made it work. Maybe I'll learn something to help me improve the rest Ive worked on. There are improvements Id like to make but don't know the language so I used all built in options which was aggravating this being my first attempt at this.
So you said earlier I needed to remove field calculations and work on relationships. From what I had uploaded could you give me some pointers on how I should have relationships and if I remove the field calculations must I use code as you did instead? If so will it write the values to the main table?
Thanks for the help always
  100+
P: 161

Phil, I restarted the DB breaking the main table into smaller tables and using vba to do the calculations. I read to set tables the way you might file documents. As far as the relationships go if field1 is in the calculation with field 2 from another table I added a relationship, same thing for table fields pulling info from other tables/queries in my comboboxes.
I don't understand your SG code but I'll copy it over and maybe figure it out.
  Expert 100+
P: 1,430

I'll try to explain the code, but if you want further help on the tables & forms, I think you should start another thread, as this one if getting too long. See https://bytes.com/newreply.php?do=postreply&t=972149 and any other articles you can find about Database Nomalisation.
To try to explain the code, the 2 update statements check here is a valid S.G. & Temperature.  Option Compare Database

Option Explicit


Function GetCorrection(Temp As Single) As Double


Dim MyDb As Database

Dim CorrectionSet As Recordset

Dim StrSQL As String

Dim TDF As TableDef

Dim Inti As Integer

Dim FieldName As String


Set MyDb = CurrentDb

Set TDF = MyDb.TableDefs("TblCorrections")


For Inti = 2 To 7 ' We don't want the CorrectionID or Temperature columns


Debug.Print TDF.Fields(Inti).Name

If TDF.Fields(Inti).Name = Me!CboSpecificGravity.Column(1) Then ' Field name matches CboSpecific Gravity

FieldName = TDF.Fields(Inti).Name ' Name of S.G. field

Exit For

End If

Next Inti


StrSQL = "SELECT DISTINCT TblCorrections." & FieldName & " FROM TblCorrections "

StrSQL = StrSQL & "WHERE Temperature = " & Temp & ";"

Set CorrectionSet = MyDb.OpenRecordset(StrSQL)


With CorrectionSet

Correction = .Fields(0).Value

.Close

Set CorrectionSet = Nothing

End With


End Function
Line 4 defines the function and says we will the temperature as single number, and the output as a double precision number.
Line 14 creates a TableDef, the same as Table TblCorections, but we can find the field names from this (SG80, SG85, ... SG105). Note that the fields are numbered from zero. What a coincidence ... they also happen to be the same names as appear in the S.G. Combo Box as the second column (Column(1) as the first column ( set to zero width, so that you can't see it is named Column(0)
Lines 16 to 23 scan the field names till they find the name as has been selected from the S.G/ Combo box. So for example, if we select SG90, we get all the values in the 5th. column of the table, and there is only a single column named "FieldName", but Access will equally be satisfied with Field(0).
So we build a simple query to find the correction corresponding to the temperature selected in the Temperature Combo Box.
This thread is far too long, so if you want talk about database structure ans table, I think you should start a new thread.
Phil
  100+
P: 161

Surething Phil, 1 last question here.... On temperature entry I get a error on this line Set CorrectionSet = MyDb.OpenRecordset(StrSQL) "missing operator in query exoression"
  Expert 100+
P: 1,430

Are you using the form "FrmCorrections"? The database I sent you works perfectly. Does it for you?
If you have changed any Control Names, change them back to the names I gave you, because the StrSQL won't find those names.
If you still have problems, please send the your Db back to me.
Phil
  100+
P: 161

Hey, I moved it to the updated DB but named the controls and tables to match your code. That said after it gives the error it works just fine. I had to remove the 2 options listed st the top because it wouldn’t run with them. I do have one option compare database at the beginning of my calculations codes though.
It isn’t using frmCorrections. I put it to the controls on my form. So I need to rename Corrections to the name of my form?
  100+
P: 161

Phil,
Here is what Ive got done taking your above mentioned advice. Hopefully its better than before just Im getting the error with your code after I moved it to the report form to intergrade it.
The data is no longer easy to follow with multiple tables but I figure a query will fix that.
  Expert 100+
P: 1,430

Sorry, got the same error in both the updates. It should read  If Nz(cboSpecificGravity) = 0 Or IsNull(cboTemperature) Then
instead of
t  If IsNull(cboSpecificGravity) Or IsNull(cboTemperature) Then
It is essential that you have Option Compare Database and Option Explicit at the top of every module.
For example, Option Compare Database will treat the letter "A" and the letter "a" as identical. Useful for ordering, filtering etc. So "A" = "a"
Option Compare Binary (not often used) compares the ASCII value of the letter ("A" = 65, "a" = 97, obviously the order of results will be totally different.
Just as important is Option Explicit. This means that every function and variable including the names of fields on forms, and a whole lot more, must be declared before those variables can be used in your code.So when you deleted "Option Explicit", you said "Don't check for errors at compile time" ... and it didn't.
Phil
  100+
P: 161

The error checking part makes sense. So the two above options just go once per page I put code, not above every code? I think I can get most of this correct with the info you have gave me. Ill need help on assigning Cbo options to fields for the agg part put I can start a new topic so we can get this closed since it has morphed from the topic.
Thanks
    Question stats  viewed: 1984
 replies: 43
 date asked: Feb 9 '19
