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