MS Access

Posted on 2014-10-08
Last Modified: 2016-07-05
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
Question by:K K
LVL 22

Expert Comment

ID: 40368870
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.
LVL 12

Assisted Solution

by:James Elliott
James Elliott earned 125 total points
ID: 40368874
Well first of all you need to have Officer_ID in your interview table which then joins to ID in your officer table.
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40368882
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.
LVL 22

Assisted Solution

rspahitz earned 125 total points
ID: 40368886
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.)
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

LVL 23

Assisted Solution

Eirman earned 125 total points
ID: 40368929
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)
LVL 31

Accepted Solution

Helen_Feddema earned 125 total points
ID: 40370674
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

Author Comment

by:K K
ID: 40378304
Does your DB allow for Data entry?  I looked at it and it looks like the forms were set up for searching records only.
LVL 31

Expert Comment

ID: 40396836
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).
LVL 26

Expert Comment

ID: 41691620
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now