Is there a limit to the number of related tables ?

[arthurbr]

In Access 2000 I have a Master Table ( A ) related to 21 ( B1 to B21) tables.
When I make a select query with A and a small number of the related tables ( 12 or 13) all goes well. But once I make the query with all of the related tables, the program does not respond any more.
I have used indexes where necessary.
I need to get all the results on one query because I use it as control source for a form.
Is ther a limitation to the number of related tables, and how can I workaround.
Thx in advance for your support and help

 

[BNPMike]

You're allowed 32 tables in a query.

It may well be that your query is accidentally creating vast numbers of records. Maybe you could post the SQL here and tell us the number of records in each table.

There is some interaction between enforced relationships, indexes and limits, so I would remove all declared relationships. Build the query again drawing in the join lines in QBE, and see if it now runs. Relationships have their advantages but are not necessary to join your tables together.

 

[Crowley16]

you only really need to build enforced relationships if you want to have cascade update/delete between the tables, if you don't need that, then there's no real reason to build the relationship...

 

[MichaelRed]

There are also limits on the number of fields and the length (in characters) of SQL statements. Twenty-Two (22) table references seems like it could easily breach either of these, although wither shoud generate and error, not just lock up the system.

Further, the 22 tables seem like a lot, could it be normalization gone beyond the Nth normal form? What needs this number of seperate entities? (just curious).





MichaelRed
mlred@verizon.net

 

[arthurbr]

Thx to all fo you for the help.
I did delete the declared relationships but the problem remains ( although it looked promising at first).

The number of separate entities is due to the fact that each record decribes every detail about a certain PC ( bios, disk, diskspeed, OS, OSversion...).
So, applying the normalization rules I created separate tables for sets of values that apply to multiple records ( there is a table for the different OS, disktypes, etc..)

While I am writing this, my PC finally got over the problem but it took the query 4 minutes to switch from Design to Datasheet view....

Fyi, the main table has about 1000 records and 26 fields ( some fileds did not need creation of separate tables).

Any other suggestions to speed things up would be much appreciated

 

[PHV]

there's no real reason to build the relationship
You never trust the wizard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[arthurbr]

Thx PHV.
Unfortunately the wizard has the same problem