MS Access SQL Insert data into table with relationship

Question

I have been searching everywhere for a solution to this problem and I have concluded that I may be asking the wrong question. I am hoping that someone here can answer this question or tell me the right question to ask.

I have two tables Estimates and TakeoffSheet:

    +----------------------+  +----------------------+
    |   Estimates          |  |  TakeoffSheet        |
    +----------------------+  +----------------------+
    | 1. Estimate_ID = PK  |  | 1. Sheet_ID = PK     |
    | 2. Number            |  | 2. Estimate_ID = FK  |
    | 3. Estimate_Name     |  | 3. Sheet_Name        |
    +----------------------+  +----------------------+

I am trying to insert a new row in Takeoffsheet where Estimate.Number=Something. Essentially there is a relationship defined between TakeoffSheet and Estimates however I dont know how to insert a new row in Takeoffsheet, with an Estimate_ID from Estimates. At least not in one SQL statement.

I know I can do it in multiple steps (first get the Estimate_ID, then add a new sheet with that) but I would rather do it in a single statement if possible. Is there a "join" type of insert in MS Access?

Thanks for any help!

Answer 1

If you defined the foreing keys, and created a View e.g. V_TakeOffSheet you will be able to append records per SQL like

Insert into V_TakeOffSheet (Sheet_Name,[Number],Estimate_Name) values ('Sheet1',1,'Est1');

Access will set Estimate_ID to the record fitting in Number and Estimate_Name, if this record is not existing it will be created and the new Estimate_ID will be used in TakeoffSheet .