Inserting Records into a Multiple-Table Query

When you’re inserting a record into a recordset based on a multiple-table join, records can be added to one, several, or all of the tables included in the join. Records from the “many” side of the join can be appended to the “many” table as needed. Records from the “one” side of the join can be added as well, as long as they don’t violate any referential integrity constraints.

The following query joins the Products (“many”) table to the Categories (“one”) table:

SELECT 
	Categories.CategoryID, 
	Categories.CategoryName, 
	Products.ProductID, 
	Products.ProductName, 
	Products.SupplierID, 
	Products.CategoryID
FROM Categories 
INNER JOIN Products 
ON Categories.CategoryID = Products.CategoryID;

If, while adding a new record to the Products table, the user specifies a CategoryID value that’s already present in the Categories table, a new record is added only to the Products table. Row fix-up occurs; values from the corresponding record in the Categories table are retrieved.

If the primary key from the Categories table is included in the join, as in the previous example, it’s possible to add a new CategoryID value to the recordset and have that new value automatically added to the Categories table.

In general, values for all required fields, and for the field or fields making up the primary key, must be supplied when you’re inserting a new record into a recordset based on a query. The exception to this rule is for AutoNumber fields (also known as Counter fields). You must not supply an explicit value for AutoNumber fields. The value for AutoNumber fields is automatically assigned by Microsoft Jet.