data organisation 2

[brightstar]

Hi,
I have a table with about 6000 rows in the following format:

field name: field1 field2 field3
row data: data11 data21 data31
row data: data12 data22 data32
row data: data13 data23 data33

and i want to get it into format:

field name: anotherfield1 anotherfield2
row data: field1 data11
row data: field1 data12
row data: field1 data13
row data: field2 data21
row data: field2 data22
row data: field2 data23
row data: field3 data31
row data: field3 data32
row data: field3 data33

does that make sense?
any ideas anyone?
not bothered if its done in a query, or ADO, DAO, anything is good if it works!

http://www.agonyonline.com

free, anonymous advice provided by the whole world

 

[bboffin]

If I read it correctly you want the name of the field in column anotherfield1 and the value in anotherfield2.

Try something like:

INSERT INTO NewTable (anotherfield1, anotherfield2)
SELECT "Field1", field1 FROM OldTable
UNION
SELECT "Field2", field2 FROM OldTable
UNION
SELECT "Field3", field3 FROM OldTable

I may not have the syntax quite right for Access but this would be OK for SQL Server.

If the UNIONs don't work use three separate INSERT queries one for each field.



Bob Boffin

 

[Golom]

Along these general lines

(Select 'field1name' As [Anotherfield1], 
        field1       As [Anotherfield2]
 From   TheTable)

UNION ALL

(Select 'field2name' , 
        field2       
 From   TheTable)

UNION ALL

(Select 'field3name' , 
        field3       
 From   TheTable)

 

[brightstar]

marvellous, between the 2 of you i go tit working in vba using a mixture of select case and do while in addition to your sql.

cheers guys.

http://www.agonyonline.com

free, anonymous advice provided by the whole world