Rather complex Append Query

[standenman]

I am trying to update client records from information provided in a Zoho online form - a csv file.  The data, however, is spread over several tables and involved one to one and one to many relationships.  

My Zoho form asks my client to give me details on the medications they are currently taking.  But this simple request has implications across the tables.  A medication is a MedicalTreatmentEvent, which has its own autonumber.  And a MedicalTreatmentEvent pertains to a given medical condition, an ClientImpairment which has its own autonumber, ClientImpairmentID.  So when the client enters "Dpression" in the "what for" blank in the Zoho form, we know this entails creating a record in ClientImpairment and getting a ImpairmentID autonumber issued. And of course the MedicationID has to be linked to MedicalTreatmentEventID, so there are just a few moving parts here.  I don't know whether my VBA code should break it up, or try to achieve it in one big append query?

[#2]

cross-post here:
https://www.access-programmers.co.uk/forums/threads/transform-csv-file-to-match-access-table-for-imp...

[#3 · Accepted solution]

@standenman This is as close as I come to a flat out "never".

Never try to append records to multiple tables in a single query.

Start with the One-side tables. Append their records.

Then use the resulting Primary Keys in subsequent appends to the Many-side tables.

Of course, that requires you to have enforced Referential Integrity on all relationships. The screenshot does show that, and that's a potential red flag.

[#4]

@George Hepworth Thanks so much!

[#5]

Related yes, but different question.