Interpolation in Access using VBA

[karthikvlb]

Dear Friends,
I am quite new to Access DB. But I know some basics of VBA in excel programming. I have to use some data from access DB for my class project, but data’s are not available directly in access DB. Which are around different tables and requires manipulation. I will mention the problem below, could please provide me the possible solutions,
In table 1, I have standard values for corresponding standard wavelength.

Standard Wavelength	Standard Value
0.3	0.0005
0.32	0.0069
0.34	0.0122
0.36	0.0145
0.38	0.0187
0.4	0.0235

In table 2, I have an ID for which we are having values for a range of random wavelength.

ID 913	Wavelength 0.335	Values 0.013
913	0.34	0.048
913	0.345	0.121
913	0.35	0.248
913	0.36	0.476
913	0.37	0.588

Calculation: For each ID in table 2, I have to find missing values with respect to standard wavelength in table 1 through interpolation and multiply the corresponding values in table 2 with standard value in table 1 and do a summation of it.


The formula will be as follows
For each ID = Ʃ (table2.values(standardwavelength)) * (table1.standardvalue(standardwavelength))
could you please share your ideas on how to approach this following problem. Is there any sql query functions available to interpolate or do we need to create VBA function in access to perform this manipulation.

[June7]

I am not aware of an intrinsic interpolation function. Establish rules and build code.

There are only even values shown for standard wavelengths. So which standard wavelength record (0.36 or 0.38) should be referenced for given wavelength of 0.37? Always round odd values up? Or do you want to get the average of standard values for 0.36 and 0.38 and use that result to multiply with?

Should wavelengths be rounded to 2 decimal places? Ex: 0.335 round to 0.34 and associate with standard wavelength 0.34?

Round() function in Access and VBA uses even/odd rule, which means 0.345 would round to 0.34, not 0.35.

Cross-posted http://www.utteraccess.com/forum/ind...pic=2044472&hl=

[karthikvlb]

Thank you for your update friend.
Could you please share me some examples on how to buildrules in access.
The problem is that the values in table two (.466) won’tmatch exactly or nearest standard wavelength value in table 1(.300), sometimesthere might be huge difference (.066) and results are not quite good. So isthere any other function to find missing values in table 2 and compare it withtable 2.
Is it possible to run this procedure for all the IDavailable in table two any suggestion on this please.

[June7]

How would you manually determine the appropriate Standard Values to use for each table2 record? What decision steps would you follow? Such as:

1. first see if there is an exact match and return that standard value, if none go to next step

2. find the two table1 records that a given table2 wavelength falls between and calculate the average of the standard values and return that result (if an average is not satisfactory, and what you want is to determine a standard value that is on a line between two standard values as determined by position of table2 wavelength on a line between two table1 wavelengths, well that is nothing I've ever done but maybe this is just a percentage calculation)

3. multiply table2 value by the determined standard value

Once you establish the rules/steps/algorithm/pseudocode, write a VBA procedure (a custom function) to apply the logic. The code will require methods to look at and pull the table1 data. This could involve opening recordsets or DLookup() domain aggregate function and If Then Else conditional structure. The function could be called from a query or textbox.