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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Dale FyeOwner, Developing Solutions LLCCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.