Can you transpose the columns and rows in a query result?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Excel you can copy/cut and paste with the transpose option to flip the
columns and rows. I am looking for a way to do this in with a query for
Access. If there is an easy way to execute an Access query and then do the
transpose by writing code in Access or using an Excel macro, this too might
work.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In Access: You can export the data from a table or a query to Excel
(File > Save As... etc.)

In Excel: You can import data from an Access table or a query (Tools >
Data > Import Data ... etc.). [Not too sure about the menu sequence
since I don't use Excel that much, but I do know it is under the Tools
menu.]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQdsvSYechKqOuFEgEQLezACgxBGaN6Bss2Ev8EDs9KeTQuFJP6QAnRXh
YHo3/YM3bNPI5f2USEDttejp
=NLcr
-----END PGP SIGNATURE-----
 
In Access, you can create a UNION query that normalizes your table and then
build a Crosstab query based on the union query that transposes the original
query.

If you provide some sample data with field names and desired output, we
could be of more assistance.
 
There is way to attach examples with this forum. And the tables I am dealing
with each has a large number of fields. This is the reason that I want to
transpose the columns and rows for readability. Can we use a simple example
instead? Suppose I had the following query:

SELECT FirstName, LastName, Address, City, State, Zip
FROM tblAddress
WHERE LastName Like("A*")

Assume these are all of the fields in the table and that it produces a 3 row
result. How would you use your suggestion to flip the columns and rows like
the following?

FirstName Ken Herman Bugs
LastName Donhowe Munster Bunny
Address 323 ... 1313 ... 102 ...
City Westmont Chicago Hollywood
State IL IL CA
Zip 60559 60606 90210
 
Assuming you have a primary key field of ID create a union query
"quniAddresses":
SELECT ID as ColHead, "LastName" AS RowHead, LastName AS TheVal
FROM tblAddresses
UNION
SELECT ID, "FirstName",FirstName
FROM tblAddresses
UNION
SELECT ID, "Address",Address
FROM tblAddresses
UNION
SELECT ID, "City",City
FROM tblAddresses
UNION
SELECT ID, "State",State
FROM tblAddresses
UNION SELECT ID, "Zip",Zip
FROM tblAddresses;

Then create a crosstab from the union query:
TRANSFORM First(quniAddresses.TheVal) AS FirstOfTheVal
SELECT quniAddresses.RowHead AS Expr1
FROM quniAddresses
GROUP BY quniAddresses.RowHead
PIVOT quniAddresses.ColHead;
 
Thanks for the help. This suggestion will be valuable. But, I don't think it
will work for my application. There are too many fields. I'm sure there is a
limit to the number of UNIONs you can do. And my tables have mutiple columns
as the primary key. I think my best bet is to write code in Access that will
execute the query, export it to Excel, open the Excel file, then perform a
copy/cut and paste with the transpose option.
 
Your table sounds a bit un-normalized. Keep in mind the final result can
have a maximum of 255 columns in Access.
 
Duane Hookom said:
Assuming you have a primary key field of ID create a union query
"quniAddresses":
SELECT ID as ColHead, "LastName" AS RowHead, LastName AS TheVal
FROM tblAddresses
UNION
SELECT ID, "FirstName",FirstName
FROM tblAddresses
UNION
SELECT ID, "Address",Address
FROM tblAddresses
UNION
SELECT ID, "City",City
FROM tblAddresses
UNION
SELECT ID, "State",State
FROM tblAddresses
UNION SELECT ID, "Zip",Zip
FROM tblAddresses;

Then create a crosstab from the union query:
TRANSFORM First(quniAddresses.TheVal) AS FirstOfTheVal
SELECT quniAddresses.RowHead AS Expr1
FROM quniAddresses
GROUP BY quniAddresses.RowHead
PIVOT quniAddresses.ColHead;

--
Duane Hookom
MS Access MVP
Click to expand...

You sir are a genius. Thanks for the suggestion!
 
Back
Top