Convert from Vertical to Horizontal

[markcrobinson]

I have a file that looks like this:

Field1 Field2
------ ------
Cust1 blue
Cust1 Green
Cust1 Gray
Cust2 Orange
Cust2 Blue
Cust2 White
Cust3 Yellow
Cust3 Red
Cust3 Green

That I need to turn into this format

NField1 Nfield2 Nfield3 Nfield4
------- ------- ------- -------
Cust1 blue green gray
Cust2 Orange blue white
Cust3 Yellow Red Green

ANy suggestions as to the best way to make this conversion?

Thanks!

 

[dhookom]

You need to first create a ranking query. This SQL is for FILENO which is similar to your Field1 and PHONE which is Field2.

SELECT qsellindag.FILENO, qsellindag.PHONE, Count(qsellindag_1.FILENO) AS GroupCount
FROM qsellindag AS qsellindag_1 INNER JOIN qsellindag ON qsellindag_1.FILENO = qsellindag.FILENO
WHERE (((qsellindag.PHONE)>=[qsellindag_1].[PHONE]))
GROUP BY qsellindag.FILENO, qsellindag.PHONE;

Then create a crosstab query based on the ranking query like:

TRANSFORM Min(qrnklindag.PHONE) AS MinOfPHONE
SELECT qrnklindag.FILENO
FROM qrnklindag
GROUP BY qrnklindag.FILENO
PIVOT "Nfield" & Format([GroupCount],"00");

Duane
Hook'D on Access
MS Access MVP