Excel Formulas in Queries

[Collins]

I know very little about queries. But I know queries are where we can do calculations. I currently have an Excel Spreadsheet with several formulas in it to do calculations. Is it possible to use the formulas from excel in a query? Or is it more complicated than that?

[June7]

Cannot use Excel functions in Access queries. Access has its own set of intrinsic functions and not as many as Excel. Often have to build custom functions in VBA to emulate a simple function in Excel. VBA can use Excel functions but don't count on same results.

I had to replicate matrix multiplication in Access. I tested 2 methods (one using Excel functions and one not) in Access against the Excel functions in worksheet. 3 different results.

[Collins]

Terrific...

[June7]

Access is a data manager, not a number cruncher. Excel is better for statistical analysis.

Right tool for the right task.

[Collins]

I agree totally. But we have several different spreadsheets using all the same data but crunching different reports and things are getting re-entered incorrectly and spreadsheets are getting missed all together. I thought I could enter the data once in a database and then just spit out the 8 different reports it would be easier. I'm finding it may not be. Like you said. Access isn't a number cruncher.

[June7]

As I noted, can do it in Access but for some intense calcs results might be different. And might have to get creative with building custom functions. Excel functions can be called in VBA but I found even that produced a different result, at least with matrix multiplication which involves some very small (lots of decimals) numbers. Access just doesn't have the precision for this.

Have to just test it out and decide what you can live with. We decided we could live with the Access results not using Excel function calls.

[Collins]

Well let me ask you this. Can I at least reduce the number of decimals it goes out to and round up to the nearest one hundredth?

[JoeM]

How complex are these calculations? Access can handle many simple calculations without much issue.
And as June said, building Custom Functions for ones that are a little more complex is also an option.

[June7]

Can do rounding.

Be aware the Round() function is Access is different from the Round() function in Excel.

Access follows even/odd (banker's) rule.

Round(3.145, 2) = 3.14

Round(3.146, 2) = 3.15

Round(3.135, 2) = 3.14

[Collins]

How complex are these calculations? Access can handle many simple calculations without much issue.
And as June said, building Custom Functions for ones that are a little more complex is also an option.

They are not overly complex. I don't think. I mean. It's mostly adding and subtracting. The sum of three years numbers. and other kinds of things.

Here is the string I'm trying to get Access to do right now.

Area Sq In: ((([2c#14]/2)*([2c#14]/2))*3.14)

It seems to be working. But it was several places past the decimal. I just found the properties button and changed the format to Standard with 3 decimals. That seem to fix it.

Is that string very complicated for Access to crunch? Or does it have to be much worse? or much simpler?

[June7]

How does the result compare to Excel calc? I don't know what a threshold for variance would be. I only know matrix multiplication demonstrated variance. And matrix multiplication is complex.

Setting format property does not change the calculated result, it is just a display setting.

[Collins]

How does the result compare to Excel calc? I don't know what a threshold for variance would be. I only know matrix multiplication demonstrated variance. And matrix multiplication is complex.

Setting format property does not change the calculated result, it is just a display setting.

So far the numbers are matching. I had a feeling Matrix Multiplication was complex because I have never heard of it. LOL...

I did find one string that is pretty long and I have to decipher what cell in excel the formula is referring to and substitute the field I created in Access.

[June7]

Matrices are often introduced at the end of trigonometry before jumping into calculus.

[Collins]

Matrices are often introduced at the end of trigonometry before jumping into calculus.

Yep... that's above my pay grade. LOL... I'm barely smart enough to figure out Access.

Thanks for the help June7...

[June7]

I found code on the web and plagiarized. The procedure isn't very long but I never would have figured it out by myself.

Good luck with your project.