Changing an Access table orientation from horizontal to vertical

[SQSunshine]

I currently have an Access table with a few demographic fields and 36 "Item" fields, named "ITEM1", ITEM2", ITEM3" etc., with numerical data in each field. There are thousands of records within this table. I need to switch the current horizontal orientation of this table to a vertical layout, with the same demographic fields, but with one field, "ITEMNUM" for example, which contains "ITEM"1", ITEM2", etc as records, and another field, "ITEMDATA" for example, which contains the data for each item. Is there a relatively easy way to do this, or do I have to create 36 queries and append the data to another table?

Ex of current table:

ID,DOB,ITEM1,ITEM2,ITEM3
1,1/1/1966,4,3,4
2,2/2/1955,3,3,4
3,7/1/1959,2,1,4

Example of needed table:

ID,DOB,ITEMNUM,ITEMDATA
1,1/1/1966,ITEM1,4
1,1/1/1966,ITEM2,3
1,1/1/1966,ITEM3,4
2,2/2/1955,ITEM1,3
2,2/2/1955,ITEM2,3
2,2/2/1955,ITEM3,4
3,7/1/1959,ITEM1,2
3,7/1/1959,ITEM2,1
3,7/1/1959,ITEM3,4

Thanks for any help someone can give me.

SQ

 

[lameid]

You need to RUN 36 queries but something like this will work...

Dim lngCounter as long
lngCounter = 1
While lngCounter <=36

      Docmd.runSQL "Insert Into Needed_Table " & _
            "(ID, DOB, ITEMNUM, ITEMDATA) " & _
            "Select ID, DOB, " & _ 
            """ITEM" & lngCounter & """ as Expr1, " & _ 
            "Item" & lngCounter & " " &
            "From CurrentTable"
      lngCounter = lngCounter + 1
Wend

 

[Andrzejek]

Just out of couriosity.....
[tt]
ID DOB ITEMNUM ITEMDATA
1 1/1/1966 ITEM1 4
1 1/1/1966 ITEM2 3
1 1/1/1966 ITEM3 4
2 2/2/1955 ITEM1 3
[/tt]
What do ITEM1, ITEM2, ITEM3 and 4, 3 stand for? Do they have any acctual meaning?

Have fun.

---- Andy

 

[dhookom]

You could try a union query to normalize the table:

SELECT ID, DOB, "ITEM1" as ITEMNUM, ITEM1 as ITEMDATA
FROM CurrentTable
UNION ALL
SELECT ID, DOB, "ITEM2", ITEM2
FROM CurrentTable
UNION ALL
SELECT ID, DOB, "ITEM3", ITEM3
FROM CurrentTable
UNION ALL
SELECT ID, DOB, "ITEM4", ITEM4
FROM CurrentTable
UNION ALL
--  etc --
SELECT ID, DOB, "ITEM36", ITEM36
FROM CurrentTable;

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

My suggestion assumes you are fixing the data... Duane's suggests querying out the results for use. Keep in mind that if you want the original data set after it is fixed, you can always use a crosstab query to get there.

Andy makes an excellent point.... What is the meaning? This implies a need for another table... Or perhaps this was demo data?

In which case you would have to modify my code to look at the field names of the source table to run the series of tables (I would use Select Case statement and put all the fields that repeat in the SQL in the first Case and then run the query in the Else block).

 

[TheAceMan1]

For Better Viewing ...

Ex of current table:

[tt]ID DOB ITEM1 ITEM2 ITEM3
** ******** ***** ***** *****
1 1/1/1966 4 3 4
2 2/2/1955 3 3 4
3 7/1/1959 2 1 4[/tt]

Example of needed table:

[tt]ID DOB ITEMNUM ITEMDATA
** ******** ******* ********
1 1/1/1966 ITEM1 4
1 1/1/1966 ITEM2 3
1 1/1/1966 ITEM3 4
2 2/2/1955 ITEM1 3
2 2/2/1955 ITEM2 3
2 2/2/1955 ITEM3 4
3 7/1/1959 ITEM1 2
3 7/1/1959 ITEM2 1
3 7/1/1959 ITEM3 4[/tt]


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]