MS Access SQL 'Deep-copy' Query Help...

[tizmagik]

MS Access Database

I'm working on a 'Reorder' function for my friend's database.
Essentially, it is a 'deep-copy' of several related tables. Here is
the table setup...

tblSalesOrder
SalesOrderID (PK) Field1, Field2, etc

tblWorkOrder
WorkOrderID (PK), SalesOrderID (FK), Field1, Field2, etc

tblOperation
OperationID (PK), WorkOrderID (FK), Field1, Field2, etc

I am able to successfully create a 'reorder' of the SalesOrder and
WorkOrder but I am having trouble with the Operation per WorkOrder.

Sample Data:

tblWorkOrder
WorkOrderID SalesOrderID Field1
25 6 ExWO1
26 6 ExWO2
27 13 ExWO1
28 13 ExWO2

tblOperation
OperationID WorkOrderID Field1
3 25 ExOp1
4 25 ExOp2
5 26 ExOp3

Note, this sample is taken AFTER the reorder at the SalesOrder and
WorkOrder level.
Desired data after the reorder at the Operation level would be:

tblOperation
OperationID WorkOrderID Field1
3 25 ExOp1
4 25 ExOp2
5 26 ExOp3
~
6 27 ExOp1
7 27 ExOp2
8 28 ExOp3

Where 6,7,8 are the 'new' records...

I tried my hand at a few SQL queries myself, but I keep hitting a dead
end. I would supply them here, but I'm afraid they probably wouldn't
be of much help.

Please let me know if there is anything else I could provide to help
with finding a solution. Thank you!

[tizmagik]

James,

That is quite a clever solution, however, I would much rather not have
to change the table definitions around, if possible. Instead, perhaps
your solution could be implemented with a temporary hash-table? I will
look into this.

Otherwise, are there any other, VBA/SQL-based solutions that anyone
could think of?

Thanks for your time!