Pivot Table and Crosstab Query Limitions

[amkazen]

Hi, I am using Access 2003 on a PC with Win XP, 2Gb RAM, etc., etc.
I have a table with 4 columns, and 73,766 records:
ID: Autonumber
Row: Text (to be used for my row headings), 458 unique headings
Column: Text (to be used for my column headings), 491 unique headings
Percent: Number, Double, 6 decimal places (to be used for my data)

I would like to create a pivot table to export into Excel, with the data
being totaled at the "Row" level while simulataneously being totaled at the
"Column" level, meaning there could be duplicates of row headings with values
under different column headings, and there could be duplicates of column
headings under different row headings. In addition, I also need to then to
have just the row headings displayed that have values that summed together
total more than 50% of the toal for each column. Confusing? Well, I am
close but the export to Excel is problematic. So, one question is "Is there
a limit to exporting a pivot table to Excel?" Also, "What would be the best
way to get just the row headings that total 50% or more?

I tried using the Crosstab query wizard but that failed with the message
"too many crosstab column headers (493)"

I created a select query and then went to "View", "PivotTable View", and
manually created my pivot table. That works to get the grand total but
exporting fails here. Also, getting just the top rows (summng to over 50%)
is perplexing. Thanks.

Andrew

 

[amkazen]

Here is the SQL:

SELECT Copy_IODirectRequireDetail.RowNAICS,
Copy_IODirectRequireDetail.ColumnNAICS, Copy_IODirectRequireDetail.Coefficient
FROM Copy_IODirectRequireDetail
GROUP BY Copy_IODirectRequireDetail.RowNAICS,
Copy_IODirectRequireDetail.ColumnNAICS,
Copy_IODirectRequireDetail.Coefficient;

 

[John Spencer (MVP)]

Access and EXCEL are both limited to 255 columns. There is no way around that
limit that I know of (Especially in Excel)

 

[Andy]

Ok, you confiormed what I believed was the problem. Thanks.

Now, how would I go about getting the rows that total to 50% to be the only
rows that are populated in the crosstab/pivot table query or report or form?

My thought is to write an intitial query that obtains those rows that meet
this criteria, and then do the crosstab query based on the initial query.
What do you think? And, any thoughts on how to write this code/criteria?

Andrew

 

[John Spencer (MVP)]

I apologize, but I have not the faintest idea of what you are attempting to do.
That being the case, I don't think I can really be of much help.