Exceeds maximum index number

[Todd]

I have a primary table with an indexed key field. Many of
my other tables are related to this table via the indexed
field. When I try to create another table relationship to
this index, I now get the error message "Too many
indexes...".
I thought I could just create another table with that
same index ID number, make a one-to-one relationship to
the primary table, then use it for the other table
relationships.
But, if I do that, then try to change data on the
original it does not properly cascasde.
I am not using any lookup fields.
I think I am at the maximum of 32 indexes for one table.

Is it possible to create a 'ghost' table (one-to-one with
the primary table) that has the same key index, but will
now allow more relationships to it?
Did I explain that OK?

Table1 has fields:
IDnumber (used as index for 32 table relationships)
field1
field2
field3
etc

GhostTable would have fields:
IDnumber --(same as above, but can be used for additional
relationships to exceed the maximum of 32)

Would I use an autonumber unique identififing number, too?

 

[John Vinson]

I have a primary table with an indexed key field. Many of
my other tables are related to this table via the indexed
field. When I try to create another table relationship to
this index, I now get the error message "Too many
indexes...".

That's odd. If all of the related tables are joined to a single field
in this table, there would need be only one Index - you don't need a
separate index for each join!

Open the table's Indexes collection (in table design view it's the
lightning bolt hitting a datasheet). What indexes are defined on the
table? Are there multiple redundant indexes on the same field?

It may be that I've been misinterpreting the message you've posted
repeatedly over the last couple of weeks... but it seems like you
should not be having *this particular* problem!

 

[Todd]

You may be mistaking me for someone else. I am new to
this newsgroup, although I did post a similar question
earlier today but never got an adequate response.

I think I might have mis-represented my problem.
There is only one indexed field. But I have over 40 other
tables with that same index that I want to have a one-to-
many relationship to that first table. But I think 32
such relationships is the maximum and I need more. Is
there a way to create another one-to-one table and use it
to add the extra relationships?

Thank you for your help.

 

[Jeff Boyce]

Todd

Pardon my intrusion, I'll ask and leave...

I believe there's a limit to indices on each table.

I'm having trouble envisioning one main table related to 40 others. Could
you describe your data a bit and how you've divided it into tables? It
would help in offering ideas.

 

[John Vinson]

You may be mistaking me for someone else. I am new to
this newsgroup, although I did post a similar question
earlier today but never got an adequate response.

I think I might have mis-represented my problem.
There is only one indexed field. But I have over 40 other
tables with that same index that I want to have a one-to-
many relationship to that first table. But I think 32
such relationships is the maximum and I need more. Is
there a way to create another one-to-one table and use it
to add the extra relationships?

I apologize. I gave snippy answers to a couple of people yesterday; I
must have been in a truly foul humor!

I'm not certain whether the limitation is on indexes (in which case
you'ld be OK, you only need one index) or on relationships. Do you get
an error when you try to add the 33rd table?

You can create a one to one relationship and have 31 tables related to
one, and 31 more to the second - but you'll need some code to create a
record in the second table whenever you create a record in the first
one; and cascade deletes may not work perfectly - it will depend upon
the order in which you delete records.

This kind of "sea urchin" table relationship seems rather unusual -
what IS this table that it relates to forty other tables? Or what are
they? If they're all tables containing the same type of data, could
they perhaps be combined?

 

[Guest]

I am designing a Cardiology database. The first table (Demographics) concerns unchanging data for an individual patient which has date-of-birth, gender, race, etc. The second table (HospitalVisit) regards a particular visit to the hospital. It contains data that will be required for almost every patient for evey new visit. The '40' other tables contain data for various test results and other data for a particular hospital visit (identified by a unique HospitalVisitIDnumber). Of course, not every test is performed on every patient and certainly not on every hospital visit. There are over 40 such categories of test results relating to a particular hospital visit. Such data might be contained in a table called 'CoronaryAngiography' or 'LeftVentriculography' or 'NuclearStressTest. There is no overlap of such data, but each does have a common HospitalVisitIDnumber relating to the particular hospital visit
I have tried to create a 'ghost' table that mirrors the HospitalVisitIDnumber of HospitalVisit Table. It is a onene relationship. But I am having problems with cascading events...whether modifying an ID number that was mistakenly entered or deleting one that was an incorrect entry all together
Hope my explanation helps you understand the issue
Any ideas
Thanks in advance for any advice

 

[Guest]

am designing a Cardiology database. The first table (Demographics) concerns unchanging data for an individual patient which has date-of-birth, gender, race, etc. The second table (HospitalVisit) regards a particular visit to the hospital. It contains data that will be required for almost every patient for evey new visit. The '40' other tables contain data for various test results and other data for a particular hospital visit (identified by a unique HospitalVisitIDnumber). Of course, not every test is performed on every patient and certainly not on every hospital visit. There are over 40 such categories of test results relating to a particular hospital visit. Such data might be contained in a table called 'CoronaryAngiography' or 'LeftVentriculography' or 'NuclearStressTest. There is no overlap of such data, but each does have a common HospitalVisitIDnumber relating to the particular hospital visit
I have tried to create a 'ghost' table that mirrors the HospitalVisitIDnumber of HospitalVisit Table. It is a onene relationship. But I am having problems with cascading events...whether modifying an ID number that was mistakenly entered or deleting one that was an incorrect entry all together
Hope my explanation helps you understand the issue
Any ideas
Thanks in advance for any advice

 

[John Vinson]

am designing a Cardiology database. The first table (Demographics) concerns unchanging data for an individual patient which has date-of-birth, gender, race, etc. The second table (HospitalVisit) regards a particular visit to the hospital. It contains data that will be required for almost every patient for evey new visit. The '40' other tables contain data for various test results and other data for a particular hospital visit (identified by a unique HospitalVisitIDnumber). Of course, not every test is performed on every patient and certainly not on every hospital visit. There are over 40 such categories of test results relating to a particular hospital visit. Such data might be contained in a table called 'CoronaryAngiography' or 'LeftVentriculography' or 'NuclearStressTest. There is no overlap of such data, but each does have a common HospitalVisitIDnumber relating to the particular hospital visit.

Todd, I did some experimenting.

Creating 32 or more links from a single indexed field to 32 or more
child tables does NOT cause any errors.

How are you defining the relationships? What SPECIFIC error message do
you get, and under what circumstances?

 

[Guest]

After I have made about 32 1:Many relationships and try to make the thirty-third, I get the error
Here is the exact message text
"The operation failed. There are too many indexes on table 'TableVisit'. Delete some of the indexes on the table and try the operation again.

I did create that 'ghost' table but, as you predicted, when I try to modify data, the cascade does not always work. It is odd that sometimes it DOES work. Is it, as you previously stated, dependent on the order that the cascade goes through the different tables

Thanks again for your insights and the generosity of your time/attention
Todd

 

[John W. Vinson]

After I have made about 32 1:Many relationships and try to make the thirty-third, I get the error.
Here is the exact message text:
"The operation failed. There are too many indexes on table 'TableVisit'. Delete some of the indexes on the table and try the operation again."

Again... how are you creating the relationships?

Creating a relationship from a 'one' table to a many table DOES NOT
CREATE ANY NEW INDEXES, at least not in my experience.

Try opening TableVisit in design view and view its Indexes (use the
icon with a lightning bolt hitting a datasheet). What indexes exist?
What fields are indexed? Are any of these indexes redundant (i.e.
additional unique indexes on the same field)?

 

[Guest]

I will do as you ask, but first let me describe how I create the relationship which then provokes the error message..
I do it in the 'Relationship' window and just drag the "VisitIDnumber" from "TableVisit" to "VisitIDnumber" in one of the tables of test results. Make sense
I will get back to you after I follow your instructions. But I too created a sample database with primative tables that I then linked in the "Relationship" window. When I got to 32 and tried to add the thirty-third, I got the same message

Thanks again, in advance
Todd

 

[Guest]

'TableVisit' has about 140 fields. Only two are indexed, 'VisitIDnumber' and 'AccountNumber'. Thats it
I am confused as to why the error message says anything about indexes, other than it referring to the relationship I am creating between table indexes
Gotta go vote, but I will be back soon.....

 

[Jeff Boyce]

Todd

I'll intrude once again, but plan to leave you in John's capable hands...

As I'm quite sure John will tell you, 140 fields is WAY TOO MANY for a
well-designed relational table. Consider reading up on "normalization".

 

[Guest]

If I divide up the primary table, 'TableVisit' into smaller tables, then each of them will require a relationship to the original 'TableVisit' which is the problem in the first place! Access will not let me create any more relationships!

 

[RSGinCA]

What version of Access are you using?

Rick

 

[Guest]

Access 2003

 

[RSGinCA]

Ok...

Here's a note about Access 97:
"In Microsoft Access 95 and Access 97, a single table cannot have more than 32
indexes and direct relationships combined."

See:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaracces
sdev/html/odc_access97conversionissues.asp

This specifically refers to Access 95 and 97 BUT I think it was written in 1997
and was specifically talking about conversion from an EARLIER version of
Access. So the fact that it was specifically referring to 95 and 97 doesn't
mean that it doesn't apply to Access 2003.

I do know that the specs for Access 2003 do say that 32 indexes are allowed in
a table and that it makes no mention of relationships, but who knows... If I
had the time I'd try it out to see if a lot of relationships affect it. From
your experience I'd be almost willing to bet it does.

Another thing: I was looking around, and I think I saw someplace that if you
specify "enforce referential integrity" for a relationship that a index will be
build for BOTH sides of a relationship. Do you have that specified for your
relationships? (Go to the relationship window... right click on the
relationship line... click on 'edit relationship'. There's a checkbox for
"Enforse Referential Integrity"... Is that checked?)

Rick

 

[Guest]

Indeed, you are correct
All the relationships have all three boxes checked:
enforce referential integrit
cascade update related field
cascade delete related record

I thought it was important to have all three active for maintaining data integrity
I checked to see if a new index was created on the many side table....nope
It must just be that I reached the limit of 32.

 

[RSGinCA]

I quickly tried to search for any problems like I made reference to about
"enforce referential integrity" and couldn't find anything. Maybe I was wrong.
It doesn't make sense. i do think I that I saw that those check boxes are
checked by default.


But on a different subject... I was looking back on what you had said before
and something seemed strange. You say that those are 1:Many relationships
between your Visit table and your Test result tables. I would think that those
would be 1:1 relationships. On a single visit would there ever be more than
one of any of those specific types of tests (frinstance would there ever be 2
Nuclear Stress Tests on one visit). Is the key to the Test tables anything more
than simply HospitalVisitID? If that is the case then I'd be VERY suspicious
of that being the cause of your problems.

Rick

 

[Guest]

I have set it up so that a single nuclear test will have a variety of test result types. For example, on the single test I describe their exerice ability, their blood pressure, their heart rate. So that table has the patient's Visit ID number, the NuclearTestIDnumber, and TestResultType and TestResultValue. Hence one visit: many tests results. The NuclearTestResult Table indexes include both VisitID# AND NuclearTestID#