Coefficient of Correlation in pivot table

Posted on 2006-06-13
Last Modified: 2008-02-01
Coefficient of correlation
I want to set up a pivot table with X and Y values where the pivot between those 2 values is the coefficient of correlation.
It is a geological table with coeff. of corr. between Gold and Copper etc.
My level of experties is intermediate in Access and rudimentary in VB.
Thanks for your help...Jakub
Question by:jakubkaiser
  • 2
  • 2
LVL 58

Accepted Solution

harfang earned 250 total points
Comment Utility
Hello jakubkaiser

This is not easily done in either a pivot table or a cross-tab query. The formula itself is simple:

Correll_G_C: (Avg(Gold*Copper)-Avg(Gold)*Avg(Copper))/StDevP(Gold)*StDevP(Copper)

The difficult part is getting that into a "pivot". Normally, each cell of a pivot contains the same calculation based on different subsets of the data (different groups of rows). You want different calculations in each cell on the entire data (differents pairs of fields).

If the values are stored in different fields, this is not possible. If they are stored vertically in a table indicating metal/value pairs for samples, it might be, although it's probably not the base solution in this case either.

Would you care to exlain whether you need these figures for futher computations or merely for display?


Author Comment

Comment Utility
Thak you harfang

It is for display only, eventually in a graph, the diagonal line being the r value. However the display in 3 columns is a first step (in a calculated field).
Sorry for the late answer-I am not familial with the way the forum works

LVL 58

Expert Comment

Comment Utility

For display only, I guess you can simply create textboxes in a form or report, in the header or the footer, while leaving the detail section blank. You could also create a query calculating the needed correlations and then use that as a base for a form or report.

The problem is that this would be with fixed fields. I don't see any easy way to make that dynamic. On the other hand, if you anyways want to substitute the r values in place of the diagonal with 1's, then this might be your best bet.

How are your fields stored? Or do you need more help at all?

About this site: First of all, welcome! I didn't realize this was your first question (^v^)

Any member can jump in at any time to add comments. Some questions get a very high activity for a short time and are solved within minutes. Others take longer with days between comments. After a given period of inactivity, a clean-up procedure kicks in.

You "task" after asking a question is to aknowlege answers, ask for further information, give feedback on what worked and what didn't and generally keep this alive until you get your answer...

Also remember that we don't know anything about your database or the problem at hand. For example, you say "between Gold and Copper etc.", which could mean many things (five fixed minerals? 2500 dynamic componds at the moment?), or "display in 3 columns", wich is not explained (which columns? why three? are they static?). It's not a problem as such, but it's good to keep that in mind.

Finally, if the answers aren't useful, try to narrow down what you want. A report? A form? An export to Excel? ... and expand your explations about your data structure and data (with sample data and sample output, for example).


LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility

Your data would have to be in a format similar to this:
Date      Metal      price
01/01/06      Gold      473
01/01/06      Copper      264
01/02/06      Gold      403
01/02/06      Copper      283
01/03/06      Gold      433
01/03/06      Copper      279
01/04/06      Gold      436
01/04/06      Copper      219
01/05/06      Gold      474
01/05/06      Copper      268
01/06/06      Gold      407
01/06/06      Copper      253
01/07/06      Gold      418
01/07/06      Copper      212
01/08/06      Gold      465
01/08/06      Copper      258
01/09/06      Gold      433
01/09/06      Copper      266
01/10/06      Gold      412
01/10/06      Copper      230
01/11/06      Gold      470
01/11/06      Copper      271
01/12/06      Gold      447
01/12/06      Copper      275

With this you could get a pivot table that looked like this:
Average of price      Metal            
Date      Copper      Gold      Grand Total
01/01/06      264      473      368.5
01/02/06      283      403      343
01/03/06      279      433      356
01/04/06      219      436      327.5
01/05/06      268      474      371
01/06/06      253      407      330
01/07/06      212      418      315
01/08/06      258      465      361.5
01/09/06      266      433      349.5
01/10/06      230      412      321
01/11/06      271      470      370.5
01/12/06      275      447      361
Grand Total256.5      439.25      347.875

This Pivot table shows the "average" Price by Day and Metal
Access does not contain a "Corellation" function for summaries in a Pivot Tables. (See harfangs Formula)

You could *Try* to add in that formula in as a Calculated Total or Detail Field, but that gets very complicated. And I doubt it would work anyway, based on the reasons harfang listed above.

Hope this helps in some small way as well
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility

Aslo check out FMSINC's "Total Access Statistics"

It contains lots of statistical function that *Might* get you close to what you want

But beware: $500.00US !

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now