transpose columns into rows

[bzh2md]

Hi all,

I have a table with data arranged like that:

Part Defect Sum of Defects

ABCD 700 250
ABCD 921 50
ABCD 800 300
XXXX 750 22
XXXX 650 150
XXXX 700 200

Any way I can arrange data so I can get in the end something like:
ABCD 800 300 ABCD 700 250 ABCD 921 50
XXXX 700 200 XXXX 650 150 XXXX 750 22

Please advise, thank you so much,

Raluca

 

[Ken Snell \(MVP\)]

Create a report with three columns, with the columns being filled left to
right (not top to bottom). Create a query that will return the records in
your desired order (to match how you want the records to display across the
report). Put three textboxes in the first column, and bind each textbox to
one of the fields.

 

[Guest]

Ken,

The issue is more complicated than that.
The data that I have needs to be ranked based on the Top 3 Sum of Defects
for each Part.

Part Defect Sum of Defects
ABCD 700 250
ABCD 921 50
ABCD 800 300
XXXX 750 22
XXXX 650 150
XXXX 700 200
YYYY 850 1000
YYYY 600 800
YYYY 400 20
YYYY 750 150
YYYY 900 300

So I have to get in the end this type of arrangement:

ABCD 800 300 700 250 921 50
XXXX 700 200 650 150 750 22
YYYY 600 800 900 300 750 150 do not show the 4th instance (400 20)

Please advise again if you wish. Thank you so much,

Raluca

 

[Ken Snell \(MVP\)]

I was out of the office all day today and did not have a chance to reply. I
will plan to post a reply on Thursday.

 

[Ken Snell \(MVP\)]

Create and save this query (call it qryDefectRanking) -- change TableName to
the real name of the table, and change PrimaryKeyFieldName to the real name
of the primary key field in the table:

SELECT T.Part, T.Defect, T.[Sum of Defects],
((SELECT Count(*) FROM TableName AS A
WHERE A.Part = T.Part AND A.Defect = T.Defect
AND A.[Sum of Defects] <= T.[Sum of Defects]) -
(SELECT Count(*) FROM TableName AS B
WHERE B.Part = T.Part AND B.Defect = T.Defect
AND B.[Sum of Defects] = B.[Sum of Defects]
AND B.PrimaryKeyFieldName > T.PrimaryKeyFieldName))
AS DefectRankNumber
FROM TableName AS T;



Create this query to provide the desired data -- change TableName to the
real name of the table:

SELECT T.Part,
(SELECT Q1A.Defect
FROM qryDefectRanking AS Q1A
WHERE Q1A.Part = T.Part AND
Q1A.DefectRankNumber = 1) AS DefectRank1,
(SELECT Q1B.[Sum of Defects]
FROM qryDefectRanking AS Q1B
WHERE Q1B.Part = T.Part AND
Q1B.DefectRankNumber = 1) AS SumOfDefectsRank1,
(SELECT Q2A.Defect
FROM qryDefectRanking AS Q2A
WHERE Q2A.Part = T.Part AND
Q2A.DefectRankNumber = 2) AS DefectRank2,
(SELECT Q2B.[Sum of Defects]
FROM qryDefectRanking AS Q2B
WHERE Q2B.Part = T.Part AND
Q2B.DefectRankNumber = 2) AS SumOfDefectsRank2,
(SELECT Q3A.Defect
FROM qryDefectRanking AS Q3A
WHERE Q3A.Part = T.Part AND
Q3A.DefectRankNumber = 3) AS DefectRank3,
(SELECT Q3B.[Sum of Defects]
FROM qryDefectRanking AS Q3B
WHERE Q3B.Part = T.Part AND
Q3B.DefectRankNumber = 3) AS SumOfDefectsRank3
FROM TableName AS T
ORDER BY T.Part;