Breaking up is Hard to Do

[Dbless]

I have a query that brings back the following data:

Order Nbr Style Color Scale Size1Qty Size2Qty Size3Qty
12345 1303 30 S 10 5 12

My database is for apparel and the sizes are grouped within each scale code. Scale "S" = Small thru XL sizes

What I want to do is bring back multiple lines of data

Order Nbr Style Color Scale Size Qty
12345 1303 30 S Small 10
12345 1303 30 S Medium 5
12345 1303 30 S Large 12 etc...

I have tried crosstab queries but not bringing back what I want. Does anyone have an idea of how this can be accomplished.

Many thanks

 

[smedvid]

Assuming that ur database table is...
Order Nbr Style Color Scale Size1Qty Size2Qty Size3Qty
12345 1303 30 S 10 5 12

Try following approach.
1 Query per Size.
=====
Query1:
Select Order, Style, Color, Scale, "Small" as Size, Size1Qty as Qnty
From tablex
=====
Query2:
Select Order, Style, Color, Scale, "Med" as Size, Size2Qty as Qnty
From tablex
=====
Then combine queries via Union ... Or Simply avoid creating queries and go straight to Union Query..


-------------
Sample Union Query:
Select Order, Style, Color, Scale, "Small" as Size, Size1Qty as Qnty
From tablex
UNION
Select Order, Style, Color, Scale, "Med" as Size, Size2Qty as Qnty
From tablex
UNION
...
UNION
...
--------
htwh

Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...