Creating a new table

  • Thread starter Thread starter Peter Smith
  • Start date Start date
P

Peter Smith

Hi

I have a table that I want to split. I want to put some of the feilds in a
separate table.

I need to write a routine to examine each record to see if I need to create
a record in the new table, then transfer the needed data to that new
record; next record; repeat to EOF.

Open tblTable
MoveFirst
Do
See if new record is needed in tblNewTable
Append data to tblNewTable
MoveNext
Loop While not EOF(tblNewTable)
End

Any idea how to do this? Should I use SQL?

TIA

- Peter
 
Peter

Are the criteria by which you will select records amenable to being queried?
Another approach, rather than iterating through every row, would be to
create an append query.
 
Hi

I have a table that I want to split. I want to put some of the feilds in a
separate table.

I need to write a routine to examine each record to see if I need to create
a record in the new table, then transfer the needed data to that new
record; next record; repeat to EOF.

Open tblTable
MoveFirst
Do
See if new record is needed in tblNewTable
Append data to tblNewTable
MoveNext
Loop While not EOF(tblNewTable)
End

Any idea how to do this? Should I use SQL?
Click to expand...

SQL will be MUCH more efficient:

INSERT INTO newtable
SELECT (field1, field3, field4, field7)
FROM oldtable
WHERE <criteria>;

This query can be created in the Query grid. Get the fields and
criteria set up first, using just the default select query; then use
the query type icon to change it to an Append query, and run it using
the ! icon.

It CAN be done in VBA code, using Recordsets, but doing so is slower,
bloats your database more rapidly, and has very few reasons to do so.
About the only reason would be if the criteria for deciding which
records to append are so complex that they cannot readily be done in a
query, but can be done in code.
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top