Fixed Pivot Table Row Headings
 

Hi Team

In an Access Crosstab Query, (which to my way of thinking is similar to a Pivot Table) you can set/fix the Column Heading values.

For instance, if I have weekly data, and not each week has data in it, it will only show those columns/row with data, but! if you go into the properties section, you can manually enter the headings so that is shows regardless of data for that week.

I cannot find anything I can do to manipulate Row or Columns in Excel, can this be done via VBA at all..??.

As I only have 2 weeks of data ( Weeks 46 & 47 of this financial year ), the PT only shows 2 rows of data for these weeks:

WeekNo_______Site1_____Site2_____Site3_____Site4__ ___Site5
_46__________01:00_____06:00_____02:00_____05:00__ ___09:00
_47__________02:00_____09:00_____06:00_____01:00__ ___02:00

Whereas I would like it to show Weeks 1 through to 52 irrespective of there being data, so it would look something like:

WeekNo_______Site1_____Site2_____Site3_____Site4__ ___Site5
__1__________00:00_____00:00_____00:00_____00:00__ ___00:00
__2__________00:00_____00:00_____00:00_____00:00__ ___00:00
__3__________01:00_____06:00_____02:00_____05:00__ ___09:00
To
..49__________00:00_____00:00_____00:00_____00:00_ ____00:00
..50__________01:00_____06:00_____02:00_____05:00_ ____09:00
..51__________01:00_____06:00_____02:00_____05:00_ ____09:00
..52__________00:00_____00:00_____00:00_____00:00_ ____00:00

Any thoughts or comments are appreciated.

TIA
Mark.