Please can I get some help to decide how best to structure my tables for the scenario below?
I want to get ‘tools’ made and I want to do so by issuing a request via a printed request form (printed report).
So table ‘A’ will be ‘Tool Request’ which will contain a description of what is required, who raised the request and attachments for drawings etc.
This ‘Tool Request’ will be used to make different types of tools by category.
One tool category in particular is ‘Fixtures’ which needs to have an incremental serial number and be recorded with that and other specific information relating only to this specific category.
We also have lots of ‘Fixtures’ that were created and serial numbered via manual paper system (approximately 1450) so I created a separate table for ‘Fixtures’ to store those that were made via the Tool Request form and also to record the old ‘Fixtures’ to this single table.
I added a field to the ‘Fixtures’ table to store the Tool Request ID so that I could link a subform (child) to the Tool Request (main) form.
Problem is, for any record that I want to enter for an old ‘Fixture’ that will not have a Tool Request ID, I get the error message that I ‘cannot add or change a record because a related record is required in Tool Request table’
Can anyone please suggest how to overcome this or a better way to record the information?
Thanks (in anticipation!)
Steve