Table to Table

[DirtDawg]

I have table1 that has many columns. the columns are names as follows voy_no, and ar_dt, from 1 to 12. now I have table2 with 2 columns voy_no and ar_dt. I would like to take all the columns voy_no with rows from table1 and input to table2 into voy_no. Is this possible?

 

[KenReay]

Hi

How about:

INSERT INTO Table2
SELECT voy_no, ar_dt1 As ar_dt
UNION
SELECT voy_no, ar_dt2 As ar_dt
UNION
SELECT voy_no, ar_dt3 As ar_dt
..etc to
UNION
SELECT voy_no, ar_dt12 As ar_dt

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[DirtDawg]

Let me explain better. From table1 there is voy_no1-voy_no12 and ar_dt1-ar_dt12 with row underneath. I would like to import all of voy_no1-voy_no12 into table2 voy_no and then ar_dt1-ar_dt12 into ar_dt in table2. Can it happen?

 

[JeremyNYC]

OK, here's some SQL that does NOT work!. My thought would be to make some code that does twelve of these in a loop, though someone else might be able to figure out how to make the sql work. I'll post code below.

INSERT INTO tblTestSingle ( v, a )
SELECT tblTest.V1, tblTest.a1
FROM tblTest
WHERE (((tblTest.V1)="1&quot)

UNION

SELECT tblTest.V2, tblTest.a2
FROM tblTest
WHERE (((tblTest.V2)="2&quot)
UNION

SELECT tblTest.V3, tblTest.a3
FROM tblTest
WHERE (((tblTest.V3)="3&quot)

The code would look something like this, though this is untested aircode:

sub blah
dim strSql as string
dim db as dao.database
dim iCount as integer

set db = currentdb
for iCount = 1 to 12
strsql = "INSERT INTO tblTestSingle ( v, a ) " _
"SELECT tblTest(V" & iCount & &quot, tblTest(a" & iCount _
& &quot " _
& "FROM tblTest " _
& "WHERE (((tblTest.V" & iCount &&quot=" & iCount & &quot)"
db.execute(strsql, dbfailonerror)
next iCount

end sub

Jeremy

(PLEASE back up your data before trying this)
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[JeremyNYC]

OK, here's some SQL that does NOT work!. My thought would be to make some code that does twelve of these in a loop, though someone else might be able to figure out how to make the sql work. I'll post code below.

INSERT INTO tblTestSingle ( v, a )
SELECT tblTest.V1, tblTest.a1
FROM tblTest
WHERE (((tblTest.V1)="1&quot)

UNION

SELECT tblTest.V2, tblTest.a2
FROM tblTest
WHERE (((tblTest.V2)="2&quot)
UNION

SELECT tblTest.V3, tblTest.a3
FROM tblTest
WHERE (((tblTest.V3)="3&quot)

The code would look something like this, though this is untested aircode:

sub blah
dim strSql as string
dim db as dao.database
dim iCount as integer

set db = currentdb
for iCount = 1 to 12
strsql = "INSERT INTO tblTestSingle ( v, a ) " _
"SELECT tblTest(V" & iCount & &quot, tblTest(a" & iCount _
& &quot " _
& "FROM tblTest " _
& "WHERE (((tblTest.V" & iCount &&quot=" & iCount & &quot)"
db.execute(strsql, dbfailonerror)
next iCount

end sub

Jeremy

(PLEASE back up your data before trying this)
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.