Limit of Relationships/Indexes

[tcb]

tbl_Person has a dozen children such as:

tch_Address
tch_PhoneEmail
tch_Employment
tch_Service
etc...

There are 50 lookup tables such as:

tlk_AddType
tlk_PhoneType
tlk_State
tlk_Country
etc...

Access has a limit of 32 indexes so I cannot establish relationships
for all of these lookup tables.

Any suggestions?

 

[Douglas J. Steele]

Most of us strongly discourage the use of lookup fields (see
http://www.mvps.org/access/lookupfields.htm for some of the reasons why).

If you don't use lookup fields, you won't need the additional relationships.
"Wait", I hear you saying, "how am I supposed to ensure that the data is
accurate?" You do this by using combo boxes on forms: tables should never be
used for data entry.

I'd also question your database design. Having 50 field in a single table is
extremely unusual in a properly normalized database.

 

[tcb]

I'm using combo boxes and list boxes in forms with lookup tables as the
source of the data. I'm not using lookup fields within tables. Many
of the lookup tables are not associated with the main tbl_Person but
with the child tables, some of which have a child or two of their own.


The users do all data entry by form which should preclude any problems.
One user has the power to change values within lookup tables and he
understand what to and not to do. I would still like to have the
safety of not allowing him to change or delete a value from a lookup
table that has a corresponding value in tbl_Person or any of the child
tables.

 

[Douglas J. Steele]

Without knowing more about your table design, I can't offer any suggestions
as to whether you're going to hit the index limit or not. (You do realize, I
assume, that the 32 index limit is per table, not per database, and that any
indexes related to the lookup tables would be in the "master" table, not the
"lookup" table.)

If you do, since you're going all your updates through forms, you'll have to
put in logic in the form to check whether a particular value is used, and
prevent the user from deleting that value if it is.

 

[Dirk Goldgar]

tbl_Person has a dozen children such as:

tch_Address
tch_PhoneEmail
tch_Employment
tch_Service
etc...

There are 50 lookup tables such as:

tlk_AddType
tlk_PhoneType
tlk_State
tlk_Country
etc...

Access has a limit of 32 indexes so I cannot establish relationships
for all of these lookup tables.

Any suggestions?

I don't see the problem. If there are 12 tables related to tbl_Person,
that makes 13 indexes for tbl_Person (including the primary key). The
lookuptables seem each to be related to one or another of the 12 "tch"
tables, so they wouldn't be pushing the index limit on tbl_Person.