Let me reword one of your statements into what I think is your answer.
YOURS: This layout is fine for a one company layout, but it would be beneficial to introduce a table that lists multiple companies that would be used to select audit questions, cross references, and placement of remarks etc. for each company.
MINE: This layout is fine for a one company layout, but it would be beneficial to introduce a LAYOUT that lists multiple companies that would be used to select audit questions, cross references, and placement of remarks etc. for each company.
This layout would be in list view, based on tblOperators which is then linked by tblOperators::_OperatorIDpk = tblProtocol2::_OperatorIDfk
I think you almost got it, I did not explain it clearly enough. I only need to select one company in a Drop-down list that is associated with the company name field which would then display all the field information relating to the protocol. The layout would not be appropriate in list view, only one company should be viewed at a time, but it would be benefical to change companies at any point in the protocol to compare information with previous audits.
So I'm not quite were I want to be yet.
My answer stands with the following edits:
1. use form view instead of list view
2. do a find for the company in the Company field
3. leave the linkage as described in my previous email
4. put fields on your layout from the other tables as appropriate
FWIW, you could make it fancy by putting a global field (tblOperators::gCompany) on the layout and tag it with an OnModify script trigger.
Create a value list based on the Company Field
Make the global field on the layout a dropdown list using that value list.
Have the OnModify script do your find for you.
That way when you change the value in your global field, it will search for that record rather than you having to manually do the find each time.
Your tblOperators::_OperatorIDpk = tblProtocol2::_OperatorIDfk gives a one company to many audit Protocols which cannot happen for a given year. The relationship has to be a one to one in this case. I did not state that the audit is updated on January of each year and expires July 1 of the following year. So companies will see and be audited every two years so a one to one relationship is appropriate here. The advantage is that the auditor can look across the companies he has audited over the last 18 months. If an auditor does 10 to 15 audits in this time frame he is doing very good.