Assign multiple people to one record - easily

[Annette]

I have a Case Notes database that I need help. The database consists
of a case table, a people table and a fact table. Each Case has many
people related to it and each case has many facts related to it.

I thought about creating a case table with the unique value of CaseID,
creating a people table with a unique value of PeopleID, but linked to
the case table by CaseID then creating a fact table with a unique
value of FactID linked to the case table by CaseID.

Each of the facts I enter could relate to a person. For example:

Case123 has John, Mary, Bob and Sue all related to it and facts 787,
788 and 789.
Fact787 states "John hit Bob". I would like the people John and Bob
related to this fact record.
Fact788 states "Mary hit Bob". I would like the people Mary and Bob
related to his fact record.
Fact789 states "John hit Mary". I would like the people John and Mary
related to this fact record.

How can I create a form that will allow the user to enter the fact for
a case and link the fact to all the people related to it? Is there a
way to show a list and multi select people? Any other suggestions?

 

[Steve]

Have you considered adding another table describing the relationship between the facts and the people involved?

The table could contain a FactID and PeopleID, (also a relationshipID as primary key if you'd like). Then, each person that is related to a fact wouldhave a separate record. This may be helpful if there isn't a set number ofpeople always involved with a fact.

 

[Annette]

Have you considered adding another table describing the relationship between the facts and the people involved?

The table could contain a FactID and PeopleID, (also a relationshipID as primary key if you'd like). Then, each person that is related to a fact would have a separate record. This may be helpful if there isn't a set number of people always involved with a fact.

Yes, that would be necessary. I guess what I was looking for is how
the form can be created that will allow for the entry of the fact and
then the selection of people to the fact. Would it be as simple as
using a list box and then selecting each name or how can it be done
without having a fact form with a people subform and then selecting
from a list. Any ideas?

 

[John W. Vinson]

Yes, that would be necessary. I guess what I was looking for is how
the form can be created that will allow for the entry of the fact and
then the selection of people to the fact. Would it be as simple as
using a list box and then selecting each name or how can it be done
without having a fact form with a people subform and then selecting
from a list. Any ideas?

You can do either; a subform with one record per person (using a combo box
with autocomplete set, so you can just start typing the name rather than
scrolling down the list) is very common.

You can also use a multiselect Listbox with some VBA code to allow the user to
select multiple names and click a button to load the selected names into a
table. Here's some sample code from one of my databases that you may be able
to adapt.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub


--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com