Rows and Columns

[SPE2]

I new member to this Forum and the question is more a ADP / SQL question but any help is much appreciated.

I have a table with multiple records that I need to reformat.

Colour Size Qty
Rec1 Red L 1
Rec2 Red M 2
Rec3 Red S 3
Rec4 Blue L 1
Rec5 Blue M 2
Rec6 Blue S 3

Transform to a new table or query

Colour L M S
Rec1 Red 1 2 3
Rec2 Blue 1 2 3

It is a partial cross tab but it is causing be grief!

 

[SkipVought]

Hi,

Please post your SQL.

Skip,

_[red][/red]

 

[SPE2]

Sorry,
Nothing other than embarrassing failures to show
I can’t seem to get away from the colour appearing multiple times

 

[SkipVought]

???

Skip,

_[red][/red]

 

[PHV]

One way (SQL code):
SELECT Colour, Sum(QtyL) AS L, Sum(QtyM) AS M, Sum(QtyS) AS S
FROM (
SELECT Colour, Qty AS QtyL, 0 AS QtyM, 0 AS QtyS FROM yourTable WHERE size='L'
UNION SELECT Colour, 0, Qty, 0 FROM yourTable WHERE size='M'
UNION SELECT Colour, 0, 0, Qty FROM yourTable WHERE size='S'
) AS U
GROUP BY Colour

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SPE2]

Thanks PH,
Looks good but it is not happy with the first Sum line as it doesn’t like the colour field not being summed. I think it may also need some other punctuation. I really do appreciate your time in looking at this

SPE

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SPE2]

My fault.. Missed out a table name, your code works fine both in mdb mode and Access Project mode with slight punctuation mods.

I never expected to get this sorted this evening. I can now scale it up to my 500,000 rec problem
Thanks again