Unlinked Tables affecting Query?

[Jay]

I have a simple SELECT query which is based on two tables with an INNER
JOIN on the VehicleID field.

I noticed however, that if I added another table to the QBE grid - even
though I didn't link the table to my query's 2 source tables in any way,
and even though the query's SQL remained unchanged - the query output
changed??

It returned the same records, but repeated hundreds of times?

Can anyone explain why just the presence of an unlinked table in the QBE
grid would affect a query? I just can't explain it.

Any help greatly appreciated.......Regards, Jason

 

[Jeff Boyce]

Jay

If you add two tables to a query but don't "join" them, you are telling
Access to provide a "Cartesian Product" (i.e., every possible combination of
values from both tables). Even if you join T1 and T2, when you add
(unjoined) T3, you'll get a Cartesian Product.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michel Walsh]

By unlinked, you mean no INNER/OUTER join? What you see is the expected
result.

If you have a table, Ds, one field, d, with 6 records, values from 1 to 6,
then, bring the table twice in a new query and you will get 36 records ( 6
for the first 'table', times 6 for the second table), with couples from
(1, 1) up to (6, 6), or, if you prefer, ALL the possibilities of throwing a
6-sides dice, twice.

When you have no inner/outer join, you have a cross join, which implies that
each record from one table is 'horizontally merged' with each and every
record of the second table (one merge at a time, per row of the result).


So, adding a table with n rows to an existing query, with a cross join, will
generate a query with n times more records than what you have, without that
new table.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Yep. It's called a Cartesian product. For every record in the new table, a
record will be returned for each record in the other table.

For example you have TableA with 10 records and TableB also with 10 records.
If you don't link these two tables, they will produce 100 records! If one had
10 and the other 100, there 1,000 record returned.

Once saw someone join a table with 1 million records to another with 8
million records and forget to join the two tables. I'll let you do the math
but it was huge.

Usually Cartesian products are a mistake; however, there are some times when
they are handy. For example when you need to find every possible combination
in two tables.

 

[Jay]

Thanks for explaining it guys... Today was the first time in >10years
Access use that I actually ran a query with a 'stray' table in QBE grid.

I did it by mistake...so was a little surprised by the output.

I understand the principle of Cartesian products & can see now why it
does it. It just threw me that 'stray' tables affect the result, even
when not referenced in the actual sql.

Is this essentially a bug/un-intended application behaviour?

Regards......Jason

 

[Guest]

Not unintended. There are some uses for that kind of join such as finding all
the possible permutations of the records.

It actually should not happen unless you want it to if, big IF, all your
tables are properly joined in the Relationship window.