Joins

[Space Cowboy]

Good afternoon good people,
How many joins can you have on a table?

 

[theDBguy]

I just checked and didn't see a limit for that in the spec table. Why do you ask?

 

[plog]

This is one of those 'If you have to ask, something is wrong' type of questions. 'Whats the best way to get blood back into your body?', 'How much exposure to plutonium is too much?'

10-1 odds you have not structured your tables correctly. Can you tell us why you are asking this and perhaps give us examples of the tables you want to mass JOIN?

 

[NauticalGent]

PLOG: AWF's ambassador for diplomacy and tact!

 

[Space Cowboy]

I have a table that has general info
the other tables for sites, reps, parts, manufacturers etc
I am building a query where calculations need to be done and then fed to another query,
but I will still need to link to the original table again eventually.
It seemed to me inefficient. to go round and round through the same table.
I thought I could use the general table with four or five links and get all the info in one query, I think it will take longer and be slower but logically less complicated.
They will not all go on one key, they will be different join keys from the main table.

 

[ebs17]

Number of joins in a query: 16 Access specifications - Microsoft Support Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table. support.microsoft.com

 

[plog]

but I will still need to link to the original table again eventually.
It seemed to me inefficient. to go round and round through the same table.

From what you have described, it doesn't sound necessary to keep coming back to the original table--just include all the fields you want along with those calculated fields. That way that first query you build has the calculations and the fields you will need later.

Perhaps a sample database or more concrete example specific to your situation would help us see your situation better.

 

[Space Cowboy]

thanks Ebs17,
very handy that link.
50* nested queries. ye ha!

 

[Space Cowboy]

From what you have described, it doesn't sound necessary to keep coming back to the original table--just include all the fields you want along with those calculated fields. That way that first query you build has the calculations and the fields you will need later.

Perhaps a sample database or more concrete example specific to your situation would help us see your situation better.

I think I will be able to nest the calculations so they are done first, then include them in the query. is best way SQL or VBA?

PS. I am proficient at neither.

 

[theDBguy]

Number of joins in a query: 16 Access specifications - Microsoft Support Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table. support.microsoft.com

Ah, I was looking at the table area only. Cheers!

 

[The_Doc_Man]

Good afternoon good people,
How many joins can you have on a table?

Answering that question is tricky because the answer is "No (explicit) limit." As others have pointed out, the limit isn't on the table. It is on the query that implements the JOIN. The limit is on how many CONNECTIONS you make between tables via the query. BUT if you have more than one query open at once, each QUERY has that same 16-JOIN limit.

As an aside, it is not unusual for DB programs to have relatively lower limits for this. In 2010 time frame, ORACLE only allowed 16 JOINs - and when we called them for help with a problem query that had 15 JOINS and sometimes hiccuped, their response was "Have you EVER gotten that to work?" (with a note of astonishment in their voices).

I think I will be able to nest the calculations so they are done first, then include them in the query. is best way SQL or VBA?

With the usual disclaimers, I will say that the answer to that question depends on many factors. HOWEVER, in my not always humble opinion, if this is going to involve what I sometimes call a "translation JOIN", you would do better to define this via static SQL-based JOIN queries because if the main purpose of the JOINs is to do a translation or lookup, you would be doing this activity A LOT and having pre-defined SQL queries sitting around would be a good tool for the toolkit.

50* nested queries. ye ha!

As to nested queries, it would be a good idea to limit the amount of nesting. Yes, you can nest a whole bunch of queries, but you can run into memory issues and/or speed issues with deeper nesting. Not to mention that if you wanted to do updates through a JOIN query, life gets complicated sometimes. But yes, sometimes the "divide and conquer" approach is helped by nesting queries. You might also see this topic under the term "layered queries."

 

[ebs17]

Calculations ... is a broad word that can be filled with many different meanings.
More specific descriptions would have to follow.

 

[Isaac]

Good afternoon good people,
How many joins can you have on a table?

One example of when you may want to use this is selecting from a main table, then having a bunch of left joins from that main table to other tables with special predicates. Then aggregating or something. But this may turn out to be exponentially slower than you'd expect - and - contrary to the usual database preaching - you may find it actually quicker to do subselect correlated subqueries in the select statement instead; I found this recently to be the case in t-sql

 

[Pat Hartman]

I thought I could use the general table with four or five links and get all the info in one query, I think it will take longer and be slower but logically less complicated.

The limit is 16 joins in a query. Typically the heart of the query is only 2 or three tables that create a hierarchy. The other joins are to lookup tables.