Un-expected Query results (1 Viewer)

[got_access:]]

Hi All
I have these three ultra simple tables.

When I add Table_A to the query editor with this query
SELECT TABLE_A.[TABLE_A DATA VALUE] FROM TABLE_A;
It returns 6 records - which I would expect.

However, when I add TABLE_B into the editor
SELECT TABLE_A.[TABLE_A DATA VALUE] FROM TABLE_A, TABLE_B;
It returns 18 records

And when I then add TABLE_C into the editor
SELECT TABLE_A.[TABLE_A DATA VALUE] FROM TABLE_A, TABLE_B, TABLE_C;
It returns 108 records.
I know its adding all of the rows from the other tables - but since I didn't add those rows within the SQL query itself - I 'm surprised it added them in the results.

This must be an inherent characteristic of the Access query editor?
This makes me think there is some kind of join going on - by default - is there?

 

[pbaldy]

It's because there's no join. With no join you get a Cartesian product. What are you expecting, and is there a relationship between the tables?

 

[theDBguy]

Hi. Take a look at this article to explain what you're seeing.

 

[got_access:]]

Awesome!
Thank you - it made for a cross join by default - or Cartesian Product.

Thanks!
Funny - I've been using Access consistently for about a year now and I'm just getting around to noticing that!

 

[The_Doc_Man]

Access is good at hand-holding, but sometimes you run into this little hold-over from set theory that rears its ugly head.