A kind of reverse concatenate

[Hallgeir]

I have a table looking something like this:

Name Field1 Field2 Field3 Field4
Bill 1 3
John 1 2 3
Jim 2 4


Is it possible, in a query, to present this like:

Name Value
Bill 1
Bill 3
John 1
John 2
John 3
Jim 2
Jim 4

 

[Rick Brandt]

I have a table looking something like this:

Name Field1 Field2 Field3 Field4
Bill 1 3
John 1 2 3
Jim 2 4


Is it possible, in a query, to present this like:

Name Value
Bill 1
Bill 3
John 1
John 2
John 3
Jim 2
Jim 4

SELECT [Name], Field1 as [Value] FROM TableName
UNION ALL
SELECT [Name], Field2 FROM TableName
UNION ALL
SELECT [Name], Field3 FROM TableName
UNION ALL
SELECT [Name], Field4 FROM TableName

 

[Allen Browne]

If there are only a few fields (as in your example), you can achieve that
with a UNION query:

SELECT Name, Field1 AS TheValue FROM Table1
UNION ALL
SELECT Name, Field2 AS TheValue FROM Table1
UNION ALL
SELECT Name, Field3 AS TheValue FROM Table1
UNION ALL
SELECT Name, Field4 AS TheValue FROM Table1;

Suggestions
=========
a) The results you are after is how the data should be stored.

b) Name and Value are not good names for fields, since both are reserved
words in JET. For a list of the names to avoid, see:
Problem names and reserved words in Access
at:
http://allenbrowne.com/AppIssueBadWord.html

 

[Hallgeir]

Hallgeir wrote:
I have a table looking something like this:

Name Field1 Field2 Field3 Field4
Bill 1 3
John 1 2 3
Jim 2 4

Is it possible, in a query, to present this like:

Name Value
Bill 1
Bill 3
John 1
John 2
John 3
Jim 2
Jim 4

SELECT [Name], Field1 as [Value] FROM TableName
UNION ALL
SELECT [Name], Field2 FROM TableName
UNION ALL
SELECT [Name], Field3 FROM TableName
UNION ALL
SELECT [Name], Field4 FROM TableName

Thank you Rick your solution works fine, but I have one extra question. In
my table I have the value 0 in the empty fields. Like this:

Name Field1 Field2 Field3 Field4
Bill 1 0 3 0
John 1 2 3 0
Jim 0 2 0 4

How can I avoid the 0 value records in my query?

regards Hallgeir

 

[Rick Brandt]

Thank you Rick your solution works fine, but I have one extra
question. In my table I have the value 0 in the empty fields. Like
this:
Name Field1 Field2 Field3 Field4
Bill 1 0 3 0
John 1 2 3 0
Jim 0 2 0 4

How can I avoid the 0 value records in my query?

SELECT [Name], Field1 as [Value] FROM TableName
WHERE Field1 <> 0
UNION ALL
SELECT [Name], Field2 FROM TableName
WHERE Field2 <> 0
UNION ALL
SELECT [Name], Field3 FROM TableName
WHERE Field3 <> 0
UNION ALL
SELECT [Name], Field4 FROM TableName
WHERE Field4 <> 0