hi i have a table with a number of column
ie date, reasons, cause, incident

the files reasons, cause, incident hold a number entered by the user
ie date reasons, cause, incident
17/1/2012 10 20 12

what i would like to do is transpose the table so

i end up with

reasons 10
cause 20
incident 12

i am using the following sql
SELECT flddate,reason AS Reason
FROM Qry_injuries
UNION ALL
SELECT fldDate,cause AS Reason
FROM Qry_injuries
UNION ALL
SELECT fldDate,inciden AS Reason
FROM Qry_injuries;

what i get is

fldate reason
17/1/2011 10
17/1/2011 20
17/1/2011 12
how do i get the column name to show with the quantity

thanks

kevin

Use a string to name the column value:

SELECT "reasons" as File,reason AS Reason
FROM Qry_injuries
UNION ALL
SELECT "cause" as File,cause AS Reason
FROM Qry_injuries
UNION ALL
SELECT "incident" as File,inciden AS Reason
FROM Qry_injuries;
--

Ken Snell
http://www.accessmvp.com/KDSnell/





"kevcar40" <kevcar40 (AT) btinternet (DOT) com> wrote