Union Query 3 fields 1 table

L

Lin

Hi, and thanks in advance.

Is it possible to do a union query on one table with three fields.

Heres my structure.
ID, Surname, MaidenName, Alias.

What i want to do is union the last three without concatenating.

Regards.
 
A

Allen Browne

Alias is a reserved word, so you may run into problems with that one, but
try:

SELECT ID, Surname AS TheName FROM Table1
UNION
SELECT ID, MaidenName AS TheName FROM Table1
UNION
SELECT ID, Table1.[Alias] AS TheName FROM Table1
ORDER BY ID;
 
L

Lin

Allen, thanks so much, it works great. Could I trouble you with one other
question to this, How can I eliminate blank fields. If possible?

Regards.

Allen Browne said:
Alias is a reserved word, so you may run into problems with that one, but
try:

SELECT ID, Surname AS TheName FROM Table1
UNION
SELECT ID, MaidenName AS TheName FROM Table1
UNION
SELECT ID, Table1.[Alias] AS TheName FROM Table1
ORDER BY ID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lin said:
Hi, and thanks in advance.

Is it possible to do a union query on one table with three fields.

Heres my structure.
ID, Surname, MaidenName, Alias.

What i want to do is union the last three without concatenating.

Regards.
Click to expand...
Click to expand...
 
A

Allen Browne

Add a WHERE clause to each one:

SELECT ID, Surname AS TheName FROM Table1 WHERE Surname Is not Null
UNION
SELECT ID, MaidenName AS TheName FROM Table1 WHERE ...

If you don't mind duplcates (e.g. where a person's Surname and MaidenName
are entered as the same), UNION ALL would be a bit more efficient.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lin said:
Allen, thanks so much, it works great. Could I trouble you with one other
question to this, How can I eliminate blank fields. If possible?

Regards.

Allen Browne said:
Alias is a reserved word, so you may run into problems with that one, but
try:

SELECT ID, Surname AS TheName FROM Table1
UNION
SELECT ID, MaidenName AS TheName FROM Table1
UNION
SELECT ID, Table1.[Alias] AS TheName FROM Table1
ORDER BY ID;

Lin said:
Hi, and thanks in advance.

Is it possible to do a union query on one table with three fields.

Heres my structure.
ID, Surname, MaidenName, Alias.

What i want to do is union the last three without concatenating.

Regards.
Click to expand...
Click to expand...
Click to expand...
 
Top