Append Query with a foreign key

[dougperk]

I have 3 linked tables (SQL tables) and I want to use a single form to input data that will be used to populate the 3 tables. SQL will auto-number the PrimaryKey in each of the 3 tables, but I don't know how to construct the Append Query so that Tables 2 & 3 will get the foreignkey value to Table 1.

Table1
Patient_id (primarykey)
First_Name
Last_Name

Table2
Addr_Id (primarykey)
Patient_id (foreignkey to Table1)
Addr_1
Addr_2
...etc

Table3
Insuance_id (primarykey)
Patient_id (foreignkey to table1)
Carrier_Name
...etc

My input Form will contain all the fields necessary for the 3 tables. (NOTE: the form will NOT be providing the 3 primarykeys, as SQL assigns these.)
I assume I will need 3 separate Append Queries.


Writing the first query to populate Table1 is easy.
How, though, do I write the Append Query for Table2, so that Table2.Patient_id will pull the value of the just created Table1.Patient_id ?

I know the easiest approach would be to create a Main/Subforms setup to accomplish this. But with that I cannot figure out how to make Table 2 and Table 3 REQUIRED. (ie I do not want a Table1 entry to be created with a corresponding Table2 and Table 3 entry.)

Thanks in advance
Doug

[June7]

Why do you need APPEND?

Are you using unbound forms?

Have you tried form/subform arrangement?