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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Dale FyeOwner, Developing Solutions LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.