This is a follow up to one of my previous questions where I am setting up a database in Access to track all of our Police Departments interviews/contacts with subjects. I was advised to separate information into the following tables:
Table 1: Officer info (Name/badge) Table 2: Subjects info (Name, DOB, SSN, Address, Phone #, Race, HT, WT, Tattoos, Vehicle Info, etc.) Table 3 Case # (Not all contacts with subjects will have a case #).
I also have fields of location of interview, and Date of contact. I am trying to build their relationships now and am confused as how to go about this process. I can't make Officer name and subject name a primary key and it wont allow me to enforce referential integrity. How do I build these relationships so that I can create searchable forms by any field that users want. For example: The user wants to search the DB for contact made with subject John Doe and pull up all information about John Doe (location of contact, Officer involved, DOB, Phone #, Vehicle Info, ect.) Example 2: user wants to enter a Date and pull all subjects and information on that date. Many users will have access to the DB.
Attached is my attempt to build the relationship tables, but its not looking so good