How to convert fields name into record

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have a table with multiple fields on it. I need to create another table
base on the first table but require to transform 8 to 9 fields from the
first table and store it as a record in the second table like the example
below. Is there a way to transform it using vba code and I do not want to do
it manually as there are over 20000 records in the first table. Just like
the crosstab query but reverse!

First Table

ID Paul John Mary
1 Good Bad Good
2 Worst Bad
3 Ok Worst


Seocnd Table

ID Name Comment
1 Paul Good
2 John Bad
3 Mary Good
4 Paul Worst
5 John
6 Mary Bad
7 Paul OK
8 John Worst
9 Mary

Thanks
 
You can create a UNION query:

SELECT "Paul" AS Name, [Paul] AS Comment
FROM FirstTable
UNION
SELECT "John" AS Name, [John] AS Comment
FROM FirstTable
UNION
SELECT "Mary" AS Name, [Mary] AS Comment
FROM FirstTable

Save that query, and use it as the basis for an INSERT INTO query. I'm
assuming you've got an Autonumber field in your new table. If not, and you
really want to generate numbers like you've got in your example, try:

SELECT ([ID]-1)*3 + 1 AS ID, "Paul" AS Name, [Paul] AS Comment
FROM FirstTable
UNION
SELECT ([ID]-1)*3 + 2 AS ID, "John" AS Name, [John] AS Comment
FROM FirstTable
UNION
SELECT ([ID]-1)*3 + 3 AS ID, "Mary" AS Name, [Mary] AS Comment
FROM FirstTable

BTW, I wouldn't advise Name as a field name. I believe it's a reserved word,
and you can run into problems using reserved words as field names.
 
Back
Top