Very Basic Issue

[adyf]

I have an <index missing> message on a field that I'm trying to relate to in another table. I'm only trying to match the Assessors title with Assessors name on a form,this information is stored in a table called Assessors. The relationship for 'Assessor' goes from the Assessor table to the Assessments Comments table via the Rules Assessment Exam table. This should still work shouldn't it?

[adyf]

Is a relationship between two tables via another table just as valid as a direct relationship between two tables? Just want to rule this out of my above problem.

[bcooney]

I would suggest adding auto-enter numerical keys for all your records. You seem to be relating tables by user-entered text values (and that's a very poor design).

Also, how about reading up on approaches to the relationship graph JonathanStark Blog? There are several methodologies available.

Index missing is typically a sign of an unstored calc on the bottom half of a relationship. Hard to tell what you're doing, really.

[adyf]

I would suggest adding auto-enter numerical keys for all your records.

For all records in all tables?

[bcooney]

Yes. Standard operating procedure. Then, redo your relationship to use those numeric keys.

[adyf]

Yes. Standard operating procedure. Then, redo your relationship to use those numeric keys.

May I ask just one more basic question please? With my query above, how will a relationship built on numeric keys match an 'Assessors Name' from one table to an 'Assessors Name' in another and pull in 'Assessors Title'? Quite wrongly as you have pointed out I have always related matching text fields, 'Assessors Name' in this case. I'm finding database structure the hardest thing to get my head round but intend to persevere.

[bcooney]

Please look at the link I provided.

You wouldn't match on Name. You would have the foreign key for the Assessors in the child table. Please read up on building relationships. Kachel