Crosstab Help

[gmatriix]

Hello All,

I need some help with a crosstab. Here is what Im trying to do.

I have a table that will look like this:

ID RawData Serial
-- --------- ------
1 0.7162 0005
2 0.7175 0005
3 0.7165 0005

I need the query to look like this:

ID Serial RawData1 RawData2 RawData3
-- ----- --------- --------- ---------
1 0005 0.7162 0.7175 0.7165



I keep getting alittle close but can't yet get it....

Any Ideas?

Thanks,

 

[plog]

How does the ID = 1 for that row you expect to be produced?

 

[gmatriix]

maybe a MIN of the ID?? possibly

I am referring to the desire results...

 

[plog]

I was referring to the desired results as well. In a crosstab you need to GROUP BY at least one field in the datasource to produce the row headings--you haven't done that. And simply taking the MAX of the ID field will not work, it has to be a GROUP BY for row headings.

Additionally, you need to put an Aggregate function (MAX, MIN, COUNT, SUM) around the value field because a crosstab is an Aggregate query.
I was able to create a crosstab to generate the data you want from the data you supplied. That doesn't mean it will continue to work when you apply it against your entire dataset. This is that query:

TRANSFORM Sum(YourTableName.RawData) AS SumOfRawData
SELECT 1 AS i
FROM YourTableName
GROUP BY 1
PIVOT "RawData" & [ID];

Replace 'YourTableName' with the name of your actual table.

 

[gmatriix]

Thanks Alot...haven't really messed with Crosstab much... thanks for tutorial!!