Transpose query data

[justin.saye]

All,

Please offer suggestions on the following problem in MS Access:

I need to transpose a block of data, multiple rows and columns. Here
is the table:

ID Date1 Date2 Cost
10 1/2/09 2/1/09 10.00
11 1/3/09 2/3/09 12.00
12 1/4/09 2/4/09 14.00

I need it to look like this, preferably via some type of query:

(10)Date1 (10)Date2 (10)Cost (11)Date1 (11)Date2
(11)Cost (12)Date1 (12)Date2 (12)Cost
1/2/09 2/1/09 10.00 1/3/09
2/3/09 12.00 1/4/09 2/4/09 14.00

Crosstab seems to only work with (2) columns of data. Any ideas?

Thanks very much in advance.

Just85

 

[KARL DEWEY]

You can use a crosstab if you add or have anothe field in your table --
TRANSFORM First([Date1] & " - " & [Date2] & " - " & [Cost]) AS Expr1
SELECT Justin.[Names]
FROM Justin
GROUP BY Justin.[Names]
PIVOT "Dates w/ Cost for ID - " & [ID];

 

[justin.saye]

Thanks, Karl. That is a big help!

You can use a crosstab if you add or have anothe field in your table --
TRANSFORM First([Date1] & " - " & [Date2] & " - " & [Cost]) AS Expr1
SELECT Justin.[Names]
FROM Justin
GROUP BY Justin.[Names]
PIVOT "Dates w/ Cost for ID - " & [ID];

--
KARL DEWEY
Build a little - Test a little

All,

Please offer suggestions on the following problem in MS Access:

I need to transpose a block of data, multiple rows and columns.  Here
is the table:

ID      Date1        Date2        Cost
10     1/2/09        2/1/09       10.00
11     1/3/09        2/3/09       12.00
12     1/4/09        2/4/09       14.00

I need it to look like this, preferably via some type of query:

(10)Date1     (10)Date2      (10)Cost    (11)Date1     (11)Date2
(11)Cost    (12)Date1    (12)Date2   (12)Cost
1/2/09           2/1/09           10.00       1/3/09
2/3/09          12.00        1/4/09         2/4/09        14.00

Crosstab seems to only work with (2) columns of data.  Any ideas?

Thanks very much in advance.

Just85- Hide quoted text -

- Show quoted text -