Copy record to another record with other recordformat

[dlm]

Hi,


How can I copy 3 different records to one record?

I have 3 records with the same recordlength but with different fields and I want them to place into a new table with only one field. That field has the same size than the recordlength of the other 3 records.

Can somebody help me please..? Thanks in advance...

[paulw]

This is not very clear - do you wnat to combine the records as a string or combine the tables as multiple lines

a, b , c => abc

or

a, b, c => a
b
c

Which one. They are both easy.

P.

[dlm]

Paulw,

It 's the second one I need

a, b, c =>
a
b
c

Thanks for your reply...

[paulw]

Set up a recordset to receive the data and then loop through each table in turn, or more simply use Append queries.

INSERT INTO TableFinal (TargetField) SELECT Table1.Field FROM Table1
INSERT INTO TableFinal (TargetField) SELECT Table2.Field FROM Table2
INSERT INTO TableFinal (TargetField) SELECT Table3.Field FROM Table3

OK?

P.

[dlm]

paulw,

I'm afraid it's a bit harder than that...

I 've got 3 tables: A, B, C
with a different recordlayout, but with the same recordlength, meaning :

table A got Field1, Field2, Field3,Field4,Field5 and Field6 table B got Field1, Field2, Field3, and Field4
table C got Field1, Field2, Field3,Field4 and Field5

Only Field1 has the same name in the 3 tables, the rest of
the fieldnames have an ohter name.

Now I have to get the 3 tables in a new one table D, with only one big field, in the following order:

D.Field1 = A.Field1 & A.Field2 & A.Field3 & A.Field4
& A.Field5 & A.Field6
next record in D
D.Field1 = B.Field1 & B.Field2 & B.Field3 & B.Field4
next record in D
D.Field1 = C.Field1 & C.Field2 & C.Field3 & C.Field4
& C.Field5
next record in D
D.Field1 = A.Field1 & A.Field2 & A.Field3 & A.Field4
& A.Field5 & A.Field6

Hoping you know an solution for this...

DLM

[paulw]

To be honest, you need to sort out the design first. What do these fields represent? Doesn't sound too well normalised!!!

You can loop through the fields in a recordset and add them to the target.

i.e. If MyRecordset represents the table that you are taking data from...

With MyRecordset Do
  If Not .EOF And Not .BOF Then 
    .MoveFirst  
    Do While Not .EOF  
      For j = 0 To .Fields.Count - 1
        MyTarget.AddNew 
        MyTarget!TargetField = .Fields[j]
        MyTarget.Update
      Next i
      .MoveNext
    Loop
  Else
    MsgBox "No Data"
  End If
End With

Cheers,

P.

[dlm]

Thanks paulw...

it works...