Microsoft KB Archive/208420

= ACC2000: Two Tables Automatically Joined in Query =

Article ID: 208420

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208420



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, the automatic join line appears anyway.

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.



Steps to Reproduce Behavior
  In a new database, create a new table as follows and save it Employees:   Table: Employees ---  Field Name: ID   Data Type: AutoNumber Indexed: Yes (No Duplicates)

Table Properties: Employees ---  PrimaryKey: ID                      Create another new table as follows:   Table: Customers Field Name: EmployeeID Data Type: Number Field Size: Long Integer Indexed: No

Field Name: ID  Data Type: AutoNumber Indexed: No                    When you save the Customers table, 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.</ol>

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

<div class="references_section">