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.)
Before:
http://img384.imageshack.us/img384/4339/sourcedata4iy.jpgAfter:
http://img137.imageshack.us/img137/3656/formatteddata2cp.jpgHere 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:
http://img384.imageshack.us/img384/1153/variationsource0fe.jpghttp://img384.imageshack.us/img384/5994/variationformatted8in.jpgWhile 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.
