[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Create Calculated Total MS Access Pivot Table

Posted on 2010-08-26
Medium Priority
3,230 Views
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
Question by:DoveTails
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 9

Expert Comment

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

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

hitsdoshi1 earned 2000 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))

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

Good Luck!
0

LVL 74

Expert Comment

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)

;-)

JeffCoachman
0

LVL 5

Author Closing Comment

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

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
###### Suggested Courses
Course of the Month13 days, 17 hours left to enroll