I am new to Access and have created a database for Doctors information in work.
I created a database with 3 tables. The Primary Key is the Doctors staff Number, the Doctors staff Number is also included in the other 2 tables and this is both the primary and secondary key linking all 3 tables.
At the beginning when I created a new record in the 'Primary table' and saved it, there would be a record automatically created in the other 2 tables. These would be blank with just the Doctors Staff Number filled in. However this no longer works. It now creates a record in the Primary table but not the other 2. How do I fix this?
I also tried to create a form and included fields from all 3 tables. This works fine if I am updating information however if I try to create a new record I get an error message The Microsoft Access Database engine cannot find a record in the table 'Table 2' with key matching field(s) 'Doctors Staff Number'. Does this mean there is a problem with my key? as there is clearer a key in Table 2 named Doctors Staff Number.
Any help with this would be great. Thanks