How to convert a single row of values into two columns? 2

[DBLoser]

I have a query called queryCauseCodes04 with this result:

CUSRE EXGM FTDD FTFM FTFSP
0 3 1 4 2

I need to convert (Update?) it to a table called CauseCodes:

CauseCode Count
CUSRE 0
EXGM 3
FTDD 1
FTFM 4
FTFSP 2

There are 18 columns but for time/space sake I only used 5. The Cause Codes always stay the same but the Count will change frequently.

Can I do this with a Union Query or do I need to use ADODB? I've been digging all day and am still lost.

 

[MajP]

Select
"CUSRE" AS CauseCode,
CUSRE AS TheCount
From
YourTable
UNION
Select
"EXGM" AS CauseCode,
EXGM AS TheCount
From
YourTable
......

 

[Andrzejek]

If you have an output from your Select sql like this:
[tt]
CUSRE EXGM FTDD FTFM FTFSP
0 3 1 4 2
[/tt]
and "the Count will change frequently.", I would go with ADODB route, something like:

rst.Open "Select * from MyTable Where ...", Conn

For i = 1 To rst.Fields.Count - 1
    str = "Insert Into CauseCodes (CauseCode, Count) VALUES (" & rst.Fields(i).Name & ", " & rst.Fields(i).Value & ")"
    Conn.Execute str
rst.Close

Assuming all values are Numbers.

Have fun.

---- Andy

 

[dhookom]

I use MajP's solution however the extra alias after the first SELECT are not necessary. I like MajP's changing "Count" to "TheCount" since COUNT is a reserved word.

SELECT
  "CUSRE" AS CauseCode,
   CUSRE AS TheCount
FROM
  YourTable
UNION 
SELECT
  "EXGM",
  EXGM
FROM
  YourTable
...

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

After the second look I agree with others.

I miss-read the post and assumed the number of Fields will change frequently, but the case is: they are static and only the Count will change. Sorry for that. :-(

But my solution will still work....

Have fun.

---- Andy

 

[SkipVought]

This kind of thing, with a multitude of columns, can be done in Excel in less then a minute.

faq68-5287

In your particular case, add a column to the LEFT, WITH A HEADING like DUMMY. Then SPECIFY the range INCLUDING the DUMMY column, in Step 4) of the FAQ.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DBLoser]

I used MajP's sql statement and it worked great. The query was nice because I didn't have to use a temporary table.

Solved