AnsweredAssumed Answered

Index Missing

Question asked by DustFairy on Jan 2, 2015
Latest reply on Jan 6, 2015 by DustFairy


Index Missing



I am trying to create a data base to allow me to do inspections of machines and complete a check sheet while doing the inspection.  Wen the inspection is complete the database should send an email to the owner with a list of comments.


Thus far I have a Company table, Unit table and Checksheet table.  A primary field in Company links to a foreign in Unit.  A primary field in Unit links to a foreign in Checksheets.  At this point I have the system working properly in creating check sheets for each unit and numbering them the way I want them to. 

On the Checksheet I have a field that calculates the Checksheet number.  My next step was to work out the Comment sheet that I email to the customer.  My idea was to have a fourth table "Comments" that was a "one-to-one" relationship with the Checksheet (possibly not the best database solution, but this is my first db).  The Comment sheet would be a series of Look-ups that checked each Comment field on the Checksheets and returned the comment to the Comment sheet.  If the comment field on the Checksheet was empty the look-up would return nothing.  (This idea may not work if the Look-up that sees nothing in the comment field actually puts a blank line in the Comment sheet.  I don't want to send a customer a page full of blank lines and one or two comments in amongst the blank lines.  But that's the next problem.)

I tried to set up a new relationship between the Comment and Checksheet using the Checksheet number (a calculated value ) and now I get an Invalid Index showing on my Comment sheet.  

I have been in the Forum and see that my mistake is that the calculated Checksheet number is not indexed and won't work as a primary key.  From the Forum I see that my solution will probably be a second Table Occurrence but when I try to follow the examples I find in the Forum (one for a fellow with an Omit/EmployeeID problem and another link to an explanation of Table Occurrences) I get lost.

Can anyone suggest where I go from here?

Happy New Year