Question:
There's basically two ways of preparing databases pre-Pivot Table.
You can keep all your data points (sales figures) in the same Row and use the Column (Field) Heading then go straight to a PT. This keeps the number of Excel Rows at a minimum since all data points are in the same Row.
The other way is to transpose the Column Heading (Fields) of the data points so that all Data Point Column Headings (Fields) are in one column and the Data Points (sales Figures are in an adjacent new Column and a new database. This creates uniqueness.
Drawback to method two: If you have 11 data points in one Row, and you transpose the Field Headings, you now use up 11 Rows for each Product Name since each data point is in its'own unique Row. This fills up Excel real fast.
The first method, which is quick to construct and convert to a PT takes up minimal Excel Rows.
The second method, which PTs prefer, can use many, many more Rows, including quickly approaching 65k and requires a 2nd database.
Now, the main question: Using method one, when I go from the origihal data base to the PT, my Column Fields disappear when I deselect them. They are still on the Wizard, but I always have to go back and reinstall.
How do I get around this? Where do they go and why?
The second method, which is the cleanest, creates a second database, then converts to PT. The Fields are are always there but I can't do Calculations. PT likes this method but it pushes Excel specs and forces me to go to Access for data storage.
Yes, this is a long Post, but I need to understand how to get around these constraints, or fix my apparant lack of understanding.
Thanks ahead of time.
Henry (mtaylor2@satx.rr.com)
There's basically two ways of preparing databases pre-Pivot Table.
You can keep all your data points (sales figures) in the same Row and use the Column (Field) Heading then go straight to a PT. This keeps the number of Excel Rows at a minimum since all data points are in the same Row.
The other way is to transpose the Column Heading (Fields) of the data points so that all Data Point Column Headings (Fields) are in one column and the Data Points (sales Figures are in an adjacent new Column and a new database. This creates uniqueness.
Drawback to method two: If you have 11 data points in one Row, and you transpose the Field Headings, you now use up 11 Rows for each Product Name since each data point is in its'own unique Row. This fills up Excel real fast.
The first method, which is quick to construct and convert to a PT takes up minimal Excel Rows.
The second method, which PTs prefer, can use many, many more Rows, including quickly approaching 65k and requires a 2nd database.
Now, the main question: Using method one, when I go from the origihal data base to the PT, my Column Fields disappear when I deselect them. They are still on the Wizard, but I always have to go back and reinstall.
How do I get around this? Where do they go and why?
The second method, which is the cleanest, creates a second database, then converts to PT. The Fields are are always there but I can't do Calculations. PT likes this method but it pushes Excel specs and forces me to go to Access for data storage.
Yes, this is a long Post, but I need to understand how to get around these constraints, or fix my apparant lack of understanding.
Thanks ahead of time.
Henry (mtaylor2@satx.rr.com)