manipulate a pivot table

[yogi]

Is there a way that you can manipulate a pivot table, (example adding more item in a list) after the pivot table is created.

 

[Dennisk]

If you mean a cross tab query then yes you can open it in design mode just as you can open any other query .

 

[Simon_MT]

You can use the PivotQuery like any other query and link it into another query however this very slow as Access has to work out what fields are in the PivotQuery i.e run the query first. For this very reason, I stopped using Pivot Tables and now hand code these extrapolated fields with iif statements.

 

[yogi]

SIMONMT

do you a example or do you know of a book or website that i could take a peak at.

another question.

i use Access in pivottableview to create what I need and export into xls.
Work find but I want to used the pivot table to populate a new formatted worksheet. do you have any idea how to do this? Or I'm going the wrong way about this, any help would be very appreciate

thanks

 

[Simon_MT]

Sorry, for my tardy response but I have been away. I given up on pivot tables long ago because they are slow and inflexible only one column can be extrapolated at a time. Although there are many different views on the method I now qualify the query instead i.e.

Age180: Sum(IIf(Now()-[Client Sale Date]>180,[Local Amt]-[Local Pay],0))
Age150: Sum(IIf(Now()-[Client Sale Date] Between 150 And 180,[Local Amt]-[Local Pay],0)) etc.

I have a straightforward totalling query. This method was used because I wanted to add Balances using [Foreign Amt] and [Foreign Pay].

It wasn't all that hard and its much faster.

Simon

 

[yogi]

if you have a worksheet that contains 100 cells to populate, you would use a query plus the iff statement that you indicate in your previous email.
don't you think that woulod take a long time to do?