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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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