ACC: AutoLookup Query Not Performed with 1-to-1 Relationship

ID: Q121332

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

Microsoft Access will not perform an AutoLookup query if it determines that the tables in the query have a one-to-one relationship.

CAUSE

Microsoft Access determines the relationship between the tables in the query by examining the indexes on the join fields. Microsoft Access will ignore the relationship that you specify when it is determining whether to perform an AutoLookup query. If your tables have one of the following index combinations, Microsoft Access will not perform an AutoLookup query:


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

RESOLUTION

To cause Microsoft Access to perform an AutoLookup query, make sure that the foreign key is neither indexed as the Primary Key nor Indexed, No Duplicates. It may be necessary to add a unique index to the foreign key on the "many" side of a relationship in order to maintain the integrity of the data. In this case, AutoLookup will not work and a workaround will have to be used.

MORE INFORMATION

Consider the following tables:


   Employees     Offices
   ----------   ----------
   EmployeeID    OfficeID
   LastName      AssignedTo

To ensure that an employee is not assigned to more than one office, add a unique index to the Offices.[AssignedTo] field. Regardless of whether there is no relationship, a one-to-many relationship, or a one-to-one relationship defined between these tables, Microsoft Access will determine that the relationship is one-to-one when determining whether to perform an AutoLookup query.

Steps to Reproduce Behavior

Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0) 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>

Select the OfficeID field, and then click Primary Key on the Edit menu.

View the table in Datasheet view, and then add the following data to the table:


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

Create a new query based on the Employees and Offices tables.

Join the tables on the Employees.[EmployeeID] and Offices.[AssignedTo] fields.

Drag all the fields from the Offices field list to the query grid, and then drag the FirstName and LastName fields from the Employees field list to the query grid.

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.

Save the query as AssignOffices, and then close it.

Open the Offices table in Design view.

Set the AssignedTo field's Indexed property to Yes (No Duplicates).

Save the table and close it.

Run the AssignOffices query. Type 3 in the first blank AssignedTo field and press ENTER. Note that Microsoft Access does not fill in the FirstName and LastName fields.

Press SHIFT+F9 to requery. Note that the fields are filled.

REFERENCES

For more information about AutoLookup queries, search the Help Index for "AutoLookup queries," or ask the Microsoft Access 97 Office Assistant.

Additional query words: row fix-up updateable

Keywords : QryFixup
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb