Crosstab query and create new query

[thart21]

Does anyone know why Access 2007 locks up when I try to build a new query off of a crosstab query? I can't even get it placed on the query design grid, it just sits there and I have to shut down to get out of Access. If it finally does drop it in (1 time out of 10), I lose it when i try to save the query. Is it just that I can't create a query based on a crosstab?

Just curious if I need to find a better way.

Thanks!

 

[dhookom]

I would make sure you have all the service packs installed.

Do you have the Column Headings property updated in the crosstab? Do the source tables of your crosstab contain thousands of records? Does your crosstab have a criteria?

Duane
Hook'D on Access
MS Access MVP

 

[thart21]

Thanks for the quick response.

Didn't have the Column Headings property set, I did that and no difference, just 5 columns.
Yes, the crosstab is pulling 57k records with no criteria. I can run it in under a minute by itself but it just doesn't want to be joined with my select query or another crosstab for that matter.
Thanks!

 

[dhookom]

Are the fields used for column and row headings indexed?

Duane
Hook'D on Access
MS Access MVP

 

[thart21]

No, the fields are not indexed and I was just told that IT will probably not index the Oracle tables for me. I'll need to start that fight with them soon. Thanks for the help.

 

[dhookom]

I missed any earlier reference to Oracle. Is all of your data coming from Oracle? If so, I would probably attempt to create a pass-through query that looks like a crosstab. It's difficult to provide a specific solution without knowing something about your tables and SQL of the crosstab.

Duane
Hook'D on Access
MS Access MVP