SQL - join three fields - same table into one column

[mackyrm]

I'm trying to create a query that lists all telephone numbers in one column. I have three telephone fields in one table and would essentially like to take these three fields and Chang and together on one list.
I have tried the union command but without success; I'm assuming this command is only used to join separate tables and not fields within the same table.

Much appreciated

Macky

 

[antoka05]

Try with:

Select <field 1> from <table>  as <Alias>
UNION Select <field 2> from <table> 
UNION Select <field 3> from <table>

Regards,
Antonio

 

[mackyrm]

Doesn't seem to work, I should be getting 25823 records, but only getting 25093...

 

[antoka05]

UNION command automatically delete duplicated records.
If you want all records use 'UNION ALL' and not 'UNION'.

Regards,
Antonio

 

[mackyrm]

Perfect. That saves me the effort of the next step in the process of cleaning and preparing the list!

Many thanks!

 

[Darqor]

I tried this, but i get only one column... could anyone tell me why? what am i doing wrong?
Thx.

SELECT `firstname` FROM `clients` AS `name`
UNION ALL Select `lastname` FROM `clients`
UNION ALL Select `number` FROM `clients`

 

[MSAccessRookie]

I tried this, but i get only one column... could anyone tell me why? what am i doing wrong?
Thx.

SELECT `firstname` FROM `clients` AS `name`
UNION ALL Select `lastname` FROM `clients`
UNION ALL Select `number` FROM `clients`

You are getting one Column, because you are only Selecting one column.

The advice was based on your request to "create a query that lists all telephone numbers in one column"

Is there a misunderstanding regarding your intentions?

 

[Pat Hartman]

If you were actually looking to concatenate the telephone numbers, use the &.

Select fld1 & ";" & fld2 & ";" & fld3 As fldList, ....

 

[Darqor]

The intension is the same, i just have different data to put in one column. mackyrm wanted 3 columns together ( three different phone numbers ) and i want to put together 3 columns as well with different data ( column1=first name, column2=last name, column3=number )


Present data:
column names: firstname | lastname | number | other data 1....
column data: jon | Doe | 23355 | xxxxx
steven | doe | 565465 | yyyyy


And what i would like to get:

column name: Name | other data
column data: Jon Doe 23355 | xxxxx
Steven Doe 565465 | yyyyy


I hope i managed to clear the confusion i created.
Kind regards.

 

[Pat Hartman]

Did you read post # 8?