Adding table in dialog box changes query

  • Thread starter Thread starter Art Schwalge
  • Start date Start date
A

Art Schwalge

I have a simple query using two unlinked tables that have
a many to many relationship between a particular field.
I've used a criteria of equality between the many to many
field, and the query returns what I want: the many rows
(1041) of the relationship.

In Design Query mode, using the Show Table dialog, I add a
third table. This table is NOT linked in the table
relationships. No tables have primary keys. I do not add a
single field from Table 3 to the query - all I do is make
it show up in the top part of the Query Design box. I run
the same query again (as a test), expecting to get the
same result. But NO! Now I get 815103 rows!

Why does merely the appearance of the table in the Query
Design dialog box affect the results of the query?
 
Art,

No relationship is defined between the two linked
tables and the third one, so the third table is causing
the random expression of data due to a null link
relationship.

Casey
 
I have a simple query using two unlinked tables that have
a many to many relationship between a particular field.
Click to expand...

Two tables CANNOT AND DO NOT have a "many to many" relationship; such
a relationship can be instantiated only by adding a third table,
linked one to many with each of these tables, with one record for each
pair of related records.
I've used a criteria of equality between the many to many
field, and the query returns what I want: the many rows
(1041) of the relationship.
Click to expand...

What you're describing is a "Cartesian Join" - no join at all; instead
every record in the first table is paired with every record in the
other table.
In Design Query mode, using the Show Table dialog, I add a
third table. This table is NOT linked in the table
relationships. No tables have primary keys. I do not add a
single field from Table 3 to the query - all I do is make
it show up in the top part of the Query Design box. I run
the same query again (as a test), expecting to get the
same result. But NO! Now I get 815103 rows!
Click to expand...
Why does merely the appearance of the table in the Query
Design dialog box affect the results of the query?
Click to expand...

Because you are creating composite records from all of the tables in
the Query; since you don't have any join lines, Access (or any
relational database) will display every record in Table1 joined with
every record in Table2, and then that pair of record joined with every
record in Table3. Access is working exactly as it is designed to do.

Note that a table with no Primary Key is pretty much useless for most
purposes, since you can't uniquely select any record; similarly a
query with no join is only rarely useful since it's totally
indiscriminate, matching every record in each table with every record
in the other. Once in a blue moon you may want to do this but
ordinarily you're using a RELATIONAL database to find records which
are related to one another (by a join field).

Could you describe what these tables represent and what you are trying
to accomplish?
 
Back
Top