By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,222 Members | 1,728 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,222 IT Pros & Developers. It's quick & easy.

Form field populted by 2 form droplist to cross reference Query

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.
Feb 9 '19 #1

✓ answered by PhilOfWalton

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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Function GetCorrection(Temp As Single) As Double
  5.  
  6.     Dim MyDb As Database
  7.     Dim CorrectionSet As Recordset
  8.     Dim StrSQL As String
  9.     Dim TDF As TableDef
  10.     Dim Inti As Integer
  11.     Dim FieldName As String
  12.  
  13.     Set MyDb = CurrentDb
  14.     Set TDF = MyDb.TableDefs("TblCorrections")
  15.  
  16.     For Inti = 2 To 7                       ' We don't want the CorrectionID or Temperature columns
  17.  
  18.         Debug.Print TDF.Fields(Inti).Name
  19.         If TDF.Fields(Inti).Name = Me!CboSpecificGravity.Column(1) Then     ' Field name matches CboSpecific Gravity
  20.             FieldName = TDF.Fields(Inti).Name                               ' Name of S.G. field
  21.             Exit For
  22.         End If
  23.     Next Inti
  24.  
  25.     StrSQL = "SELECT DISTINCT TblCorrections." & FieldName & " FROM TblCorrections "
  26.     StrSQL = StrSQL & "WHERE Temperature = " & Temp & ";"
  27.     Set CorrectionSet = MyDb.OpenRecordset(StrSQL)
  28.  
  29.     With CorrectionSet
  30.         Correction = .Fields(0).Value
  31.         .Close
  32.         Set CorrectionSet = Nothing
  33.     End With
  34.  
  35. 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
Share on Google+
43 Replies


PhilOfWalton
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
Feb 10 '19 #2

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.
Feb 10 '19 #3

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.
Feb 10 '19 #4

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
Feb 10 '19 #5

PhilOfWalton
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
Feb 10 '19 #6

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
Feb 10 '19 #7

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.
Feb 10 '19 #8

PhilOfWalton
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
Feb 10 '19 #9

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.
Feb 10 '19 #10

PhilOfWalton
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
Feb 11 '19 #11

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.
Feb 11 '19 #12

PhilOfWalton
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
Attached Images
File Type: png Corrections.png (14.3 KB, 210 views)
Feb 11 '19 #13

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
Feb 11 '19 #14

100+
P: 161
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
Feb 19 '19 #15

100+
P: 161
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.
Feb 19 '19 #16

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
Attached Files
File Type: zip DBtest.zip (177.8 KB, 13 views)
Feb 19 '19 #17

PhilOfWalton
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
Attached Files
File Type: xlsx Williamson.xlsx (24.1 KB, 21 views)
Feb 19 '19 #18

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
Feb 19 '19 #19

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.
Attached Files
File Type: xlsx testMP.xlsx (29.1 KB, 23 views)
Feb 20 '19 #20

Rabbit
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.
Feb 20 '19 #21

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
Feb 20 '19 #22

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.
Feb 20 '19 #23

Rabbit
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.
Feb 20 '19 #24

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
Feb 20 '19 #25

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+((60-current temp)*multiplier).
Feb 20 '19 #26

100+
P: 161
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, 17 views)
Feb 20 '19 #27

PhilOfWalton
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
Attached Files
File Type: zip Williamson.zip (30.9 KB, 12 views)
Feb 20 '19 #28

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.
Feb 20 '19 #29

100+
P: 161
Phil I looked at your file, its just Form FrmCorrections with a table.
Feb 20 '19 #30

Rabbit
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.
Feb 21 '19 #31

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.
Feb 21 '19 #32

Rabbit
Expert Mod 10K+
P: 12,366
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.
Feb 21 '19 #33

100+
P: 161
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.
Feb 21 '19 #34

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

Try this instead.

Phil
Attached Files
File Type: zip Williamson.zip (149.5 KB, 15 views)
Feb 21 '19 #35

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
Feb 21 '19 #36

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.
Feb 24 '19 #37

PhilOfWalton
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Function GetCorrection(Temp As Single) As Double
  5.  
  6.     Dim MyDb As Database
  7.     Dim CorrectionSet As Recordset
  8.     Dim StrSQL As String
  9.     Dim TDF As TableDef
  10.     Dim Inti As Integer
  11.     Dim FieldName As String
  12.  
  13.     Set MyDb = CurrentDb
  14.     Set TDF = MyDb.TableDefs("TblCorrections")
  15.  
  16.     For Inti = 2 To 7                       ' We don't want the CorrectionID or Temperature columns
  17.  
  18.         Debug.Print TDF.Fields(Inti).Name
  19.         If TDF.Fields(Inti).Name = Me!CboSpecificGravity.Column(1) Then     ' Field name matches CboSpecific Gravity
  20.             FieldName = TDF.Fields(Inti).Name                               ' Name of S.G. field
  21.             Exit For
  22.         End If
  23.     Next Inti
  24.  
  25.     StrSQL = "SELECT DISTINCT TblCorrections." & FieldName & " FROM TblCorrections "
  26.     StrSQL = StrSQL & "WHERE Temperature = " & Temp & ";"
  27.     Set CorrectionSet = MyDb.OpenRecordset(StrSQL)
  28.  
  29.     With CorrectionSet
  30.         Correction = .Fields(0).Value
  31.         .Close
  32.         Set CorrectionSet = Nothing
  33.     End With
  34.  
  35. 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
Feb 24 '19 #38

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"
Feb 24 '19 #39

PhilOfWalton
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
Feb 24 '19 #40

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 wouldnt run with them. I do have one option compare database at the beginning of my calculations codes though.

It isnt using frmCorrections. I put it to the controls on my form. So I need to rename Corrections to the name of my form?
Feb 24 '19 #41

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.
Attached Files
File Type: zip DatabaseOutline.zip (67.1 KB, 10 views)
Feb 25 '19 #42

PhilOfWalton
Expert 100+
P: 1,430
Sorry, got the same error in both the updates. It should read
Expand|Select|Wrap|Line Numbers
  1. If Nz(cboSpecificGravity) = 0 Or IsNull(cboTemperature) Then
instead of
t
Expand|Select|Wrap|Line Numbers
  1. 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
Feb 25 '19 #43

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
Feb 26 '19 #44

Post your reply

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