Transform Rows to Columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Samantha - 15 Aug 2006 20:08 GMT

I need to create a query (and then a report ) that shows the data from rows
into columns; all data is from one table.
The fields of the table are:
SerialNumber
StepID
PassOrFail
Start

Possible Data are:
SerialNumber StepID Start PassOrFail
SN1 1 1/1/06 Pass
SN1 2 1/2/06 Fail
SN2 1 1/3/06 Pass
SN3 1 1/1/06 Pass

What I would like to see in the query result is:
SerialNumber StepID1 PassOrFail StepID2 PassOrFail ...
SN1 1/1/06 Pass 1/2/06 Fail
SN2 1/3/06 Pass
SN3 1/1/06 Pass

I'm trying the crosstab query, as follows (but not working exactly the way I
need it to be):
TRANSFORM Max(Table1.Start) AS MaxOfStart
SELECT Table1.SerialNumber, Table1.PassOrFail
FROM Table1
WHERE ((Not (Table1.SerialNumber) Is Null))
GROUP BY Table1.SerialNumber, Table1.PassOrFail
PIVOT Table1.StepID;

SerialNumber    PassOrFail    1    2    5
SN1    Fail        1/2/06
SN1    Pass    1/1/06
SN2        Pass    1/3/06
SN3        Pass 1/1/06

Can this be done in MsAccess? Any help would be very much appreciated!

Reply to this Message

KARL DEWEY - 15 Aug 2006 20:24 GMT

Try this --
TRANSFORM Max([Start] & " " & [PassOrFail]) AS X
SELECT Samantha.SerialNumber
FROM Samantha
WHERE ((Not (Samantha.SerialNumber) Is Null))
GROUP BY Samantha.SerialNumber
PIVOT "StepID" & [StepID];

> I need to create a query (and then a report ) that shows the data from rows
> into columns; all data is from one table.
[quoted text clipped - 33 lines]
>
> Can this be done in MsAccess? Any help would be very much appreciated!

Reply to this Message