Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert from Vertical to Horizontal

Status
Not open for further replies.

markcrobinson

Programmer
Dec 14, 2009
10
US
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!
 
You need to first create a ranking query. This SQL is for FILENO which is similar to your Field1 and PHONE which is Field2.
Code:
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:
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top