• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3272
  • 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 ?

Thanks
0
DoveTails
Asked:
DoveTails
  • 2
  • 2
1 Solution
 
hitsdoshi1Commented:
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
 
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.
0
 
hitsdoshi1Commented:
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
 
Jeffrey CoachmanCommented:
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
 
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 !
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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