Too many relationsihps

[mdhoneybee]

I am relatively new to Access, and have made an extensive database for research statistics. I am basing all 36 tables off of one main table containing each subjects' demographics. My problem in creating relationships is that tables 1-31 have linked appropriately to the demographics table, BUT when I tried to link table #32, an error message said that I had too many relationships to one table and I needed to delete one before making the relationship. Is there any way around this? Thanks.

 

[johnherman]

More info is needed to address your question. HOw about a couple sample table layouts?

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[mdhoneybee]

Unfortunately, I do not know how to show you an example of my table, but maybe this will clarify. For example, my demographics table contains an ID No (my primary key), and demographic info for all my subjects (thousands!!). Inside the relationships section of the database (where you drag & drop to set relationships), I have the demographics table at the center. Each of my other tables (linked by the primary key, of course), radiates outward like a star from the demographics table and is set accordingly with a one-to-one, or one-to-many relationship depedending on the data. It has accepted the radiating relationship links for 31 of my tables, but will not let me add more relationships based on that center table. Is there a maximum number of tables that can be related on one table?

 

[lupins46]

The maximum sum of (relationships + indexes) for a table is 32.

 

[lupins46]

Of course there is no compulsion about creating any relationships in your database. I'm not advocating this as a 'normal' approach, but if you have a properly coded application with no user access to tables then you can create an application with no permanent relationships at all. I'm not in any way denying the considerable and reassuring benefits of enforcing RI but beyond that there is little to be concerned about. You can create ad-hoc relationships in queries as required and if you are doing queries in code then that's all you will ever do anyway.

The other issue you need to consider is whether you really need all these separate tables or whether you just need one table with some sort off category/class identifier.

 

[mdhoneybee]

So, I don't absolutely have to have the enforced RI. Great! I'll keep the most important 32 tables linked, and the rest will just be floating. According to a database instructor at our research instiution, it is better design for our study to have each test in a separate table. Thanks so much for your help!

 

[BobRodes]

I'm not sure your database instructor would feel the same way if he were aware of the limitation you describe. Perhaps you will revisit him/her and check. Not using Access to enforce RI is fine (I generally don't do it), but not HAVING RI is NOT fine. I rigorously enforce RI through code.

Bob

 

[lupins46]

I agree with Bob - that is what I meant by' a properly coded application'.

 

[mdhoneybee]

I met w/ my db instructor yesterday, and she suggested making 2 separate databases and linking all the other tables in the 2nd db to the main table in the 1st db. The only problem is that it still won't let me enforce RI in the 2nd table. Is there any other solution? I need to keep RI enforced through Access as many undergraduate students and other ever-changing flows of pre-docs & post-docs will be entering the data. I want to keep as many limitations on the db as possible to minimize data entry errors such as duplication. I wish I could just force Access to accept more than 32 relationships!

 

[lupins46]

There is no way round this limit. And please note it is not a limit of 32 relationships - it is a limit of 32 (indexes + relationships) which is usually less than 32 relationships.

Putting tables into a different database will not affect anything to do with this issue; although it is a good idea to have tables in a separate database anyway for other reasons.

 

[mdhoneybee]

Thanks lupins46! I appreciate your help. The only problem I'm running into is that I need all 50 or so tables to relate to one main table of subject information. Since Access's limit is 32 (including the indexes!), I have nearly 20 tables that still need to be related. Now, in my second database, I have linked the other 20 tables to the main table in the first database. BUT, this will not allow me to enforce RI in the second database. Should I duplicate the main table in both databases, rather than linking the 2? I know that is poor design to duplicate, but my instructor and myself are unsure of the next step. She's never run across this particular limitation to Access. Any ideas??

 

[PHV]

Can't you categorize your 50 or so tests into, say, 10 kind of test and thus play with a hierarchical relationship ?
main table --> test category --> test result

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