Many-to-One Joins

The following example joins the Products table (the focus of the query) with the Suppliers table, which provides lookup information, including the supplier’s company name and city:

SELECT 
	Products.*, 
	Suppliers.CompanyName, 
	Suppliers.City
FROM Suppliers 
INNER JOIN Products 
ON Suppliers.SupplierID = Products.SupplierID;

The user can change data in any field from the Products table, including the SupplierID field, which links the Products table to the Suppliers table. If you change the value of the linking field through a datasheet or a form in a Microsoft Access application, you will also see that the corresponding lookup information from the “one” table is automatically retrieved and redisplayed. This technique is known as row fix-up or AutoLookup.

Although the focus of this many-to-one query is the Products table, it’s also possible (though perhaps not desirable) to change the values in fields retrieved from the lookup table, such as the CompanyName or City field in the Suppliers table. The user may attempt to alter the CompanyName value for one record of the recordset created from this query, under the impression that the change will affect only the current record. However, because the value is actually stored in the Suppliers lookup table, the value is changed for every record in the recordset.