Transpose data

[Dave]

How can a data table in Access where there are multiple fields and one record
be transposed to one field and multiple rows as in Excel copy/paste
(transpose)?

Thank you
Dave

 

[Douglas J. Steele]

There's no such capability in Access, since that really doesn't make sense
in the context of relational databases.

If all of the fields are of the same datatype, you could create a Union
query along the lines of:

SELECT Field1 FROM MyTable
UNION
SELECT Field2 FROM MyTable
UNION
SELECT Field3 FROM MyTable

but note that that would return them sorted by value. If you needed to keep
them in the same order, you'd have to do something like:

SELECT Field1, 1 As FieldNumber FROM MyTable
UNION
SELECT Field2, 2 FROM MyTable
UNION
SELECT Field3, 3 FROM MyTable
ORDER BY 2

 

[Ann]

There is a way to do this in Access 2002. It's Article 283875 in the
Microsoft Knowledge Base. I've never tried it but printed it when I came
across it because you never know when you might need something.