Copy column to a Row ?

  • Thread starter Thread starter saustin
  • Start date Start date
S

saustin

Hi All,
Have a column (60 records) that i need to copy to
a row (to become a new header row). It is possible ? Any
ideas appreciated. Thanks, Steve.
 
Hello Steve,

Open two recordsset, likely in a button. RecordsetA will be for the table
receiving the data and Recordsetb for the table with the 60 row.

Create a new record in Recordset A using the AddNew method.

Loop Through Recordsetb assigning the values from each record to the new
record in RecordsetA.

When you are done looping, Update RecordsetA.

So it would be like this:

Dim rstA as RecordSet
Dim rstB as RecordSet
Set rstA = CurrentDb.OpenRecordSet("RecordsetA",DbOpenDynaSet)
Set rstB= CurrentDb.OpenRecordSet("RecordsetB",DbOpenDynaSet)

rstA.AddNew
rstB.MoveFirst
Do Until rstB.EOF
Select Case [RecNo] 'Or any other method of Identifying the Record
being addressed. Not knowing your Data I am just deomonstrating this.
Case 1
rstA![Field Whatever] = rstB![FieldWhatever]
Case 2
rstA![Field Whatever] = rstB![FieldWhatever]
 
Hi Steve,

Not sure I've got your true meaning, so I've waited for the NG, but in the
absence of replies ...

When you say "header row", do you mean: as though you were about to create
an export file?

In that case, you would want something like:
"Cust Id", "Company name", "Contact Surname", "Contact Forename" ....
You could write a small loop to concatenate each of your 60 rows into that
type of string.

Alternatively, you might mean that you have a table with 60 rows of a single
column and another table with 60 columns into which you wish to port the
data. Again that's a small loop.

On the other hand, you might have something completely different in mind?

CD

.... sample code completely untested

Function Built_Export_Header$
Dim wk$, q$, sep$, rs As DAO.Recordset
q$ = Chr(34)
sep$ = ", "
wk$ = ""
Set rs = CurrentDb.OpenRecordset("SourceTable",dbOpenDynaset)
Do
If rs.EOF Then Exit Do
wk$ = wk$ & sep$ & q$ & rs![SourceColumnName] & q$
' *^* resultant syntax fault if embedded quote exists in column
data content
' ... should really call a quote_protect function to ensure that
can't happen
rs.MoveNext
Loop
Set rs = Nothing
Built_Export_Header$ = Mid$(wk$,Len(sep$)+1)
End Sub

Function Pivot_Col_to_Row
Dim rsS As DAO.Recordset, rsD As DAO.Recordset
Dim i%

Set rsS = CurrentDb.OpenRecordset("SourceTable",dbOpenDynaset)
Set rsD = CurrentDb.OpenRecordset("DestinationTable",dbOpenDynaset)
i = 0 : rsD.AddNew ' open up new, empty record
Do
If rsS.EOF Then Exit Do
rsD(i) = rsS![SourceColumnName]
i = i+1
rsS.MoveNext
Loop
rsD.Update
Set rsS = Nothing
Set rsD = Nothing
End Sub
 
Back
Top