What's the equivalent of Excel's "Field Settings"

[kmacd6951]

In an Excel pivot table, you can right click on a column in the pivot table, select Field Settings and change the column from "Count" to "Average" (for example). I am wanting to be able to do this in Access, but can't find how...

I have 4 columns in my data area of my access pivot table:
Count of Cases, Count of Age, Sum of Last Edit and Sum ot Cooked

I want the middle two columns to actually be averages rather than Count or Sum. Is there a way to get this to happen without exporting to Excel?

Here is my SQL if that makes any difference:

SELECT SWB_SW_CASE.SWCASEID, SWB_SW_CUSTOMER.AMNEMONIC, SWB_SW_CASE.SWTYPE, Team_Type.Team_Type, SWB_SW_CASE.SWSTATUS, SWB_SW_CASE.AISSUETYPE, SWB_SW_CASE.SWDATECREATED, SWB_SW_PROD_RELEASE.SWNAME, Employee_Team.SWLOGIN, Employee_Team.TEAM, SWB_SW_CASE.SWNOTE, SWB_SW_CASE.SWRESPONDBY, IIf([SWRESPONDBY]<Date() Or IsNull([SWRESPONDBY]),1,0) AS COOKED, GetWorkDays([SWB_SW_CASE]![SWDATECREATED],Now()) AS AGE, SWB_SW_CASE.ALASTEDITEDDATE, GetWorkDays([SWB_SW_CASE]![ALASTEDITEDDATE],Now()) AS [LAST EDIT], [Employee_Team Query].ALASTCOMMAFIRSTNAME, SWB_SW_CASE.APROBLEMTYPE, SWB_SW_CASE.AECOMMERCETYPE, SWB_SW_CASE.APAYER
FROM ((SWB_SW_PROD_RELEASE INNER JOIN SWB_SW_INST_PRODUCT ON SWB_SW_PROD_RELEASE.SWPRODRELEASEID = SWB_SW_INST_PRODUCT.SWPRODRELEASEID) INNER JOIN (((SWB_SW_CASE INNER JOIN SWB_SW_CUSTOMER ON SWB_SW_CASE.SWCUSTOMERID = SWB_SW_CUSTOMER.SWCUSTOMERID) LEFT JOIN Team_Type ON SWB_SW_CASE.SWTYPE = Team_Type.Type) LEFT JOIN Employee_Team ON SWB_SW_CASE.AIDXCASEOWNERID = Employee_Team.SWPERSONID) ON SWB_SW_INST_PRODUCT.SWINSTPRODID = SWB_SW_CASE.SWINSTPRODID) LEFT JOIN [Employee_Team Query] ON SWB_SW_CASE.SWASSIGNEDTO = [Employee_Team Query].SWPERSONID
WHERE (((SWB_SW_CUSTOMER.AMNEMONIC)<>"PRO" And (SWB_SW_CUSTOMER.AMNEMONIC) Is Not Null) AND ((SWB_SW_CASE.SWTYPE) Is Not Null) AND ((SWB_SW_CASE.SWSTATUS)<>"Closed" And (SWB_SW_CASE.SWSTATUS)<>"Deleted") AND ((SWB_SW_CASE.SWDATECREATED)>=#1/7/2004#) AND ((Employee_Team.SWLOGIN)<>"mmcguire") AND ((SWB_SW_CUSTOMER.ASD)=1));

 

[Norie]

Where are you actually summing? I can't see any reference to sum or count in your SQL.

When you are in the Query Design view do you not have the totals dropdown with Group By, Sum, Average, Count etc

 

[kmacd6951]

Re: What's the equivalent of Excel's "Field Settings&qu

Ahhh... OK.
I wasn't summing at that level, it was in the Pivot table view.

So when I run the query, I get all my results, and then I was putting it into pivot table view so I could see the Case Backlog by Case Owner basically.

The defaults for my pivot table view were "Count" on some columns and "Sum" on others.

Would it help if I email you a copy of what my pivot table looks like?

Thanks for your help!
Katie[/img]

 

[Norie]

Can't you use a crosstab query? They almost equivalent to Excel pivot tables.