Duplicating records and related data

[Chrisopia]

I found this incredible step by step into duplicating data:

http://allenbrowne.com/ser-57.html

I am using it for invoice purposes, a product may be similar to a previous one that was already entered into the system.
We have so many products, with so many variables, this will be a useful tool to replicate long winded, tedious descriptions of products which may occur time and time again as a repeat order.

I figure a "enter invoice number" box will pop up, then it will take the Invoice ID and replicate all the data, plus that in the related "tblOrders"

My issue, which the walk through doesn't address, is that "tblOrders" is related to yet another table "tblDescriptions"

I need all of that information to be replicated.

[ tblinvoice ] --< [tblOrders ] ---< [ tblDescriptions ]

Now I'm just getting into SQL. It runs smoother and cleaner than an access built query in my opinion, I can just make out what the walkthrough is describing, but my head gets boggled where to begin the code for the next loop for the sub-children.

Any help will be greatful!

 

[Pat Hartman]

If tblDescriptions is a lookup table, it doesn't need to be duplicated. The new record would point to the same description as the copied record.

 

[Chrisopia]

No, unfortunately, it isn't a lookup table.
It contains yet more variables. heres an example:

[tblInvoice]: invoiceNumber: "0001", TotalDue: "£50"
-[tblOrder]: QTY: "5", Description: "Mounted Boards", Price: "£45"
---[tblDescription]: Size:"A2", Stock:"Glossy sticker", Colour: "4", Finish:"Laminate"
---[tblDescription]: Size:"A2", Stock:"5mm Foam Board", Colour:"White", Finish:"Mounting"
-[tblOrder]: QTY: "1", Description: "Setup Fee", Price: "£5"

See in tblDescription, there are so many variables, in colour, stock, stock type, print type, start size, finish size, we can't possible make pre-sets for everything. The stock inventory would be endless. Though each field in tblDescription could be a lookup field, I still need to figure out how to replicate tblDescription, and not just point it to the ID of the field that should be replicated.

 

[Pat Hartman]

Once you have created
---[tblDescription]: Size:"A2", Stock:"Glossy sticker", Colour: "4", Finish:"Laminate"
why is it a problem to point to it? Why does it have to be duplicated?

 

[Chrisopia]

It needs to be duplicated to be edited.
It is to be used as a repeat order button, or as a "similar product" item,

In this example, another customer may buy mounted boards, but they may buy 3 instead of 5, or they may be A1 instead of A2 size, but saves retyping all that information

 

[Pat Hartman]

Use the same technique to copy these records as you do to copy tblOrders.