Copying Fields from one table to another

[TidyTrax]

I have a number of fields in one table that i wish to copy from that table to other smaller tables. For example the main table has approx 140 fields, and i wish to take the values for each record of fields 1,2,3 and place them in a smaller table, is there a piece of code that i can use to do this??

 

[TonyJollans]

TidyTrax,

Assuming we're talking Access here, you can use a Make Table Query (or an Update or Append Query if the target Table already exists).

Or do you want to do it in code? If you do, create a sample query and then copy the generated SQL into a code module and use DoCmd.RunSQL to run it.

Enjoy,
Tony

 

[TidyTrax]

any chance of an example of that code? Just getting used to vba!

 

[TonyJollans]

Hi TidyTrax,

In a Code Module just add this, literally one line, routine, and run it. You'll need to supply your own names of course.

Sub CreateNewTable()
DoCmd.RunSQL "SELECT OldTable.Field1, OldTable.Field2, OldTable.Field3 INTO NewTable FROM OldTable;"
End Sub

If you want something more flexible, ask again but I'll be packing it in for the night shortly.

Enjoy,
Tony

 

[TidyTrax]

I was assuming that i could use this piece of code to take the appropraite field and put it into the approriate field

DoCmd.RunSQL "SELECT tblTest.F1 INTO tblInput.location FROM tblTest;"

But this gives me an error, i can easily create a new table with the command

DoCmd.RunSQL "SELECT tblTest.F1 INTO tblTest2 FROM tblTest;"

But when it comes to trying to put the output of the sql query into an already created field of an existing table it doesnt work - any ideas???

 

[TonyJollans]

Hi TidyTrax,

Do you want to add new rows to the existing new table or update existing rows?

To add new rows use:

INSERT INTO NewTable ( Field1, Field2, Field3 ) SELECT OldTable.Field1, OldTable.Field2, OldTable.Field3 FROM OldTable;

To update existing rows is slightly more complex because you need to identify the target record. Come back if that's what you need.

Enjoy,
Tony

 

[TidyTrax]

I had a just caught on to using the Insert statement, its been 4 and a bit years since i did Sql, so its slowly coming back!

Thanks a lot Tony you've been a great help, much appreciated

 

[TidyTrax]

Out of interest Tony, how do you update an existing row??

 

[TonyJollans]

Hi TidyTrax,

Off the top of my head it's along the lines of ..

UPDATE NewTable Inner Join OldTable on NewTable.Field1 = OldTableField1 Set NewTable.Field2 = OldTable.Field2

Enjoy,
Tony