I'm finding it diffcult to run a query on 20 tables

[jappianing]

Hello Brethren,
I seiously l need your help urgently. I'm trying to run a query on seemingly unrelated tables. I used the query wizard but the wizard returned empty fields. I tried to manipulate the SQL code using RIGHT JOIN, LEFT JOIN OR OUTER JOIN, but to no avail.
Your suggestions will be very much appreciated.
joe

 

[pr2-eugin]

JOINS among 20 tables?? Never taken that leap before..

I am not sure but I think Access does not allow joins more than 15.. I will lookup on that.. In the meantime.. Your question with the word "UNRELATED TABLES" worries me a lot.. Relationships form a base for DB's.. If they are not related, it is not possible to perform a JOIN..

So think again what is that you want to do, and post back..

 

[Libre]

20 tables is a lot.
Are you sure you need them all in the Join?
In my designs, I rarely try to join more than just a few tables at once.
Very complex queries are best designed in "stages".
Instead of 1 massive query, you create a few simpler queries.
Example - I want a recordset of all high school male seniors who made the football team and also took the course, Home Economics.
This could be 3 or 4 queries, starting with the most basic information (high school males) and then using that query in the next (seniors who are also on the football team) and then use that one in the next (took the course Home Economics). Wouldn't surprise me if THAT were an empty recordset!

A few other thoughts:
-Sometimes you get zero results out of a recordset because there really ARE zero records that meet the criteria. (i.e. footballers that took home ec). This is not an error, it's functioning properly. I would worry just as much about getting too many records out of a join.
-Also, check the join TYPE. The default is all records from both queries in the join must match - but that can be changed to SHOW ALL records from either, and just the matching records from the other. All depends on what you are trying to do.

Hope this helps.

 

[jappianing]

Thank you guys for your response. I will reassess what they want me to do and get back to you.
Cheers

 

[pr2-eugin]

Just found this Article.. Microsoft Access Specifications, it turns out that the maximum JOINS on a Query level is 16.

 

[jappianing]

Hello Paul,
Thanks for the info. I will try to reduce the number of tables in the query to say 10 and see what happens.
Many thanks.

joe