Solved

Create Calculated Total MS Access Pivot Table

Posted on 2010-08-26
5
3,028 Views
Last Modified: 2012-05-10
I have a pivot table with two fields in the detail section (main/center part of pivot table) for each employee.
     ie      Smith          Johnson         Davis
              1   4               3   3             5   4

Goal is to have a count of employees with one number greater than the other.

In Excel I can accomplish this with a formula:    =COUNTIF(B5:F5,">="&G5)

I'm only getting errors in Access with no success.  In fact, anything I try in "Create Calcuated Total..."   causes errors. ie( The query could not be processed: )

Can anyone tell me how to add a calculated total for a pivot table in MS Access 2007 ?

Thanks
0
Comment
Question by:DoveTails
  • 2
  • 2
5 Comments
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 33534788
There are several ways to accomplish this......can you write me what exactly are you looking for.....what I mean by that.....with the displayed number what should it calculate..or what does your formula calculates...or just write what values will your excel cell "B5 to F5 would have and what is value of G5?

from your eg... you want to sum 1 & 4 if > 3 ? so the answer should be 5 ?
0
 
LVL 5

Author Comment

by:DoveTails
ID: 33535280
I have two fields ... TargetScore .....   RankingScore.

The idea is an employee may have a skill ranking of 3 for a skill such "Writing SQL" and his desired target rank for that position may be 5.  Therefore training or coursework is needed for the employee to improve his skill set.    (He's only at 3 out of 5)

I want to add the number of employees at or below their target scores.  Therefore a manager can see how many employees that are in need of particular skills.

I want to count the employees...................CountIF
if their score is at or below target....................  (RankingScore <= TargetScore)

With the pivot table open....from Design Tab.....Tools group....I select Formulas.....Created Calculated Total.

On the Calculatin tab I cannot get any formula to work.    
I think I'm doing something rather stupid, but I just don't get it.  

Thanks for taking the time to look this over.
0
 
LVL 9

Accepted Solution

by:
hitsdoshi1 earned 500 total points
ID: 33535559
Well I am not exactly sure pivot table in access the best approach cos of limitations.....but in Access there is no "CountIf" function like excel. So instead, you might want to try:

=Sum(Iif([YourField]=No,1,0))

or =count(Iif([field]=value))

So as for your eg:

=Count(IIF([RankingScore]<=[TargetScore])

Good Luck!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33535847
I'm with hitsdoshi1.

It may be best to do these specialized calculations in the Query, then use the PT to summarize the data.
This is what PTs, were designed to do: ...Summarize, ...not calculate.
;-)

Also remember that PT's are a direct descendant of "CrossTab" queries in SQL (and Access)

You can typically use a Crosstab query to get the same results as a PT.
(and this may be easier to do your calculations as well)

There is a Crosstab query wizard in Access to help you get started.

;-)

JeffCoachman
0
 
LVL 5

Author Closing Comment

by:DoveTails
ID: 33536152
Thanks very much for the comments.
May not be cleanest method though it is working well:

Below: (IIf([TargetScore]>[SkillRating],1,0))
AtOrAbove: (IIf([TargetScore]<=[SkillRating],1,0))

Appreciate the time on this !
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Launch app from Access custom ribbon 8 33
Display label on subreport when NO DATA on subreport 4 22
Exporting Access Tables as CSV 3 24
combo box query 6 6
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

778 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