Correlation coefficent (CORREL in excel)

[AJ]

I would like to run a correlation coefficent in ms access against some
tables. Can I do that in a query or do I need to create a VBA procedure. I
have a sql server query that works but not sure how to incorporate it into
access.
It is the same as the CORREL function in excel.

Does anyone have any examples?

Any help would be great.
AJ

 

[Wolfgang Kais]

Hello AJ.

I would like to run a correlation coefficent in ms access against
some tables. Can I do that in a query or do I need to create a VBA
procedure. I have a sql server query that works but not sure how
to incorporate it into access.
It is the same as the CORREL function in excel.

Does anyone have any examples?

Let's assume that you have a table (TheTable) with two columns X
and Y that contain the numbers you want to use in the calculation,
then the following access query will calculate the desired value:

SELECT (Avg([X]*[Y])-Avg([X])*Avg([Y]))/(StDevP([X])*StDevP([Y]))
AS CORREL FROM TheTable;

 

[Wolfgang Kais]

Hello "AJ".

Have you tried my suggestion?
Did it work?
Or did it not work?
Supposing it's because I kind of expected a little reaction, I'd like
to let you know what's driving me (and I guess many others too)
anwering questions on the internet.
It's not that I want my name appearing once more on the internet.
It satisfies me to enable someone the solve his/her problem.
How can I know whether my attempts are helpful? - From the reactions.
What if don't get any feedback? - I imagine the smile on your face.
But did you even read my answer? - I hope so, but I can't be sure.
So if it's feedback that causes satisfaction and I don't get any?
....
In this case, the correl function would return #N/A.

 

[AJ]

WOLFGANG THANK YOU!!
I am very new to access and am tackling a project that I think is a little
difficult. I am on my third access/vba book tyring to learn how to do
everything.
I am still working on putting it into a procedure, etc but the code itself
works great. Thank you and I am sure you will see more posts from me soon as
I try to convert sql server code to vba access.
Thanks again