Hi Zoe
Easily done with one SQL statement. Check Richard's SQL seminar 1 and especially 2.
Access SQL Seminars

Rather than going that route, I would suggest you keep all of the same stuff in one table. You've got potential orders and final orders, but when you think about it, they both represent orders. So why don't you just add a field called "order type" that can store whether it's potential or final? It's like in my full course database where we have quotations and invoices. Essentially, a quotation is before the customer agrees to buy it and an invoice is after they've bought it. It's literally just one checkbox that switches between the two.

So instead of trying to move records between multiple tables when the records represent the same thing, keep all of the same things in the same table. But if you don't want to do that and rebuild your database, then yes, it could be easily done with an append query. I cover them in detail in the SQL seminar part 2, which is what Sami recommended.

You'll not only have to append the order record, but also any child line items, which I'm assuming you have in a different table. But I don't know your database setup, so I can't tell you for sure. I also cover append queries in Access Expert 14 and Access Expert 22.

Archive Records might help you too.

hi Sami and Richard, thank you both! These are very helpful, let me check out the seminar,. Really appreciate the quick response.