ACC2000: AutoLookup Query Not Performed with a One-to-One Relationship
ID: Q208916
|
The information in this article applies to:
Advanced: Requires expert coding, interoperability, and multiuser skills.
SYMPTOMS
Microsoft Access does not perform an AutoLookup query if it determines that the tables in the query have a one-to-one relationship.
CAUSE
Access determines the relationship between the tables in the query by examining the indexes on the join fields. Access ignores 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, Access does 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 Access to perform an AutoLookup query, make sure the foreign key is neither indexed as the Primary Key nor as Indexed, No Duplicates. You may have 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.
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: Following the steps in this example will modify the sample
database Northwind.mdb. You may want to back up the Northwind.mdb file
and perform these steps on a copy of the database.
- 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>
- Click 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 design grid, and then drag the FirstName and LastName fields from the Employees field list to the query design 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 Indexed property of the AssignedTo field 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 Access does not fill in the FirstName and LastName fields.
- Press SHIFT+F9 to run the query again.
Note that the fields are filled.
REFERENCES
For more information about AutoLookup queries, click Microsoft Access Help on the
Help menu, type autolookup queries in the Office Assistant or
the Answer Wizard, and then click Search to view the topics
returned.
Additional query words:
row fix-up updateable prb
Keywords : kbdta QryFixup
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb