Help with Pivot table subform

[Becca]

Hi

I am usually pretty good wih pivot tables in Excel,and had no issues in Access 97, but using PTs in Access 2000 has got me stumped.

I have a query with the (simplified) feilds [activity] [Year] [totalPrice]. The source of the query is another query that has a parameter linked to a parent field on the main form.

I want to format the information into the final query into a dynamic pivot table that I can insert into a subform. In particular, I will want to have [activity] as a row field, [year] as the column field and [totalprice] in the data area.

I have tried the cross tab query and creating a subform from this, however, as you scroll through the parent forms, different years may be impacted, and a subform created from the crosstab seems to 'lock in' specific year feilds into the subform format.

I then tried making a pivot tale form with little success, as when i put my feilds where i want them I get the error message "The quey could not be processed: the data prvodier didn't provide any further error information". The only time I can get data to come up is if I drop al feilds into the data area, but this doesn't give me the format I want.

So I am at a loss here as to what I am doing wrong, as I am doing the ol' drag drop routine as I always do and I don't even know if I can then use this as a subform to display my results prettily. Does anyone have any ideas?

Much obliged..

Rebecca

 

[Becca]

In case it helps in jogging someone's memory of having a similar problem - here is a screen sump of the error message. It seems to happen no matter how simple or complex my table is. I am starting to wonder if I am just missing the point in how to use it in 2002, or if there is just somethign wrong with my Access version and I need to reinstall, or if I need to do this sort of stuff in SQL and forget about using the wizards.