mattkizerian
Technical User
Is it possible to do a nested append query? Here's the situation...
I'm writing a database for an engineering orgainization. Documents must be generated, checked and approved. After the approval process, any changes must be incorporated under a revision.
To generate the revision, I would like to duplicate the current "document" using an append query set with a critria to the PK of the document being revised.
The information that comprises a document resides in three tables with 1-to-many relationships:
tblRevision-->tblInstrument-->tblInstrumentDetail
So, if I want to duplicate (i.e. "revise", record 3 in tblRevision, I'd set my append query's criteria to RevisionID = 3, run the query and get a new set of duplicate records (with their FK being the RevisionID of the new RevisionID). That gives me a duplicate set of Instrument records ready to be revised.
BUT, how do I then take each of these Instrument records and duplicate its set of Instrument Details using an append query? A "nested" append query seems like it would work conceptually, but how would it be constructed? All the examples of nested queries I've seen simply pass a value (or values) from one query to limit another query.
Is this even possible? Or do I have to do this through code (which I could do, but it seems kind of kludgy for some reason).
Sorry this was so wordy--I didn't know how else to clearly explain what was going on. Thanks in advance,
Matt Kizerian
I'm writing a database for an engineering orgainization. Documents must be generated, checked and approved. After the approval process, any changes must be incorporated under a revision.
To generate the revision, I would like to duplicate the current "document" using an append query set with a critria to the PK of the document being revised.
The information that comprises a document resides in three tables with 1-to-many relationships:
tblRevision-->tblInstrument-->tblInstrumentDetail
So, if I want to duplicate (i.e. "revise", record 3 in tblRevision, I'd set my append query's criteria to RevisionID = 3, run the query and get a new set of duplicate records (with their FK being the RevisionID of the new RevisionID). That gives me a duplicate set of Instrument records ready to be revised.
BUT, how do I then take each of these Instrument records and duplicate its set of Instrument Details using an append query? A "nested" append query seems like it would work conceptually, but how would it be constructed? All the examples of nested queries I've seen simply pass a value (or values) from one query to limit another query.
Is this even possible? Or do I have to do this through code (which I could do, but it seems kind of kludgy for some reason).
Sorry this was so wordy--I didn't know how else to clearly explain what was going on. Thanks in advance,
Matt Kizerian
