ACC: Two Tables Automatically Joined in Query (113885)

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."

MORE INFORMATION

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.

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