PIVOT TABLE???

[jane30]

Hi, I use Access97. I created a Pivot table on a Select query. I want to use this Pivot table as my template and create more pivot tables on some other select queries. The field names in these queries are exactly the same. Can I do that? And how? Thanks a bunch.

 

[lameid]

The trick is to specify the column headings property in the QBE for the field that is your Row source... Simply right click the field and select properties.

For example, for a row source field of
Format([DATE_ID],"mmm&quot

I have a column headings property of
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

This is necessary if you always need to have the column headings even if they do not end up with values.

Once you've done that you can always copy and paste the column headings or look at the SQL view to see how it works there if you want to build your own SQL statement.

 

[jane30]

How can I have the SQL view for my Pivot Table??? I'm using Access97. Thanks alot

 

[MichaelRed]

Jane30,

It is against general policy to make duplicate posts in these fora. It careats additional traffic which some people will review multiple times. Since the membership contributes their time to answer requests, it is just more for them to do. I'm sure you -as a member of several of these fora can appreciate the difficulty for members in reviewing the same requests.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[lameid]

The button on the toolbar that lets you switch between design and datasheet views is also a dropdown. If you drop it down you can select SQL which will put you in SQL view.

 

[jane30]

Hi, Lameid! I'm able to switch between design view, SQL view only when to edit the query. Since I need to use the existing Pivot Table as template and create new Pivot table on another existing query, I need a way to change the query name. e.g. I created Pivot1 on query1. Then I copied Pivot1 to Pivot2. Then I go to Form design view and hope to the data source from query1 to query2. It didn't work. I tried your way: change the Pivot table's Row source to query2 under Design View and refresh the data, it did nothing. I tried to look for the SQL view for Pivot Form. I didn't see it. Did I do it wrong? Much thanks to you.

 

[lameid]

I find that if I just want to change a table/query source in a query, the easiest thing to do is to copy the text from the SQL view and copy it into wordpad. Next use the replace function in wordpad to replace every use of the table/query name and copy that text and paste it into the sql view of a new query. This is a the quick and dirty way.

Otherwise you can do something similar by concatenating an Sql string together programatically. You could then use the SQL statement to set the RowSource property of a form or report. Alternately you could use the sql statement to set the sql property of a querydef object. I guess it all depends on what your goal is.

Without knowing your VBA background I don't know if the second paragraph is helpful. If you need more help, please post your template SQL statement and describe exactly what it is you are trying to accomplish.

 

[jane30]

Hi, Lameid! I tried to modify the RowSource by copying and pasting my SQL statement over. Access started calculating. Then returned me an error message, says that "can't move or modify the part of Pivot table or insert cell within Pivot table...." After this error msg, gave me another one: the operation of OLE object failed. OLE server may not be registered. Reinstall OLE server. This is my first time running into this message. I've never had problem to run query, create forms and run my VBA code, except changing the data source of this Pivot table this time. Does it mean that Access97 can't change the data source for a Pivot table?

I need to create more than 10 Pivot tables on 10 different queries. The good part is all there queries are based on one master table. So, they have the same fields but extracting different data. I created one Pivot table, Pivot1 on query1. I formatted everything, including header, footer and page break, etc. Since I need to create so many Pivot tables, it's sort of tedious to create one by one. So, I copied the Pivot1 and named as Pivot2. so I don't have to go through the tedious formatting process. However, Pivot2's data source is still query1. It suppose to be query2. So, I went to the Pivot form design view and changed the Rowsource to query2. And Access returned me the error message mentioned above. How to work around this? now I'm using crosstab query instead. That'll be great if I know how to work around this. Your time and help are greatly appreciated.

 

[lameid]

Open Wordpad (Start, Programs, Accessories, WordPad).

Open Pivot2 which is a copy of Pivot1.

Switch to the SQL view of Pivot2.

CUT the SQL text out of Pivot2 and paste it in Wordpad.

In wordpad select query1 but make sure that you do not select any sqare brackets.

From the wordpad edit menu select replace.

The Find What field should have query1 in it.

In the replace with Field type in query2

Press the Replace all button.

Cut all the text in wordpad and paste it in the SQL view of Pivot2.

Switch to design view to make sure the query compiles.

Save the query.

Run it and make sure the result make sense.

Also check the QBE to make sure everything looks right.

 

[jane30]

Hi, I guess what I did wrong was I modified the RowSource, not the Record Source by copying and pasting the SQL statement to the RowSource. Now it worked fine after I change the Record Source. Thanks a lot.