Transpose data with TransferSpreadsheet or OutputTo commands

[BallunarCrew]

I am trying to output data from a table using either TransferSpreadsheet ot OutputTo. I have coded it with both methods and both work fine. I need to transpose the data during this spreadsheet creation. I have searched on the net and found code that I think might do it at bytes.com, searching for "transferspreadsheet transpose" but the article will not open - it forces my browser to close. Any ideas on how to code this transpose? I am working in Access 2007.

 

[vbajock]

By transpose, do you mean a "crosstab", the Access term for a pivot table?

 

[BallunarCrew]

I think that is the right term. By default, it puts the column headers across the top of the spreadsheet and the data below each column header. I want the column headers to be down Column A instead of across Row 1. Just the same as if (in Excel) I copy and Paste Special and select Transpose.

 

[dhookom]

BallunarCrew,
Please provide your table and field names, some sample records, and how you want them to appear in your spreadsheet.

Duane
Hook'D on Access
MS Access MVP

 

[BallunarCrew]

table name: ManagementData
fields: (there are about 50 so I will only list a few)
OV - integer
NP - integer
Pct Per PT - double - percent format
Actual Collections - double - currency

There is no primary key on the table. I have spaces in the field names so that it will look good on the output. TransferSpreadsheet ignores captions added to columns in a table. I do not know if OutputTo also ignores them - did not try since I have it working like this.
I have code that gathers these pieces of data and puts it into a table to be output to Excel.
This is run for a date range specified by the user and the table is cleared out each time so there is only one record in the table at a time. A simple output of the table looks like this:

OV NP Pct Per Pt Actual Collections
20 5 69.2% $100.00

What we want is:
OV 20
NP 5
Pct Per Pt 69.2%
Actual Collections $100.00

I did find that using OutputTo rather than TransferSpreadsheet does the formatting (dollar sign, percent sign, etc) very nicely.

 

[dhookom]

I expect you can use a union query like:

SELECT "OV" as TheWhat, [OV] as TheValue
FROM ManagementData
UNION ALL
SELECT "NP", [NP]
FROM ManagementData
UNION ALL
SELECT "Pct Per Pt", [Pct Per Pt]
FROM ManagementData
UNION ALL
--- etc ---

I never name my fields based on what I want users to see. If I want to export some records, I will use a query where I can alias the actual field name with some other title.

Duane
Hook'D on Access
MS Access MVP

 

[BallunarCrew]

Thanks for this reply. I have not had a chance to see if this will work for us yet but intend to.