Unanswered: Crosstab, Pivot Table, Transposing Record Sets
Here we go again. Does Microsoft provide a way within Access 2007 to accomplish crosstab reporting of a record set? I am not looking to report calculated fields. I want to report transposed selected fields of a record set.
A typical record set is comprised of six (6) records. Let us say that each record consists of three fields. I want to transpose (crosstab) the record set and achieve a horizontal listing of at least one of the fields from each record; thus, the chosen field from each record becomes a new field in a transposed record. And in the end, we have transposed six(6) records into one(1)record for reporting purposes.
This transposed record will be used in list the fields from the original six(6) records from left to right on a single line within the Detail Area in a standard report.
I can achieve this in a Pivot Table View available in Access 2007 at least I think I have; however, I cannot get the view in the Detail Area of the standard report.
If at all possible, I do not want to start programming to achieve this.
Any ways out for me? I can explain more but it already a long post.
Create a Cross Tab query and use it as the report's datasource. When you create the Cross Tab query you have to choose which fields will continue to be Row Headings, which become Column Headings and which are Data Values. But it definitely should be possible. I use Cross Tab queries all the time.
I have been directed to get a few other tasks done prior to revisiting this one; however, I was able to use report page formatting to arrange the data across the page instead of down the page by using the column options in page layout. This record report arrangement will not show when viewing the output in "Report" view but does show under "Print" preview mode. I will try a sub-report method and embed the sub_report into the main report-hope the page layout will hold true when doing this. I also hope this will provide a work around until I have more time. I don't like it much since I use the "Report View" when working with data and report writing.