PivotTable View - Query - Several questions

[lameid]

I have what I consider a stupid requirement and I think a pivot Table View could be my holy grail... Using Access 2010.

In pivot table view there is an Export To Excel button on the Design tab in the Data group. Anyone know of a way to use VBA / Macro for the same or the reference to add it to a ribbon?

Secondly anyone know a way to hide column values without filtering? In essence I need to show all the values multiple times but for WTD, MTD and YTD column sections; so I do not want to filter out WTD and therefore limit YTD. I am thinking add a flag to the underlying query and ideally hide the false versions and use those as column headings. - I can at least make this work with normalizing the data (nothing like being brought in after it is wrong).

 

[SkipVought]

hi,

So what is the REAL objective, PivotTable being only a tool to accomplish something greater.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lameid]

I have data that can be grouped by several grouping type fields like department or region that depending on what iteration will group the data (row heading). That is likely I will have a department report / pivot and another for region. I also have a date field involved, we'll call it DateX here and will be involved in column heading grouping. Lastly I have a series of values I want to sum. We'll call them poll questions. Currently the poll questions are denormalized or I have columns that are like poll_answer_1, poll_answer_2 etc. To make things more interesting the poll can be open or closed so I need another set of logical columns to show the completed poll data vs. 'all answers' (the fact that the poll questions can change over time and the complexity / maintainability of the query are both reasons to kick it over to a one to many relationship).

All poll answers are yes/no - (technically bit, SQL Data).

The end result is a report that will display headers something like the below with the count of true answers...

WTD MTD YTD
Region A1 Tot A1 Complete Total A1T A1CT A1T A1CT


I am thinking I can add a flags based on DateX to display MTD WTD and YTD flags to then in turn use to display the data in the PivotTable view.... I just do not want to see WTD = 0 but do not want to filter it out because it would break YTD totals.

Naturally my data really isn't poll data and I am sure the analagy breaks down somewhere.

After posting I did realize I could just export the data the one time to get the Pivot layout and subsequently just update an Excel template's data (can't refresh native to Excel as permission issues come into play and I do not have that kind of control nor patience for bureacracy)

 

[SkipVought]

I just do not want to see WTD = 0 but do not want to filter it out because it would break YTD totals.

How would that kind of display work for multiple regions, where only ONE region has a WTD value but ALL regions have YTD values, for instance?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lameid]

One region would have a WTD total and the others would be 0/null... Assuming Null in data and I'll trap with a function in the base query if and when someone needs to see 0.

So I am thinking my result set looks something like

Region WTD MTD YTD Data colums...
Here 1 1 1
There 0 0 1
Neither here nor there 0 1 1

Except that I may pull all the detail so there may be multiple lines for each region.... In actuality I will probably have all regions plus a 'None' Region if an a 'pollster' does not have region specified (I love havig a Full outer join in SQL Server for asinine things like this).

 

[SkipVought]

Did you answer YOUR QUOTE? I did not catch that.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lameid]

Did you answer YOUR QUOTE? I did not catch that.

Uh Oh... you seem to think that it did which means that you think and therfore it is true that you can only add a column to aggregate / value once in a pivot and I therefore need to repeat the values either with a union or temptable in my datasource...

Seems like the best I could hope to do is group by YTD, MTD then WTD.

A view in my opinion much more useful than the one requested.