ACC2000: AutoLookup Query Not Performed with a One-to-One Relationship (208916)

   Table 1                  Table 2
   ----------------------   ----------------------
   Primary Key              Primary Key
   Primary Key              Indexed, No Duplicates
   Indexed, No Duplicates   Primary Key
				

MORE INFORMATION

Consider the following tables:

   Table: Employees
   ----------------------------
   Field Name: EmployeeID 
   Data Type: AutoNumber
   Indexed: Yes (No Duplicates)

   Field Name: LastName
   Data Type: Text

   Table Properties: Table1
   -------------------------------
   PrimaryKey: Category ID
   Index1: Category Name; Location


   Table: Offices
   ----------------------------
   Field Name: OfficeID 
   Data Type: AutoNumber
   Indexed: Yes (No Duplicates)

   Field Name: AssignedTo
   Data Type: Text

   Table Properties: Table1
   -------------------------------
   PrimaryKey: Category ID
   Index1: Category Name; Location
				

To ensure that an employee is not assigned to more than one office, add a unique index to the Offices.[AssignedTo] field. It doesn't matter whether there is no relationship, a one-to-many relationship, or a one-to-one relationship defined between these tables; Access determines that the relationship is one-to-one when determining whether to perform an AutoLookup query.

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Open the sample database Northwind.mdb and create the following new table:

      Table:  Offices
      ------------------------
      FieldName: OfficeID
      Data Type: AutoNumber
      Field Size: Long Integer
      New Values: Increment
   
      FieldName: Building
      Data Type: Text
   
      FieldName: AssignedTo
      Data Type: Number
      FieldSize: Long Integer
      DefaultValue: <blank>
   					

2. Click the OfficeID field, and then click Primary Key on the Edit menu.
3. View the table in Datasheet view, and then add the following data to the table:

      OfficeID   Building   AssignedTo
      --------------------------------
          1          A
          2          A
          3          B
   					

4. Create a new query based on the Employees and Offices tables.
5. Join the tables on the Employees.[EmployeeID] and Offices.[AssignedTo] fields.
6. Drag all the fields from the Offices field list to the query design grid, and then drag the FirstName and LastName fields from the Employees field list to the query design grid.
7. Run the query. Type 7 in the AssignedTo field, and then press ENTER. Note that the AutoLookup query fills in the FirstName and LastName fields for the record.
8. Save the query as AssignOffices, and then close it.
9. Open the Offices table in Design view.
10. Set the Indexed property of the AssignedTo field to Yes (No Duplicates).
11. Save the table and close it.
12. Run the AssignOffices query. Type 3 in the first blank AssignedTo field and press ENTER. Note that Access does not fill in the FirstName and LastName fields.
13. Press SHIFT+F9 to run the query again.
    
    Note that the fields are filled.