By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,285 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,285 IT Pros & Developers. It's quick & easy.

too many indexes in Access 2003 database

P: n/a
I have created a database with approximately 60 tables. One table
"tblClients" needs to be linked with about 40 tables. Some are simply
look up tables. Others hold multiple records for each client, for
example children information. (One client can have many children.)

Other tables are linked to tblClients and have a one-to-one
relationship. This is because many of the clients will not have any
data in the fields, for example, marital data.

I have reached a limit and now receive a message "There are too many
indexes on table 'tblClients'"

So how do I created relationships between the rest of my tables and
tblClients? Or am I forced to combine tables?

Thank you for any help or advise you can give me.

Deborah

P.S. Lesson from this database. Create relationships as you create the
tables and before the forms are created. I've learned the lesson so
please do not beat up on me too badly.
Feb 28 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Wed, 27 Feb 2008 17:40:06 -0800 (PST), "dg******@twcny.rr.com"
<dg******@twcny.rr.comwrote:

IIRC the maximum is 32 indexes. And each relation counts as one
(because a hidden index is created).
So you either triage the relationships and create only the most
important ones, or you consolidate some of them into for example a
LookupSeveralThings table. This table could contain several lookups,
especially if you keep the ID values in separate ranges.

-Tom.

>I have created a database with approximately 60 tables. One table
"tblClients" needs to be linked with about 40 tables. Some are simply
look up tables. Others hold multiple records for each client, for
example children information. (One client can have many children.)

Other tables are linked to tblClients and have a one-to-one
relationship. This is because many of the clients will not have any
data in the fields, for example, marital data.

I have reached a limit and now receive a message "There are too many
indexes on table 'tblClients'"

So how do I created relationships between the rest of my tables and
tblClients? Or am I forced to combine tables?

Thank you for any help or advise you can give me.

Deborah

P.S. Lesson from this database. Create relationships as you create the
tables and before the forms are created. I've learned the lesson so
please do not beat up on me too badly.
Feb 28 '08 #2

P: n/a
Thank you both for your input. I always find your posts very helpful.
I will admit that I was hoping for a magic bullet.

Here is my plan and I will appreciate your opinions. I'll combine the
lookups into one table. It will have the usually primary key and a
field for the data the user can understand. Then I'll add a third
field for the Lookup category. So for Income Types, IT will be
entered; for Housing Types, HT will be entered, etc. Then I can
restrict the combo box to the Lookup category.

I'll also change the names of most of my primary keys and end them
with PK instead of ID. If I then compact the database, won't some of
the hidden indexes then be removed?

Then the remaining relationships (which I hope will be just a few) I
can maintain with code.

How do you think my plan sounds?

Thanks again!

Deborah
Feb 28 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.