combining data from several columns into a single column

G

Guest

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
 
N

Neil Sunderland

Deb said:
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
Click to expand...

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
 
G

Guest

Neil Sunderland said:
Deb said:
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
Click to expand...

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:
Click to expand...

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?
 
D

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


Deb said:
Neil Sunderland said:
Deb said:
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
Click to expand...

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:
Click to expand...

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?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split combined name (Smith, John) into (Smith) (John)? 6
How do I query multiple rows into a single string in Access? 3
Combine 2 fields into 1 via SQL query 2
Combining rows for identical names 2
Partial Search in a Query 4
Concatenating a single column 6
How to combine 2 data columns? 1
Seperate a column into 2 columns 2

Top