Ok maybe I was a little vague!! Is there a way to do it all in one update statement? I have never worked with SQL that much. I can pull it out..then another statement to insert and then another to delete the old data.
There is no SQL command to duplicate data. And you are correct, the INSERT command or in Access, an Append Query is what you want.
If the data is critical then you might consider a transaction in VBA. This allows you to do the Append and the Delete and guarantee that both steps were completed successfully. If either step fails then you can rollback the transaction so that neither step is executed.