INSERT DATA INTO TABLE FROM DIFF TABLE

[pelagic]

I have 2 tables with the following fields.

Table1
Field1 Field2 Field3 Field4 Field5 Field6
1 4 6
A B C
5 8 1
Table2 (Empty table without data)
Field1 Field2 Field3

I like to write a query that take data from table1 and insert into table2. The result in table2 shall be.

Table2
Field1 Field2 Field3
1 4 6
A B C
5 8 1


Thank you very much for your help

 

[PHV]

In a standard code module create the following function:

'A generic function to get the Xth element of an arbirtrary numbers of same type values:
Public Function getXth(x As Integer, ParamArray Args())
If x <= 0 Or x > UBound(Args) + 1 Then Exit Function
Dim i As Integer, j As Integer
For i = 0 To UBound(Args)
  If Trim(Args(i) & "") <> "" Then j = j + 1
  If j = x Then
    getXth = Args(i): Exit Function
  End If
Next
End Function

And now your query:
SELECT getXth(1,Field1,Field2,Field3,Field4,Field5,Field6) AS Field_1, getXth(2,Field1,Field2,Field3,Field4,Field5,Field6) AS Field_2, getXth(3,Field1,Field2,Field3,Field4,Field5,Field6) AS Field_3
FROM Table_1;

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

 

[pelagic]

Is there a getXth function in Access?
I tried your query and It did not work.
getXth function is not define.


Thanks

 

[PHV]

Just to know, have you read the beginning of my post ?
I said you to create this getXth function !

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