combining data from several columns into a single column

[Deb]

I am working with a database that has 11 columns with the same data type. I
need to compile this data into a single column - replicating the data in the
other columns to create a series of separate records for each of the other 11
columns that are not null.

For example:

Original Table:
John Smith -1 -1 0 -1

End Table:
John Smith -1
John Smith -1
John Smith 0
John Smith -1

 

[Neil Sunderland]

I am working with a database that has 11 columns with the same data type. I
need to compile this data into a single column - replicating the data in the
other columns to create a series of separate records for each of the other 11
columns that are not null.

For example:

Original Table:
John Smith -1 -1 0 -1

End Table:
John Smith -1
John Smith -1
John Smith 0
John Smith -1

Something like this:

SELECT [name], field1 FROM mytable WHERE field1 IS NOT NULL
UNION ALL
SELECT [name], field2 FROM mytable WHERE field2 IS NOT NULL
UNION ALL
[...]
UNION ALL
SELECT [name], field10 FROM mytable WHERE field10 IS NOT NULL
UNION ALL
SELECT [name], field11 FROM mytable WHERE field11 IS NOT NULL

 

[Deb]

I am working with a database that has 11 columns with the same data type. I
need to compile this data into a single column - replicating the data in the
other columns to create a series of separate records for each of the other 11
columns that are not null.

For example:

Original Table:
John Smith -1 -1 0 -1

End Table:
John Smith -1
John Smith -1
John Smith 0
John Smith -1

Something like this:

SELECT [name], field1 FROM mytable WHERE field1 IS NOT NULL
UNION ALL
SELECT [name], field2 FROM mytable WHERE field2 IS NOT NULL
UNION ALL
[...]
UNION ALL
SELECT [name], field10 FROM mytable WHERE field10 IS NOT NULL
UNION ALL
SELECT [name], field11 FROM mytable WHERE field11 IS NOT NULL

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

I've used:

SELECT [name], Expr4 FROM AllTraining WHERE Expr4 IS NOT NULL
UNION ALL
SELECT [name], Expr5 FROM AllTraining WHERE Expr5 IS NOT NULL
UNION ALL
SELECT [name], Expr6 FROM AllTraining WHERE Expr6 IS NOT NULL
UNION ALL
SELECT [name], Expr7 FROM AllTraining WHERE Expr7 IS NOT NULL
UNION ALL
SELECT [name], Expr8 FROM AllTraining WHERE Expr8 IS NOT NULL
UNION ALL
SELECT [name], Expr9 FROM AllTraining WHERE Expr9 IS NOT NULL
UNION ALL
SELECT [name], Expr10 FROM AllTraining WHERE Expr10 IS NOT NULL
UNION ALL
SELECT [name], Expr11 FROM AllTraining WHERE Expr11 IS NOT NULL
UNION ALL
--

but I'm getting the error message "Invalid SQL statement: expected 'DELETE',
'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

Any ideas?

 

[Duane Hookom]

Remove the final UNION ALL
....
SELECT [name], Expr10 FROM AllTraining WHERE Expr10 IS NOT NULL
UNION ALL
SELECT [name], Expr11 FROM AllTraining WHERE Expr11 IS NOT NULL;


--
Duane Hookom
MS Access MVP

I am working with a database that has 11 columns with the same data
type. I
need to compile this data into a single column - replicating the data in
the
other columns to create a series of separate records for each of the
other 11
columns that are not null.

For example:

Original Table:
John Smith -1 -1 0 -1

End Table:
John Smith -1
John Smith -1
John Smith 0
John Smith -1

Something like this:

SELECT [name], field1 FROM mytable WHERE field1 IS NOT NULL
UNION ALL
SELECT [name], field2 FROM mytable WHERE field2 IS NOT NULL
UNION ALL
[...]
UNION ALL
SELECT [name], field10 FROM mytable WHERE field10 IS NOT NULL
UNION ALL
SELECT [name], field11 FROM mytable WHERE field11 IS NOT NULL

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

I've used:

SELECT [name], Expr4 FROM AllTraining WHERE Expr4 IS NOT NULL
UNION ALL
SELECT [name], Expr5 FROM AllTraining WHERE Expr5 IS NOT NULL
UNION ALL
SELECT [name], Expr6 FROM AllTraining WHERE Expr6 IS NOT NULL
UNION ALL
SELECT [name], Expr7 FROM AllTraining WHERE Expr7 IS NOT NULL
UNION ALL
SELECT [name], Expr8 FROM AllTraining WHERE Expr8 IS NOT NULL
UNION ALL
SELECT [name], Expr9 FROM AllTraining WHERE Expr9 IS NOT NULL
UNION ALL
SELECT [name], Expr10 FROM AllTraining WHERE Expr10 IS NOT NULL
UNION ALL
SELECT [name], Expr11 FROM AllTraining WHERE Expr11 IS NOT NULL
UNION ALL
--

but I'm getting the error message "Invalid SQL statement: expected
'DELETE',
'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

Any ideas?