"untranspose?"

[HubbleO]

If I have a table as such:

Prov Num Field
123 aaa
123 bbb
123 ccc
456 xxx
456 zzz

How would I go about getting:

Prov Num Field1 Field2 Field3
123 aaa bbb ccc
456 xxx zzz

 

[dhookom]

Create ranking type query that numbers each record within a ProvNum. Then build a crosstab using the ranking column.

qrnkProvNumRank

SELECT SuchAs.[Prov Num], SuchAs.Field1, Count(SuchAs_1.Field1) AS CountOfField1
FROM SuchAs AS SuchAs_1 INNER JOIN SuchAs ON SuchAs_1.[Prov Num] = SuchAs.[Prov Num]
WHERE (((SuchAs.Field1)>=[SuchAs_1].[Field1]))
GROUP BY SuchAs.[Prov Num], SuchAs.Field1;

Then create your crosstab

TRANSFORM First(qrnkProvNumRank.Field1) AS FirstOfField1
SELECT qrnkProvNumRank.[Prov Num]
FROM qrnkProvNumRank
GROUP BY qrnkProvNumRank.[Prov Num]
PIVOT "Field" & [CountOfField1];

Duane
Hook'D on Access
MS Access MVP