Consolidate Multiple Columns into Two Columns

[mefox]

Hello, Everyone...

I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.

What the simple query looks like:
http://i1159.photobucket.com/albums/...eryExample.jpg

Second what I want the query to look like:


http://i1159.photobucket.com/albums/...eryExample.jpg

This is probable an easy fix, and i would appreciate any help you can give. I am stuck on this

[lfpm062010]

Using "UNION" should solve your problem. I called the table "Example".

SELECT Example.Number, Example.Name1 AS Name
FROM Example
WHERE Example.Name1 IS NOT NULL
UNION
SELECT Example.Number, Example.Name2 AS Name
FROM Example
WHERE Example.Name2 IS NOT NULL
UNION
SELECT Example.Number, Example.Name3 AS Name
FROM Example
WHERE Example.Name3 IS NOT NULL
UNION
SELECT Example.Number, Example.Name4 AS Name
FROM Example
WHERE Example.Name4 IS NOT NULL
UNION
SELECT Example.Number, Example.Name5 AS Name
FROM Example
WHERE Example.Name5 IS NOT NULL
;

[mefox]

Thanks i knew it was something simple i was missing, thank you for solving this problem