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
K KAsked:
Who is Participating?
Helen FeddemaCommented:
You probably need a many-to-many relationship here -- one officer might interact with multiple subjects, and one subject with multiple officers.  See my sample database, which links Scouts and Badges in a many-to-many relationship.

Here is a link for downloading the database:

and here is a screen shot of the form:
Many-to-many form
Traditionally, you use an autonumber field as the primary key of each table.  Using that, you get a guaranteed-unique value to reference, then you can use a lookup based on that.  To implement that, start by adding an ID field of type AutoNumber to each table and set that field as the key.

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.
James ElliottManaging DirectorCommented:
Well first of all you need to have Officer_ID in your interview table which then joins to ID in your officer table.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
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.
Ooops..sorry...just saw your image.  Seems you have the IDs already.  So what you need to do is change the linked field in the Field Interview table to point back to the Officer ID in the Officer table.  The easiest way is to create a new field called OfficerID and use the "lookup" wizard in the data type dropdown as I described above.  When you're done, you can delete the Subject Last Name field (unless you've already added data to it, in which case I can show you how to migrate it to this new field.)

Likewise, instead of linking FID to Case#, you'll want to point it to the Case ID (and probably need a new field for that, following the lookup wizard.)
EirmanChief Operations ManagerCommented:
Thanks for the points in the previous related question K K
You need four tables ...


To get the best help here, I would suggest that you make up a sample database and post it.  No data is necessary, but do create the important fields and do your best with the relationships.  Experts will be happy to correct any errors and give suggestions.
(What I'm saying is ... experts are unlikely to create a database for you, but will edit your database)
K KAuthor Commented:
Does your DB allow for Data entry?  I looked at it and it looks like the forms were set up for searching records only.
Helen FeddemaCommented:
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:

-- 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.

Experts-Exchange Cleanup Volunteer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.