Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 208420 - Last Review: June 29, 2004 - Revision: 2.0
ACC2000: Two Tables Automatically Joined in Query
This article was previously published under Q208420
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.
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.
To delete the join line, click it, and then press DELETE.
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.
APPLIES TO
- Microsoft Access 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate