Variation of transposing a table

[jangdc]

Hello Everyone,

I have a current database with multiple fields that I would like t
convert into a table that contains a row with the ID, Field Name an
value. Example:

ID Honda Mazda Ford etc.....
1 $5000 $6000 $8000
2 $3000 $7000 $6000


converted to:

1 Honda $5000
1 Mazda $6000
1 Ford $8000
2 Honda $3000
2 Mazda $7000
2 Ford $6000

Is there a module or some code that I can use to automate this? I'
sure others have had the need to perform this type of conversion.

thanks for your help

D

 

[John Vinson]

I have a current database with multiple fields that I would like to
convert into a table that contains a row with the ID, Field Name and
value. Example:

ID Honda Mazda Ford etc.....
1 $5000 $6000 $8000
2 $3000 $7000 $6000


converted to:

1 Honda $5000
1 Mazda $6000
1 Ford $8000
2 Honda $3000
2 Mazda $7000
2 Ford $6000

Good move!

A "Normalizing Union Query" is the ticket here. I'd suggest creating
the target table with fields ID, Model and Cost (with ID and Model
being the joint Primary Key if that's appropriate).

Then in the SQL window edit the following query:

SELECT ID, "Honda" AS Model, [Honda] AS COST
FROM yourtable
WHERE [Honda] IS NOT NULL
UNION ALL
SELECT ID, "Mazda" AS Model, [Mazda] AS COST
FROM yourtable
WHERE [Honda] IS NOT NULL
UNION ALL
SELECT ID, "Ford" AS Model, [Ford] AS COST
FROM yourtable
WHERE [Honda] IS NOT NULL
UNION ALL
.... <etc>

Save this query and then create an Append query based on it to
populate the new table.