Duplicate Master-Child records (versioning)

[jerem]

Hi,


I am trying to create a versioning function in Ms Access. That means that I have a bunch of related data that I would like to duplicate with a new version number, also the child records should be duplicated and linked to the newly duplicated items. I can't get my head around what the best approach would be. Do I need to do that in a loop that goes through each record, duplicates it, then uses the newly created PK (e.g. ID) to create the child records with that PK as FK (e.g. OrderID) ? Or is there a way to do that in one query?
Thanks!

[June7]

What you describe sounds like an audit log.

Review: http://allenbrowne.com/AppAudit.html

Otherwise, why don't you just save copy of db every day?

[jerem]

Thanks June but not really... Maybe by explaining how the data presents itself: There is an item which is the source of the main form. Then there is a subform (continuous form) with a Master/Child link with a bunch of sub-items. Then there are two other subforms (continuous forms) with parameters with a Master/Child link to the sub-item subform. So the table structure is One Item to Many sub-items. Then each one sub-items to many parameters1 and many parameters2. The user wants to be able to save/freeze a version (version 1) of all the items with all the related sub-items and parameters, and start with a copy of all that to make changes (version 2). Then he wants to be able to switch between version 1 and 2.
So the idea is to keep the item as is but duplicate all the sub-items and parameters, and create the link (PK->FK) between the duplicated sub-items and the duplicated parameters.
I know I could create a loop that goes through each records get the PK in a variable then run an append query on the child table with the saved PK variable as a FK... But could isn't there a cleverer way to do that all in one time. or in one query?
Thank you.

[June7]

This has to be the most bizarre requirement I've ever read.

How many 'versions' does user want - how many generations of versions? Just keep the original and then keep doing edits on the second? Don't care about keeping all the edits?

So every time a new record is created, make a copy?

Not sure what you mean by 'all in one time, or in one query'. If you need to grab the parent and all children (and grandchildren) records and copy them to respective tables, no, I don't think there is another way.

[jerem]

Ok Thanks!