ACC2000: Two Tables Automatically Joined in Query (208420)

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.

MORE INFORMATION

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.

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

REFERENCES

For more information about joining tables in queries with join lines, click Microsoft Access Help on the Help menu, type Join multiple tables and queries in a query in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about automatic joins between tables, click Microsoft Access Help on the Help menu, type Enable or disable automatic joins between tables and queries in queries in the Office Assistant or the Answer Wizard, and then click Search to view the topic.