Microsoft KB Archive/113885

= ACC: Two Tables Automatically Joined in Query =

Article ID: 113885

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q113885



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



SYMPTOMS
If you create a query based on two or more tables that have matching field names with matching data types, and one of the tables has a primary key defined, Microsoft Access will automatically join the tables on the matching field names.



RESOLUTION
Create the table relationships that you want in the Relationships window before you create new queries. If you do not create the relationships that you want first, you will have to delete the automatic join line in the query.

In Microsoft Access 7.0 and 97, you may disable AutoJoin entirely by clicking Options on the Tools menu, clicking the Tables/Queries tab, and clearing the check box labeled "Enable AutoJoin."



STATUS
This behavior is by design.



Steps to Reproduce Behavior
  In a new database, create a new table as follows and save it:

     Table: Employees ---     Field Name: ID         Data Type: AutoNumber (or Counter in version 2.0) Indexed: Yes (No Duplicates)

Table Properties: Employees ---     PrimaryKey: ID                          Create another new table as follows and save it.

     Table: Customers ---     Field Name: EmployeeID Data Type: Number Field Size: Long Integer Indexed: No     Field Name: ID         Data Type: AutoNumber (or Counter in version 2.0) Indexed: No                        Save the table as Customers. Do not create a primary key. Create a new query based on the Employees and Customers tables. Note that the tables are automatically joined on the ID field, where the desired join is from Customers.EmployeeID to Employees.ID.</li></ol>

To delete the join line, select it, and then press DELETE.

<div class="references_section">