INSERT INTO more than one table

[TonyTOCA]

I have the following problem

I'll give you an outline first. I have data in a delimited-textfile
from a labinstrument which i wanna add on a daily bases to my own
database used for storing labresults . The relevant tables in my
database are:
tbPatientInformation,: piID (pk), piLastName, piFirstName, piDOB etc
etc.
tbLabtest: ltID (pk), ltName, ltFullName, etc etc
tbTestResults: trID(pk), piID, ltID, trResult, trTestDate etc etc

I managed to import and extract the relevant data (using the
transfertext method) from the textfile into a temp table,
tbImportTemp, with the following fields : Patientname, Testname,
TestResult, TestDate. I then have another temp table, tbResultsTemp,
where i split the patientname into lastname and firstname using a
function (ParseWord, Allen Brown) in an APPEND query. So now i have a
table with the following fields: LastName, FirstName, Testname,
TestResult, TestDate. LastName and FirstName should go into
tbPatientInformation, after grouping them (1 patient can have many
tests). Testname, TestResult and TestDate should go into
tbTestResults. Testname from tbResultsTemp has the same values as
ltName from tbLabtest, but i can't insert it right away into
tbTestResults cuz i need the ltID corresponding to it.

My question is: how do i accomplish this-> Group the patients and then
insert LastName and FirstName into tbPatientInformation and at the
same time inserting the results (Testname, TestResult and TestDate)
into the results table with the appropriate piID and ltID.

Checking for duplicates will be done afterwards when the DOB's and the
rest of the patient information are being entered, but i'll get back
to this in another thread.

I would appreciate any help on this.

 

[[MVP] S.Clark]

I would accomplish this with VBA code. Which inserts each parent, returns
the new ID, then writes the child records.

 

[TonyTOCA]

Thx Steve

VBA code, ok....could you give me an aircode example?