Combining 3 Columns into one

[f430]

i need to write a query that combines more than one column together.
one example of what i am trying to do is as follows:

Col 1 Col 2 Col 3
15..... 13..... 10
2..... 9...... 12.5
6..... 5...... 22

Query new colunm
15
2
6
13
9
5
10
12.5
22
However the column fields are text.

[Stewart Ross]

I'm assuming this is not a homework question. If it is our rules do not permit us to answer it.

You need to use a Union query to do what you ask. I can show part of this (thus not giving you the complete answer if it is a homework question) as follows:

Select [Col 1] from [Your table] UNION
Select [Col 2] from [Your table];

Like any SQL-based query, the values returned form a Set. The ordering of values within a set cannot be guaranteed unless you apply a specific ORDER BY clause in the SQL - but in Union queries you may find that this gives unexpected results.

-Stewart

[f430]

actually what i am trying to do is a bit more complicated, and im not even sure if it is possible.
here is what i am trying to do

Part Number.....Col 1.......Col 2.....Col 3
1........................10..........20........30
2........................11..........__........31
3........................12..........__........__

my question is can i do this:

part number.......New Col
1..................10
2..................11
3..................12
1..................20
1..................30
2..................31

im trying to have a query that replicates the part number if col 2, or col 3 are being used. and then make that a table.
this is not homework, school hasnt even started

Thanks

[Stewart Ross]

Hi. It helps to know what you want up front, as the solution is not quite the same.

Select [part number], [col 1] from [Your Table]
UNION
Select [part number], [col 2] from [Your Table] 
WHERE [Col 2] IS NOT NULL 
UNION
Select [part number], [col 3] from [Your Table] 
WHERE [Col 3] IS NOT NULL;

-Stewart

[f430]

where would i enter this in, if i am trying to put it in a query?

[NeoPa]

You can tag an ORDER BY line to Stewart's SQL if you like :

ORDER BY [Part Number]

Of course you realise this is on;y necessary because the structure of your table is not normalised (See Normalisation and Table structures).

[NeoPa]

f430: where would i enter this in, if i am trying to put it in a query?

Open the query (QueryDef) that you want to use it in then select View / SQL View. from there you simply paste it over whatever's there. You won't be able to see it in Design View after that as UNION queries are not supported in that view.

[f430]

Thank you NeoPa, i appreciate your help

[NeoPa]

No worries. These columns aren't [Defect Code 1] through [Defect Code 3] by any chance are they?

[f430]

they are the defects but im using this modified table for creating diagrams.
i was actually thinking of modifying this a little, if i wanted to do the following

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........__. ......__..........__........__

my question is can i do this:

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

[NeoPa]

f430: my question is can i do this:

No. It's not. That is an entirely new question (You should pay more attention to advice given. Stewart mentioned this earlier). People don't appreciate answering a question correctly only to be told the question has changed. This has happened twice now in this thread already.

If you want to ask a new question then please do so in its own thread.