How to Link Related Cases? 2

[dedren]

My database manages investigative cases. Once I open a case I would like it to show related cases based on last names.

Currently the main form has several subforms pulled from multiple tables. When I try to create a query that filters by the last name it brings up no results. I think this is because the subform it is referencing is a datasheet, has multiple LastName controls, and the first control it references is blank.

How do I get it to check the rest of the controls and bring up related cases?

Objects
Main Form: frmCaseMain
Subform1: sbfCaseLink
Subform2: sbfInvolvedParties
Control: LastName

This is the Criteria I used in my query:
Forms!frmCase.sbfInvolvedParties.Form!LastName

 

[dhookom]

It would help if you provided your table relationships. I can't believe linking or relating on LastName would uniquely identify records in either parent or child tables. Forms and subforms are typically related based on the Primary Key field from the master table to a Foreign Key field in the child table.

Duane
Hook'D on Access
MS Access MVP

 

[dedren]

Here are table relationships:

tblCaseMain - Has the bulk of the case information. CaseNum is the primary key.

tblNamelst - Holds all the names and has many names per CaseNum. NameID is the primary key (not used) but I use CaseNum as the foreign key in this table.

Here is how it works for the end user:

The user creates a case on a form and in the sub-form they can enter multiple people who are involved in the case.

I have attached a link to the PDF of the relationship screen of the DB. It is a Google Docs link that I made smaller using TinyURL.com.

 

[dhookom]

I (and many others) don't have access to your pdf.

Duane
Hook'D on Access
MS Access MVP

 

[dedren]

Not sure why that didn't work, maybe making it a TinyURL messed it up; here's the full link:

 

[dhookom]

Do you have the LastName field in the main form (assuming that is the form you are searching)? If not, you can't search a main form for a value from the subform.

You could create a "find record" combo box in the main form header that has a row source based on tblNamesLst. The bound column of this combo box would need to be CaseNum.

Duane
Hook'D on Access
MS Access MVP

 

[dedren]

I believe I can search a subform because Forms!frmCase.sbfInvolvedParties.Form!LastName worked to reference the control of subform2, but it only checked the first control of subform2's datasheet. Also the article on this page gives the syntax to do it:

http://blogs.techrepublic.com.com/msoffice/?p=617

Does that make sense?

I have already designed a find record option, but that method is too slow for investigations. I wanted MS Access to search for other related cases automatically based on the information that the investigator entered. This way they could quickly see if the perpetrator was involved in other cases.

Let me see if I can explain it properly. The main form is called frmCase and has CaseNum as its primary key. On frmCase is a subform called sbfInvolvedParties. sbfInvolvedParties displays all the names of people who are involved in this particular case and are linked by CaseNum. In sbfInvolvedParties is a control called LastName, which is what I would like to use to find other cases that match the name.

Is that what you thought I meant?

 

[dhookom]

You can search a subform but only the current records displayed/returned in the subform (current case only).

You would need to create a search form or combo box that would search all records in tblNamesLst for matching names.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I think the reason people are confused is because your database is improperly designed.

If I understand correctly.
If john Smith is involved in case1 then there is a record in tblNameslst with all of John Smiths information and a foreign key "case1" to case1 in tblCasemain. If he is involved in Case2 then there is another record with all of his information and a foreign key "case2".

This violates normality. You should have a junction table because you are describing a Many to Many relationship. A case can have many persons and a person can be involved in many cases.

tblCase_Person
CaseID
personID


Then remove the case number from tblPerson

Assume John Smith has a NameID of NID1, and he is involved in cases Case1, Case2, and Case22. Then all you store is on record in tblNameLst.

CaseID PersonID
NID1 Case1
NID1 Case2
NID1 Case22

If your db was properly designed returning every case that John Smith is involved in would happen instantaneously. Entering data would be much quicker for repeat offenders.
Also you can easily query
John Smith from main street vs John Smith from 1st street since each John Smith has a unique Name ID that uniquely identifies a person.

Based on how you use this database, I really would spend the time to normalize your database instead of band-aiding this.

 

[dedren]

Ah, that makes a lot of sense. This was my first database and I was learning through Google, as I went along, under serious time constraints. I think I can convince the team to hold off on this as I normalize the table and split the DB between front end and back end, which should also solve the problem they have with locking each other out. Thank you both very much!

 

[MajP]

However, this will likely add a bit of mission creep. Now your subform "parties involved" will be based on a query that joins the junction table to the names table. The subform is linked to the mainform by the caseid in the junction table.

You are going to want to be able to quickly search the existing names from a list and if that person is not already in your list then you want to add their details to the names table. Once they are added to the names table you can select them from the list in your subform. There are several ways to set up this interface, but it will be more involved than what your currently have.

Since most of the time I assume your perps are not repeat offenders this is how I would make the interface. The subform is not editable. I only can add or delete related records. I would have a button to add a associate a name to the case. It would pop open a form with First, Last and Middle. All other fields are hidden. Once you add first, last, middle it will query the db for possible matches, and display them. If there is a match you can select and associate to your case. If not you can continue to add the person's details. When complete then it would associate the new record to the case.

Bottom line to associate a name to a case it has to first exist in the name table. Either you can select an existing name, or you have to create a new name and then select it.

 

[dedren]

Also, just to make sure I understand. To normalize this properly I would just need to create a separate table with the primary keys of tblCase and tblNames and have each table establish a relationship with it?