Indexes limited

[DanW]

What is the best practice to work around the 32 indexes limitation in Access.



I would like to have a main table for each customer with 55 foreign keys, but can only have 32 relationships.

One area requires 40 Foreign keys, all equally important, abc1, abc2, abc3, ... abc40, so I don't know how to deal with that.

What I really need is to know how important is it to make, sooner rather than later in my project, a relationship for each foreign key, versus using code to look up the value based on the foreign key. Is there a bigger problem than not having referential integrity of the tables enforced?

[dcrake]

First of all I think you have a data normalisation problem. I have never come across a situation whre I have reached the limitations what type of system is it?

David

[DanW]

This is for ranking applicants, such that each evaluator will predict the relative ranking of their top 40 choices out of many applictions. Each evaluator could change their rankings until a final decision on the applications.

Thus, every application has equal weighting, and the order is important.

So I thought a main table for all the evaluators with a field for each of the 40 choices, abc1, abc2, abc3, ... abc40. that would contain a foreign key to the applicants table.

This works fine with no relationships in the small number of records that I have in my test tables.

The number of evaluators will be between 50 and 100, but assume it is 1-2 thousand to see if it stresses Access. The number of applicants will be 500 - 1,000, but assume 5000 just to get your opinion if this will cause problems in the program later.

When I make a many to one relationship from each of the 40 fields to the foreign key of the applicants table, I run out of indexes after 32. I didn't know this about Access.

What is best practice for this? I don't see a linking table working as I am still limited to 32 indexes. Is there a way to have a relationship without the sorting index? Is the relationship important for this small of a database?

[dcrake]

So let me get this right, you have one table with 40 fields and in these fields is the id number of an applicant. Still confused. Lets reduce this to five and can you provide a sample of what you mean. It seams to me that this is a case of denormalised data. Stick it in an Excel spreadsheet if it is easier to describe there.

David

[DanW]

Thanks for your interest and no Excel

Section of Evaluators table...
Applicant1
Applicant2
Applicant3
Applicant4
Applicant5...
Applicant40

These fields are set to Long Interger number data type

I was trying to establish a many to one relationship, with referential integrity enforced box checked, to the autonumber ID of the applicants table, and got a message box saying Access allows only 32 indexes in a database.

I am at the beginning of the project and have been working on other parts. I can do lookup select queries to grab the right applicant's record without any relationship defined between the tables.

So I want to know how necessary it may be to define the relationship. I'm wondering if that would be necessary for report queries that I haven't started yet. I haven't made a database in 10 years.

What I have done is the simple way to me. I thought about a linking table and the evaluator would have to select the ranking of the applicant. But it seemed a lot more complicated to code (weak area for me) the process of lowering the ranking on all the lower ranked applicants and kicking out Mr. 40 because he is now Mr. 41. I assume now that is the best practice, as it would only use one index for each table.

But is the defined relationship important? Is it impossible, lazy or just ugly to go forward without relationships defined between the two tables?

[DanW]

I have just realized that I can make as many relationships as I would like, as long as I only check the Enforce Referential Integrity box 32 times or less.

Sorry for my ignorance. I should have tried that first before starting the thread.

I guess this is solved such that I can use the lazy, ugly way instead of the elegant way to rank my applicants, unless there is something I am missing.

Still welcome any comments.

Dan

[dcrake]

So you have an evaluators table and in this table you have 40 fields named
Applicant1
Applicant2
...
Applicant40

If so this is totally denormalised. You should have

ApplicantID
Applicatn fields

Remembers columns are expensive rows are free.

David