columns to rows

[David Cox]

I do not have a computer running Access at the moment. An idea occurred to
me.

a common task is to convert table data such as:

Key col1 col2 col3 ...... colN

to a new table

Key colkey


The usual responses I have seen is VBA or a big union query

Is it possible to create a table with just one numeric field colkey
containing records with values 1 to N, put that in the design grid with the
original table in a make table query with output fields Key and colddata,
and write:

coldata: IIF(colkey=1,[col1],IIF(colkey=2, [col2],(IIF(colkey=3,[col3]
..........

Apologies if I am wasting group time.

Comments?

David F. Cox

 

[Douglas J. Steele]

There's a limit as to how many IIf statements you can nest (sorry, I don't
remember what it is, but it's somewhere around 10)

Better would probably be to use the Choose function:

coldata: Choose([colkey], [col1], [col2], [col3], ..., [coln])

 

[David Cox]

Yup, far better. It did occur to me as soon as I had walked away, but I had
to wait for family to evacuate the PC to say so. :-<

There's a limit as to how many IIf statements you can nest (sorry, I don't
remember what it is, but it's somewhere around 10)

Better would probably be to use the Choose function:

coldata: Choose([colkey], [col1], [col2], [col3], ..., [coln])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I do not have a computer running Access at the moment. An idea occurred to
me.

a common task is to convert table data such as:

Key col1 col2 col3 ...... colN

to a new table

Key colkey


The usual responses I have seen is VBA or a big union query

Is it possible to create a table with just one numeric field colkey
containing records with values 1 to N, put that in the design grid with
the original table in a make table query with output fields Key and
colddata, and write:

coldata: IIF(colkey=1,[col1],IIF(colkey=2, [col2],(IIF(colkey=3,[col3]
.........

Apologies if I am wasting group time.

Comments?

David F. Cox