Too many indexes using a lookup table

[midastouch1]

I work on a medical research team, and we get whole specimens donated to us. When we receive these specimens, we slice them up into (up to) 35 pieces (and are tracked as 35 unique fields in the associated table). Then each piece may or may not contain certain "parts" of the whole. The parts of the whole can easily be defined in a lookup table (which I have done). Each of the 35 "slice piece" fields has a correlating "here are the sub-parts that are contained in this slice" field, and I'm setting each of those fields up as a multi-value lookup field. I'm running into a problem at around "slice piece" #27 where the table will not save and I get an error saying that there are too many indexes on the main table. I have read that referential integrity connections count as indexes, and have unchecked the "enforce referential integrity" for each of the existing lookup relationships (yes, I know the dangers of this), but still get the same error when trying to add another lookup for the "slice piece 28" field. The main table itself has only 1 index (on it's PK) outside of these lookup connections. Am I missing something here? Or can anyone suggest another route to get 35 fields associated with the same lookup table on the same main table? Here's a mock field set to hopefully clarify:
tblSpecimen.SliceNum"1-35" (the 35 individual slice fields, default values of 1-35)
tblSpecimen.SliceComponents"1-35" (the 35 correlating multi-value slice components fields)
lkpComponent.Component (all of the components contained in the specimen as a whole)

Notes:
The database will be split with all tables (including lookups) in the back end.
The specimen is a human organ, so barring some leap forward in human evolution, the "components" listed in the lookup table are not subject to change in any of our lifetimes

 

[Frothingslosh]

Your database is set up poorly from the ground up. You're also using lookup fields. I'd strongly recommend reading about NORMALIZATION. You should also read the Ten Commandments of Access.

Your tables, from what you described, should probably be laid out something like this:

tblSpecimens
SpecimenID_PK (autonumber, primary key) (link to SpecimenID_FK)
DateReceived
SpecimenTypeID_FK (number/long integer, used as foreign key)
Add any other fields that would apply to entire specimens.

tblSpecimenTypes
SpecimenTypeID_PK (autonumber, primary key) (link to SpecimenTypeID_FK)
SpecimenDescription ("Heart", "Lung", etc)
Add any other fields specific to individual specimen types as a whole.

tblSlices
SliceID_PK (autonumber, primary key) (link to SliceID_FK)
SpecimenID_FK (number/long integer) (indicates what specimen this slice came from.)
Any other fields specific to specific slices.

tblSliceComponents
SliceComponentID_PK (autonumber, primary key) (link to SliceComponentID_FK)
SliceComponentDescription (text)
Any other fields specific to the components.

tblSliceComponentListing (This is technically a join table)
Note: SliceID_FK and SliceComponentID_FK should be rolled into a composite key, rather than having a separate primary key field.
SliceID_FK
SliceComponentID_FK
Add any fields that are unique to this specific slice/component listing - most of your data will probably be in this table.

This setup will avoid virtually all the issues you listed.

 

[midastouch1]

Frothing,
Thanks for the suggestion. I think I'm missing something here. I fully understand that I broke the hell out of normalization when I basically stuffed a 4-field table into the middle of an existing record (6 if I made a separate table and added PK and FK fields). Yes, there are actually 4 data fields that are repetitive.

I requested a "friend" add from you on the site, and would be more than happy to call you offline to explain the procedural constraints that led to that decision and pick your brain a little on different potential solutions. I'd also be happy to come back to the forum and outline the constraints and credit you in the detailed solution, should we be able to come up with one. I'd hate to come up with a solution and not share it with the community, but because this is medical and pseudo-sensitive, I'd prefer not to expose the gritty procedural (work procedures, not programming) details to the masses. Let me know!

 

[Frothingslosh]

Huh...it appears you have private messages from members turned off. Send me a PM and I'll reply with one of my email addresses. I have no problem working with you 'offline', as long as any resolution is posted here for future users.

 

[Frothingslosh]

Due to us adding a few tens of thousands of new providers and customers here at the health insurance company where I work and the resulting need for a TON of new people, my telephone is sort of on loan to our Customer Service department, believe it or not.

I've written down your phone number and extension, and will call you after I leave work at 4 eastern. Feel free to nuke your post.

 

[midastouch1]

Ok, so after a couple of phone calls between Frothingslosh and I, he beat me upside the head with the simple solution: normalize your data retard... What I had was parent table "a" with child table "b", but child table "b" needs a child table "c" to define the subparts associated with the records in child table "b". I was attempting to smash child table "c" into its parent table "b" by creating 35 duplicate variables adding the numbers 1-35 to the end of each variable name. Bottom line, normalize your data appropriately and not only does your database function better, but it also prevents you from having to write an "I'm retarded" post on the forums. Thanks a lot for the help Frothing!

 

[namliam]

if the parts and subparts are the Nearly same thing, it is perfectly viable to have a parent/child relationship in one table...

Much like an Employees table will have normal employees, team leads, assistant managers, managers, top level managers, directors, assistant to the manager of keeping the coffee machine filled, etc.....
Perfectly OK to put all that into one table instead of 10 different ones.

 

[Frothingslosh]

if the parts and subparts are the Nearly same thing, it is perfectly viable to have a parent/child relationship in one table...

Much like an Employees table will have normal employees, team leads, assistant managers, managers, top level managers, directors, assistant to the manager of keeping the coffee machine filled, etc.....
Perfectly OK to put all that into one table instead of 10 different ones.

Very true. In this case, however, they were not. The subparts were various things that could appear in the parts. The options were basically to use either a list of checkboxes or a many-to-many relationship, and for this specific database, the many-to-many seemed the route to take due to certain entry and reporting needs.