VERTICAL TO HORIZONTAL query

VBA  ACCESS 2010

what i need:
I have a 2 column table:
based upon the data i need to create a query that will make this view horizontal


i need a query

The data looks like this in vertical ..
FieldA has repeating values need to be moved to a single line.
A field  C , D, E , F etc....need to be created as equal to repeating values.
Current Data:
i.e.
FieldA       FieldB
10A371      2CYF2
10D839      3ANW9
10G927      12M181
10G927      12M192
10G928      12M182
10G928      12M193
10J875      1ECE4
10K005      5RVT9
10K005      12M183
10K005      12M191
10K007      12M182
10K007      12M193

in query result
fieldA        fieldb            fieldC         fieldD
10A371      2CYF2            
10D839      3ANW9            
10G927      12M181      12M192      
10G928      12M182      12M193      
10J875      1ECE4            
10K005      5RVT9      12M183      12M191
10K007      12M182      12M193      



Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
Dale FyeCommented:
Since Access doesn't have a Rank function, I generally start out by creating a query that counts the number of values less than the current value.

In your case, that query might look like:

SELECT T1.FieldA, T.FieldB, Count(T2.FieldB) as Rank
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.FieldA = T2.FieldA AND T1.FieldB>=T2.FieldB
GROUP BY T1.FieldA, T1.FieldB

You then make this into a subquery:

SELECT sub.FieldA, sub.FieldB, sub.Rank
FROM (
SELECT T1.FieldA, T.FieldB, Count(T2.FieldB) as Rank
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.FieldA = T2.FieldA AND T1.FieldB>=T2.FieldB
GROUP BY T1.FieldA, T1.FieldB
) as sub

run that query, then view it in design view, change the query from a SELECT query to a Crosstab query
SELECT FieldA as the RowHeader, Rank as the ColumnHeader, and FieldB as the Value field.  In the Totals row of the query, change the FieldB column to be "First"
0
 
Rey Obrero (Capricorn1)Commented:
test this query, change name of table and fields accordingly

TRANSFORM FIRST(FieldB) AS FirstField
SELECT FieldA
FROM (
 SELECT tblFord.FieldA, tblFord.FieldB, "Field " & Count(tblFord2.FieldB) AS ItemCount
 FROM tblFord, tblFord AS tblFord2
 WHERE tblFord.FieldB <= tblFord2.FieldB AND tblFord.FieldA = tblFord2.FieldA
 GROUP BY tblFord.FieldA, tblFord.FieldB
 ORDER BY tblFord.FieldA
)
GROUP BY FieldA
PIVOT ItemCount
0
 
FordraidersAuthor Commented:
Rey, Thanks for replying.

the query is working.
but returning the field headers in numeric order.
material_no      Field 1      Field 10      Field 11      Field 12      Field 13      Field 14      Field 15      Field 16      Field 17      Field 18      Field 19      Field 2      Field 20      Field 21      Field 22      Field 23      Field 24      Field 25      Field 3      Field 4      Field 5      Field 6      Field 7      Field 8      Field 9
10A020      6UFT9                                                                  10E534                                          10E533      10E532      10E531      10E530      10A025            

Thanks fordraiders
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
test this

TRANSFORM FIRST(FieldB) AS FirstField
SELECT FieldA
FROM (SELECT tblFord.FieldA, tblFord.FieldB, "Field " & Chr(64 + Count(tblFord2.FieldB)) AS ItemCount FROM tblFord, tblFord AS tblFord2 WHERE tblFord.FieldB<=tblFord2.FieldB And tblFord.FieldA=tblFord2.FieldA GROUP BY tblFord.FieldA, tblFord.FieldB ORDER BY tblFord.FieldA)  AS [%$##@_Alias]
GROUP BY FieldA
PIVOT ItemCount;
0
 
Rey Obrero (Capricorn1)Commented:
Test this

TRANSFORM FIRST(FieldB) AS FirstField
SELECT FieldA
FROM (SELECT tblFord.FieldA, tblFord.FieldB, "Field " & Chr(64 + Count(tblFord2.FieldB)) AS ItemCount FROM tblFord, tblFord AS tblFord2 WHERE tblFord.FieldB<=tblFord2.FieldB And tblFord.FieldA=tblFord2.FieldA GROUP BY tblFord.FieldA, tblFord.FieldB ORDER BY tblFord.FieldA)
GROUP BY FieldA
PIVOT ItemCount;
0
 
FordraidersAuthor Commented:
dale, worked great !!!
0
 
FordraidersAuthor Commented:
Thanks to All...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.