I have crosstab queries that I export from access into excel. I would love to figure out a way to automatically format this data into nice looking tables (ultimately published in a report in Word).
I have a before and after photo example you can refer to (this is my 1st attempt at utilizing free image hosting, so let me know if it is not working.)
Here are some criteria:
1) Have to be able to figure out what constitutes a distinct section of the table, so the borders can be applied properly. (I.e. thick borders around Grade 6, Grade 8 etc.)
2) Be able to color over a column (i.e., in this case, the State 2005 data)
3) Grey separators for table titles
4) appropriate manipulation of font size, bold, ital, justification
A pretty tall order. If you look in the sourcedata picture at column A, I have tentatively begun titling rows with labels that might be parsed to help interpret the nature of the row, i.e. "lblSpacer_TblTitle," "lblSpacer_Grade," "lblSpacer_SourceYear" in addtition to the labels I am using for the individual variables.
In the past, some have suggested pivottables, but when I tried, it would not let me put text data in the 'body' of the table, so I was at a loss of how to make it work (for example, in addition to the grade and year labels, some cells might need to read "n/a," if data were unavailable for past years). Another suggestion was that Crystal Reports should be able to make tables similar to this; I could not find references to this ability in the many Crystal tutorials I found online, I could not actually see a potent difference between Crystal's report engine and Access's.
What I would like is something that can automatically deal with different variations that might arise and format appropriately; A year might be missing, or an entire grade:
While I'm shooting for the moon, how about automatically adjusting column widths so that it makes the table have the same dimensions.
Am I going about this in the hardest possible way? Let me know that, too.