• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3343
  • Last Modified:

Create Calculated Total MS Access Pivot Table

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 ?

  • 2
  • 2
1 Solution
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 ?
DoveTailsAuthor Commented:
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.
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:


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

So as for your eg:


Good Luck!
Jeffrey CoachmanMIS LiasonCommented:
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.


DoveTailsAuthor Commented:
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 !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now