|
Subject:
|
Report - Transpose fields and records
|
|
Posted By:
|
Clive Astley
|
Post Date:
|
11/9/2004 11:49:34 AM
|
The "normal" layout of a report comprises fields along the x-axis and the records along the y-axis.
If the underlying query is in the above format, is there any way of having the fields along the y-axis and the records along the x-axis. Similar to the results of the Transpose function in Excel.
Any help appreciated. Thanks.
Clive Astley
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
11/15/2004 3:31:06 PM
|
I'm not quite sure what you mean. Do you mean when you use the report wizard and you ask for tabular format of data that you want the rows as columns and columns as rows vs. the default?
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Clive Astley
|
Reply Date:
|
11/15/2004 6:14:32 PM
|
Hi Greg,
Thanks for your reply. Yes, that's exactly what I want to do.
Clive Astley
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
11/16/2004 7:47:51 AM
|
Unless there's a third-party add-in to do that, the easiest way I can see doing that is to use the wizard to create the report in a strict columnar format (one column from the top down) and then you manually place all the fields. That is, don't use the tabular format at all, go strictly top down to place all your fields in the report, and then rearrange them yourself.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Clive Astley
|
Reply Date:
|
11/16/2004 9:24:07 AM
|
Thanks Greg. I haven't been able to fathom an alternative either. I've so far done it by brute force method of producing a DAO recordset to fill variables which then fill text boxes on the report. It works, but just thought their had to be a better way.
Latest problem is trying to produce a report on the fly from a crosstab query. The number of column headings in the crosstab varies with the data. But once one has written a report, it's layout is fixed forever.
Thanks gain. Best wishes,
Clive Astley
|
|
Reply By:
|
Ray Pinnegar
|
Reply Date:
|
11/16/2004 10:06:10 AM
|
Have you tried coding a pivot table from within VBA then you could provide any type of dynamic design?
Cheers Ray
|
|
Reply By:
|
Clive Astley
|
Reply Date:
|
11/16/2004 10:10:30 AM
|
Thanks Ray. But I don't really understand what you mean. Can you give me a bit of a clue please.
Thanks.
Clive Astley
|
|
Reply By:
|
penta
|
Reply Date:
|
11/16/2004 12:39:20 PM
|
Hi Clive!
I've been reading about how to do the transpose in an access db.There is no
clue in access books about it. I am not an expert programmer and i will let
them to implement it but a better way is u read ur report source to an array
and then u retrieve ur source from the array transposing the rows and columns.
I would be happy to see this happening.
Regards Penta.
|
|
Reply By:
|
Clive Astley
|
Reply Date:
|
11/16/2004 1:16:20 PM
|
Thanks Penta. I wonder if this is what I am doing already. Reading a recordset into an array and then using the array contents to fill text boxes on the report. But it seems a bit messy. Can you give me an example of what you mean please.
Many thanks.
Clive Astley
|
|
Reply By:
|
penta
|
Reply Date:
|
11/16/2004 1:49:58 PM
|
Clive!
As i have said i'm not an expert. Please read "Having problems retrieving an array to a table" on my ulterior posts. The main difference seems to be that i have used a table or a qry and the function "get rows" to read the source to the array. Then the idea is to have the inverse function (only experts would know how!) to retrieve the values from the array transposing columns and rows to a new table which will become the report source.
Hope this will help.
Regards P.
|
|
Reply By:
|
Clive Astley
|
Reply Date:
|
11/16/2004 2:06:06 PM
|
Thanks very much. I am beginning to see the idea now. Use a temporary table. I'll try some experimenting.
Thanks again and best wishes,
Clive Astley
|
|
Reply By:
|
Ray Pinnegar
|
Reply Date:
|
11/17/2004 6:22:09 AM
|
Using Pivot Table approach. As I see it, if you can produce manually a pivot table report of your data, and that is what its good at. You can then programmatically design the pivot table to match that specification. Not only that, but you can dymanically change the design to suit varying row and column arrangements.
Cheers Ray
|
