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.

On Wed, 27 Feb 2008 17:40:06 -0800 (PST), "dgardner (AT) twcny (DOT) rr.com"
<dgardner (AT) twcny (DOT) rr.com> wrote:

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.

Deborah, I just want to assure you that the particular problem you descibe
here is very common. It may even be an indicater that you have a good design
here.

Instead of having lots of different tables with similar structure (such as
Customers, Shippers, Suppliers, Employees), combining them into one client
table has some important advantages - especially for foreign keys. You can
then make payments to or receive payments from any of these "clients."

But it does mean you have lots of foreign keys going back to the client
table, and Access does run out of indexes trying to maintain these with
Referential Integrity (RI). So, you choose the relations where you want RI
enforced, and the less vital relations you maintain with code.

You may be able to remove some unnecessary indexes. By default, Access
indexes any field that ends in a name such as ID, Code, Num, ... So it
automatically creates an index on the ClientID field. If you then mark it as
your primary key, that's 2 indexes on the one field. Similarly, it creates a
hidden index on the foreign key involved in a relation with RI, so you
really don't want a visible index on that either.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<dgardner (AT) twcny (DOT) rr.com> wrote

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

Responses in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<dgardner (AT) twcny (DOT) rr.com> wrote

Yes, that's a good strategy if you have lots of lookups (as distinct from
lots of related tables.)

The up-side is that there are fewer relationships (so fewer indexes.)

The down-side is that since RI is applied against the entire lookup table,
only, it is possible to end up with an inappropriate type in your data
(typically because someone changed the type after it was created.) Sometimes
you can avoid that by providing an interface where the type is entered as a
default value, using separate forms for each type, so the user can't even
see the hidden type and therefore can't change it.

Still, it makes good sense.

No need to do that. Just remove all the entries for:
AutoIndex on Import/Create
In Access 2007, that's under:
Office Button | Access Options | Object Designers.
In older verisons, it's under Tools | Options.

Sounds good.

On Fri, 29 Feb 2008 10:13:21 +0900, "Allen Browne"
<AllenBrowne (AT) SeeSig (DOT) Invalid> wrote:

Responses in-line as well.


Integer and set aside ranges of values for each lookup.
Your solution with a third column indicating LookupType works as well.


Indexes window (View > Indexes) to review what Access created for you.
Keep in mind that AutoIndex is for non-developers who need to be
protected against doing eh... stupid things, but developers indeed
want to turn this off and be in control of which indexes are being
created. If you see two indexes specifying the same set of initial
fields in the same order, delete the one with the fewest fields.


someone accesses the tables directly and your code can't do the right
thing? I would rather find a better RDBMS than give up declarative
referential integrity.