Merge multiple columns into one via a query in an access database

microsoft accessquery

i need to write a query that combines multiple columns of text.

my database is a database of wallpapers on my computer (i use john's background switcher), so i can easily find them and have them categorized.

each record has 4 character fields (i figured it was easier to do this than a tag style thing.) i need a query to merge these so all characters from all 4 different fields are in one column, an not merged into one field. please remember that some of the character fields are empty.

also, if there is an easier way to do this, please notify me.

here is a sample of what i want:

(table)
col1|col2|col3    
x   |y   |z    
c   |    |    

(query output)    
outputcol    
x    
y
z  
c

Best Answer

Maybe this?

SELECT col1 FROM table
UNION
SELECT col2 FROM table
UNION
SELECT col3 FROM table
UNION
SELECT col4 FROM table

UNION only joins distinct values (if you have 4 't' values, it only puts one). UNION ALL will store duplicates.

Related Question