Combining Columns

[f430]

i was wondering if it is possible to do the combine columns into a query to make a new table.
if my original table looked like this
Part Number.....Col 1.......Col 2.....Col 3..Col 4.......Col 5.....Col 6
1.............. ..........10... .......20...... ..30.......12.. ........40..... ...15
2.............. ..........11... .......21...... ..31.......12.. ........__..... ...__
3.............. ..........12... .......22...... ..__.......__.. ........__..... ...__

how can i make the new query look something like this

part number.... Col......Col
1.............. ....10......... ..20
2.............. ....11......... ..21
3.............. ....12......... ..22
1.............. ....30......... ..12
1.............. ....40......... ..15
2.............. ....31......... ..12

Thanks

[evildracko]

perhaps a join or union condition

select part number, select([col1 union col 3] from table name a) as Col, select([col2 union col 4] from table nam b) as Col
from tablename, tablename a,tablename b

might work.. ts been a while since doign this give it a go

[f430]

what do u mean by tablename a and tablename b

[NeoPa]

Something like the following may work for you :

SELECT   [Part Number]
        ,[Col1]
        ,[Col2]
FROM     [YourTable]
WHERE  (([Col1] IS NOT Null)
   OR   ([Col2] IS NOT Null))
UNION ALL
SELECT   [Part Number]
        ,[Col3] AS [Col1]
        ,[Col4] AS [Col2]
FROM     [YourTable]
WHERE  (([Col3] IS NOT Null)
   OR   ([Col4] IS NOT Null))
UNION ALL
SELECT   [Part Number]
        ,[Col5] AS [Col1]
        ,[Col6] AS [Col2]
FROM     [YourTable]
WHERE  (([Col5] IS NOT Null)
   OR   ([Col6] IS NOT Null))
ORDER BY [Part Number]