Export to Excel and Transpose

[Steven547]

Hey guys. I have an Access Query that displays information. I need to export that to Excel, but transpose it in excel...

When it is run in access and displayed, it looks like:

Company Area Street
UPS Kane Allen
FedEx Cook Daniel

I would like the Company, Area, and Street to be the ROWS and the data to become the columns. I found one topic here, but wasn't understanding the "normalize" crosstab. For a crosstab, I need a value, but there are no values, it's just raw data out of a table.

To export to excel, I have:
DoCmd.TransferSpreadsheet acExport, , "qryEffectiveTrustees", "TestXLS.xls", True

How would I do this? Would I do it in Access first then export as above?

Thanks.

 

[dhookom]

What would the data look like in Excel? Is there a primary key in your Access query? If you had a primary/unique field, you could use something like:
[quniNormalizing]

SELECT ID, Company AS TheVal, "Company" AS TheCol
FROM AccessQuery
UNION ALL
SELECT ID, Area, "Area"
FROM AccessQuery
UNION ALL SELECT ID,Street, "Street"
FROM AccessQuery;

Then create a crosstab based on the union query

TRANSFORM First(quniNormalizing.TheVal) AS FirstOfTheVal
SELECT quniNormalizing.TheCol
FROM quniNormalizing
GROUP BY quniNormalizing.TheCol
PIVOT quniNormalizing.ID;

The result should be
[tt][blue]
TheCol 1 2
-------- ------- --------
Area Kane Cook
Company UPS FedEx
Street Allen Daniel
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[Steven547]

Ok...what does the "union" or "normalize" mean? I've actually never hear those terms before...? maybe that is where I'm getting confused..

and the excel would look like what you posted. The Rows in Access become Columns in excel.

Also, there is no primary key in the table. It's just a basic table for input.

 

[dhookom]

IMHO, every table needs a field or fields that identify uniqueness. If your table doesn't have one then add an Autonumber field and make it a primary key. Then try my solution as it worked well for me.

You can find out what a union query is by creating one as per my sample. There are lots of resources on the web that describe union queries and also normalizing. I wouldn't consider your original table/query un-normalized if this was your actual data.

Duane
Hook'D on Access
MS Access MVP