Subquery: Add Missing Master Records

Let's say you have some data to add to your detail table, but some of the master records are missing. Here's a example query that uses a subquery to add "missing records" based on matching an ID field between two tables.

INSERT INTO tblMasterRecords ( MyID, MyText )
  SELECT MyID , "New Master record"
  FROM tblNewData
  WHERE NOT EXISTS
      (SELECT * FROM tblMasterRecords WHERE MyID=tblNewData.MyID);

Some things to note here:

• The query in the brackets is called a subquery.
• The EXISTS condition resolves to true when the subquery returns any records. By using this in the WHERE NOT EXISTS syntax, the main query returns records from tblNewData only when the subquery doesn't return any records.
• Inside the subquery, the ID of current record in the main query is referenced as tblNewdata.MyID.