ACC: Two Tables Automatically Joined in Query
ID: Q113885
|
The information in this article applies to:
-
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 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.
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.
REFERENCES
For more information about joining tables in queries with join lines,
search for "join," and then "join tables" or ask the Microsoft Access 97
Office Assistant.
Keywords : QryJoin
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb