Solved

Create Calculated Total MS Access Pivot Table

Posted on 2010-08-26
5
2,934 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

747 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

12 Experts available now in Live!

Get 1:1 Help Now