K K
asked on
MS Access
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
Relationship.PNG
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
Relationship.PNG
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would be hesitant to put SSN in an Access database for security reasons. You might want to consider putting it in SQL Server Express, which provides more control of access to that type of "personal" information.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Helen,
Does your DB allow for Data entry? I looked at it and it looks like the forms were set up for searching records only.
Does your DB allow for Data entry? I looked at it and it looks like the forms were set up for searching records only.
Yes, you can enter data and select items in the subform to set up a link. The record selector combo box in the header is just for going to the selected record. You may have to enable the database first (if you get that annoying yellow bar in the header).
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Split:
-- Helen_Feddema (https:#a40370674)
-- Eirman (https:#a40368929)
-- rspahitz (https:#a40368886)
-- James Elliott (https:#a40368874)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
MacroShadow
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Split:
-- Helen_Feddema (https:#a40370674)
-- Eirman (https:#a40368929)
-- rspahitz (https:#a40368886)
-- James Elliott (https:#a40368874)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
MacroShadow
Experts-Exchange Cleanup Volunteer
Next, you'll need to go into each related table and add a "foreign key" field that links back to that primary key.
For example,
Table 1: Officer info (Name/badge)
Table name: tblOfficer
Primary key field: OfficerID, type autonumber
OfficerName, type Text
Badge, type Text
Table 4:
Table name: tblOfficerCase
Primary key field: OfficerCaseID, type autonumber
OfficerID, type "lookup"* to tblOfficer
CaseID, type "lookup"* to tblCase (table 3)
the "lookup" type is technically a wizard found under the data type dropdown list that allows you to connect the field to another table. When it is done, it will set the foreign key (e.g. OfficerID in tblOfficerCase) to the same data type as the referenced field (number if using auto-number) and link to the specific fields that you wanted to view, such as officer name (even though you're connecting through the officerID)
See if that helps get you started. We can continue form there if it makes sense.