Changing query row results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BobT - 16 May 2005 00:04 GMT

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

Signature

Reply to this Message

Douglas J. Steele - 16 May 2005 00:05 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>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
[quoted text clipped - 22 lines]
>
> Thanks

Reply to this Message

BobT - 16 May 2005 04:03 GMT

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

Thanks again,

Signature

> SELECT Key, V1 AS VXX
> FROM MyTable
[quoted text clipped - 37 lines]
> >
> > Thanks

Reply to this Message

John Spencer (MVP) - 16 May 2005 00:37 GMT

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.

> 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:
[quoted text clipped - 23 lines]
> --
> BT

Reply to this Message