Changing query row results

[BobT]

I have a query that parses data form a field. This field contains 3 digit
codes that I am breaking out. For instance if the field contains the codes:

aaabbbcccdddeee

My result query looks like this

Key V1 V2 V3 V4 V5
1 aaa bbb ccc ddd eee

I need my result to be in the form of:

Key VXX
1 aaa
1 bbb
1 ccc
1 ddd
1 eee

In this manner I can create a table with two fields to contain the data
instead of the first query that parses the data out to mutiple columns. How
would I go about doing this?

Thanks

 

[Douglas J. Steele]

SELECT Key, V1 AS VXX
FROM MyTable
UNION
SELECT Key, V2 AS VXX
FROM MyTable
UNION
SELECT Key, V3 AS VXX
FROM MyTable
UNION
SELECT Key, V4 AS VXX
FROM MyTable
UNION
SELECT Key, V5 AS VXX
FROM MyTable

 

[John Spencer (MVP)]

One field containing multiple values?
Does it contain the same number of value sets? (Always 5)

If so, you can use a union query to normalize the data.

SELECT Key, Mid(CodeField,1,3) as Code
FROM YourTable
UNION ALL
SELECT Key, Mid(CodeField,4,3) as Code
FROM YourTable
UNION ALL
SELECT Key, Mid(CodeField,7,3) as Code
FROM YourTable
UNION ALL
SELECT Key, Mid(CodeField,10,3) as Code
FROM YourTable
UNION ALL
SELECT Key, Mid(CodeField,13,3) as Code
FROM YourTable

If you feel the need to put this into a table, you can use the above query to
either do a make table query or an insert query.

 

[BobT]

Thank you all for the quick response. I should have known this
Everything is working great!

Thanks again,