Number of left joins allowed in query

  • Thread starter Thread starter Rob Parker
  • Start date Start date
R

Rob Parker

Is there a limit to the number of left joins a query can have?

I'm using Access 2002 , and trying to created the equivalent of a SQL Server
View from a main table which has 26 related lookup tables. I can design the
query in the QBE grid OK, but when I try to run it Access hangs.

If there is a limit which I've exceeded, is there a work-around technique I
could use to establish the "view" I need? Note: this is for design/debug
purposes only - I'm not concerned with speed/performance issues, as the real
app is actually built in SQL Server - I'm just using Access (since I don't
have SQL Server at home) to do some exploratory work.

TIA,

Rob
 
Replying to my own post:

Patience is a virtue ...

My query with 26 left joins did eventually run - it simply took about a
minute to do so (with only about 3000) records in my main table. I'd simply
closed the "Not responding" Access application too soon in my earlier
attempts to run it.

And, for the record, to fix this, I'm about to create my "View" as a real
table, by running a series of update queries on a copy of the original
table. Yes, it won;t be dynamic as an SQL Server view is, but it will work
for what I want now.

Rob
 
Dear Rob:

You could also install the MSDE that comes with your Access and just write
and use a SQL Server query. Set up a database that references your Access
Jet tables. You may need to buy the SQL Server Developer Edition ($50) for
the tools it would provide you. You can then use the optimizer and probably
get this done in just a few seconds.

Tom Ellison
 
According to Access Help, you can have up to 32 Tables in a Query. I think
this basically forces the number of joins to be 31 max.

The SQL Server will be faster, especially if you use Pass-Through Query
since in this case, it is the SQL Server that processes the Query.
 
Hi Tom,

Thanks for the suggestion. I'll keep it in mind if I need to do this sort
of thing in the future, but for now my flattened table to simulate the view
is all I need.

Rob
 
Thanks, Van.

.... particularly for the limit on the number of tables in a query. I didn't
find that in the Access help file - which (for me) seems to be an excellent
example of how to produce unusable documentation for anything I need to look
up. At least the A2002 version is better than the A2k one, which for many
subjects shows blank pages!

As I said, the real application uses SQL Server, so this performance issue
doesn't exist. And for my current work with it, my flattened "view" table
is all I need.

Rob

..
 
Back
Top