How to copy linked records (append query?) (1 Viewer)

[vbaDev]

Hi. I am working on a Risk Assessment database.

I have attached an image of the 2 tables concerning my question.

The main "transaction" table is the tblAssessments and a linked table tblRisks drives a subform - showing multiple Risks per assessment.

The field "OccupantID" identifies the facility where Assessments are done (there are other tables, of course).

I am using an OccupantID "00000" to store templates - pre-filled assessments with most common options selected.

I am trying to develop code and/or query or SQL that would do this:

copy all templates (records from OccupantID 00000) and corresponding sub-records from tblRisks into the same tables, but under a different (selected by user) OccupantID.

I have no problem just using an Append Query (actually a SQL statement in VBA with variable parameters), but that only lets me copy into 1 table - so I can copy just the tblAssessments records.

but how do I then copy the tblRisks related records and make sure I attach them to the correct AssessmentID?

Thanks!

 

[Moniker]

Write an append query in the QBE using both tables, and get it to provide the output you want. This will probably be similar to the table/query combination(s) you currently have to populate the main form/subform. Once you have the query written in the QBE (view the results in Datasheet view with actually running it so that the append doesn't take place), switch it to SQL view and just copy/paste the SQL into your code and parameterize what you need to.

 

[vbaDev]

ok, but how will it copy/append into both tables? append query only allows 1 INSERT INTO destination table, no?

 

[Moniker]

It will take a pair of queries. You'll have to play with appending to the main form first, and then base the subform's table append on those changes. Additionally, you can use relationships to do this all in one fell swoop. Enforce referential integrity and a change in one affects the other.

 

[vbaDev]

eh, i still don't get it - is there a way in append queries to append to a "query"?

because if not, i don't see how the system will "know" to which new parent-table record to append the new child-table records. unless it's done iteratively via VBA, but I haven't come up with a way to do that yet either.

 

[vbaDev]

well, i did solve my own problem, so i thought i'd post the solution for consistency:

here's what i did:

in the parent table i added a field called "append_AssmtID" and when copying (appending) assessments this field will store the "old" AssessmentID. I also use a "NewOccID" to assign the new OccupantID, but actually I substitute a variable in VBA for that.

INSERT INTO tblAssessments ( Scope_Description, Applicability, OccupantID, Status, [Level], append_AssmtID )
SELECT tblAssessments.Scope_Description, tblAssessments.Applicability, '00000' AS NewOccID, tblAssessments.Status, tblAssessments.Level, tblAssessments.AssessmentID
FROM tblAssessments
WHERE (((tblAssessments.OccupantID)='36010'));

(see picture 1)

then, I use a 2nd append query, to copy child records, but I use the parent table and link to the new "append_AssmtID" field, thereby making sure the child table's records are attached to the right parent record. Some fields are excluded for compactness. Also, it seems preferable to use "only show rows where the joined fields from both tables are equal" type of link in the query, that way you don't even need the "WHERE" clause.

INSERT INTO tblRisks ( AssessmentID, Include, Exclude_Reason, RiskID, Description, Severity, Probability, Control, Head, Eyes, Ears, Face, Neck, Hands, Arms, [Lung/Torso], Back, Legs, Feet, [N/A], [Safety Precautions/Safe Practices], Significant )
SELECT tblAssessments.AssessmentID, tblRisks.Include, tblRisks.Exclude_Reason, tblRisks.RiskID, tblRisks.Description, 
FROM tblRisks LEFT JOIN tblAssessments ON tblRisks.AssessmentID = tblAssessments.append_AssmtID
WHERE (((tblAssessments.append_AssmtID) Is Not Null));

see picture 2

this certainly works well for me, considering that this copy process only happens once, when a site begins using their version.