Solved

MS Access

Posted on 2014-10-08
9
47 Views
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
Relationship.PNG
0
Comment
Question by:K K
9 Comments
 
LVL 22

Expert Comment

by:rspahitz
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.
0
 
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.
0
 
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.
0
 
LVL 22

Assisted Solution

by:rspahitz
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.)
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 23

Assisted Solution

by:Eirman
Eirman earned 125 total points
ID: 40368929
Thanks for the points in the previous related question K K
You need four tables ...

Officers
Cases
Subjects
Interviews

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)
0
 
LVL 31

Accepted Solution

by:
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:
http://www.helenfeddema.com/Files/accarch178.zip

and here is a screen shot of the form:
Many-to-many form
0
 

Author Comment

by:K K
ID: 40378304
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.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
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).
0
 
LVL 27

Expert Comment

by:MacroShadow
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:

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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

895 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

14 Experts available now in Live!

Get 1:1 Help Now