Table structure convercion

[Pantelis]

I have a table with the following structure:

Code Char/stic value
----------------------------------------
cod1 char1 char1's value for this code
cod1 char2 char2's value for this code
cod1 char3 char3's "" "" "" ""
cod2 char1 char1's value for this code
cod2 char2 char2's value for this code
cod2 char3 char3's "" "" "" ""
cod3 char1 char1's value for this code
cod3 char2 char2's value for this code
cod3 char3 char3's "" "" "" ""
.. . .
.. . .

and I wand to write some code to built another table like
the following.
(or at least to but the data in a ready table with the
next structure)

Code char1 char2 char3
---------------------------------------
cod1 value value value
cod2 value value value
cod3 value value value
.. . . .
.. . . .

Some ideas please.

Thanks in advance,
Pantelis

 

[Roxie Aho]

If you've got a lot of fields this could get tedious, but
you can try this.

Make your new table structure.

Run an APPEND query appending Code to Code and Value(Old)
to Char1(new). The third column in the query is Char from
Old with a criteria of "Char1." The SQL is INSERT INTO
tblNewCode ( Code, Char1 )
SELECT tblOldcode.Code, tblOldcode.Value
FROM tblOldcode
WHERE (((tblOldcode.Char)="Char1"));

Run a series of UPDATE queries. This query is based on
tblOldCode and tblNewCode with a one-to-one relationship
between the Code fields.

The first update is Char2 of New to [tblOldcode]![Value]
using "Char2" as the Criteria in the Char field of
tblOldCode. The SQL is UPDATE tblNewCode INNER JOIN
tblOldcode ON tblNewCode.Code = tblOldcode.Code SET
tblNewCode.Char2 = [tblOldcode]![Value]
WHERE (((tblOldcode.Char)="Char2"));

Update Char3, and so on.

Roxie Aho
(e-mail address removed)
http://www.usinternet.com/users/roxiea/index.htm