how do you change data from rows to columns

[grahamnet]

i have a table with info that reads something like this.

a b c d
1 3 2 9

i would like to write some vba code to convert the info from the table above into another table and the format look like this

a 1
b 3
c 2
d 9

any ideas?
ta!

 

[PHV]

Export your table to Excel, play with the TRANSPOSE function and import to Access the transposed table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grahamnet]

that works great but its a bit hard to automate that
any other ideas.
or if it is possible to automate this process let me know
please.
ta!

 

[PHV]

In access VBA take a look at the DoCmd.TransferSpreadsheet method.
In excel, play with the macro recorder to discover how executing worksheets function in VBA.
And then you may consider automate the whole process from within access with OLE automation.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SiberBob]

Couldn't you write the values into an Array(R,C) and then read the values from the Array(C,R)?

 

[grahamnet]

EXAMPLE CODE WOULD BE GREAT!

 

[Zion7]

Here's an example of what SiberBob suggested.
Dim r, f , iRecCount as integer
Dim vArray as Variant
Dim rec as ADODB.Recordset
Set rec = ADODB.Recordset
rec.Open "TableName",CurrentProject.Connection,acOpenDynaset, acLockOptimistic

iRecCount = rec.RecordCount

vArray = rec.GetRows(iRecCount)
rec.Close

The GetRows method, extracts ALL the fields from your table,
the argument passed, tells how many records to extract.

I asked for all(rec.RecordCount).

vArray is a 2 dimensional array(Get rows is always 2 dim.)
vArray(a,b) a is the fields, b are the rows(or records).

To get the results from vArray, you loop through both dimensions.

Dim iFieldCount,iRowCount as integer

iFieldCount = UBound(vArray,1) gives count of fields(1st dimension)
iRowCount = UBound(vArray,2) gives count of Records(2nd dimension)


First we loop through 1st record
For r = 0 to iRowCount 'hold on first record,loop through all fields

For f = 0 to iFieldCount
In here you write code to append new record.(Whatever method you use...Insert Query, ADO AddNew ...)

txtNewField = vArray(f,r)
Next f
Next r


This sequence will give all fields in first record, then go to next record & show all fields. So when you append, remember the order your results
are coming in. Fields First & then rows & exactly how your original table had their ordinal positions.
..prepare your new table accordingly.

Hope this helps, good luck!

 

[Zion7]

Actually grahamnet, I'm still at a bit of a loss at what you're trying to achieve. I don't see how Field1, Field2, Field 3, are going to become Field1 in record1, Field1 in record2, Field1 in record3?

but, my point is, if that is the case, my code is not complete.

..first, just loop through fields of 1st record to establish all new records

For f = 0 to iFieldCount

v1stRecord = vArray(f,0) '0 remains on 1st record
Rec2.AddNew
Rec2!FirstField = v1stRecord
Rec2.Update

Next f

Next same procedure, but only update into 2nd field

For f = 0 to iFieldCount

v2ndRecord = vArray(f,1) '1 get 2nd record

Rec2!SecondField = v2ndRecord
Rec2.Update

Next f

This is not very dynamic, I would need more time, but, I believe, still viable. It should offer some options.

If need be, let me know if I need to elaborate more.

Good Luck!