ACC: Two Tables Automatically Joined in Query

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

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 you want in the Relationships window before you create new queries. If you do not create the relationships 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.

MORE INFORMATION

Steps to Reproduce Behavior

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

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

3. Save the table as Customers. Do not create a primary key.

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

REFERENCES