Primary Key in a Multivalue Field

[LegalTec]

I have a completed database to manage attorneys cases and it works great! There is a Case_Table and an Attorney_Table with a one to many relationship connecting the two. I have a field in the Case_Table titled "Assigned To" which allows a user to see a drop-down list of every attorney in the office and choose ONE to assign to a case.

The issue im facing now is the need to assign MULTIPLE attorneys to a case. Simply switching the "Assign to" lookupfield to store multiple values fails because the "values" are the Primary Keys of each Attorney, and throws an error message.

What I've Tried:
Deleting the "Assign To" Field from the Case_Table and creating a Joining_Table which contains the Case_ID and Attorney_ID, which I cannot understand where the new "Assign To" field for the form would be????

 

[kjv1611]

What you've tried is the correct route to go. Here's how it will work:

Table structure:

CaseTable (CaseID, CaseDate, OtherCaseDetailFields that are absolutely 100% case specific)
AttorneyTable (AttorneyID, Name, Address, Phone, etc)
CaseAttorneyMatch (CaseAttorneyMatchID, CaseID, AttorneyID)

So to find what attorney is matched to what case, you'll need to look at your new table, CaseAttorneyMatch. You want to keep the new ID in that table so you do have a UniqueID.

Then if you have a situation where one Attorney is the Lead Attorney, then you could add another field to that third table so you'd have something like:
CaseAttorneyMatch (CaseAttorneyMatchID, CaseID, AttorneyID, [highlight]IsLeadAttInCase[/highlight])

And the IsLeadAttInCase could be set as a Number - Byte value field, using 0 and 1 values... 0 = not lead att, 1 = lead att, and either set 1 as default, only allow one per case/att match, or else only set it when indeed an att is the lead attorney.

Make sense?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[LegalTec]

Thank you for the quick reply!

I have this setup now as you stated, however the big issue is with the interface. The interface currently is setup as follows: There is a form called "Case" this form works off a query and allows a user to enter all of the details for a new case and assign it to an attorney in the "assign to" field, which is essentially a Lookup field that displays a list of attorneys from the AttorneyTable.

I'm now trying to figure out how to leave this simple form structure the same, and with the "assign to" field now being deleted from the CaseTable what field will be used as the multivalue lookup list?

 

[kjv1611]

You simply create a new record in your CaseAttorneyMatch table. I'd just use an INSERT SQL statement from VBA like this:

Private Sub cmdAssignAttorneyToCase_Click()
   Dim strSQL As String
   strSQL = "INSERT INTO CaseAttorneyMatch (CaseID ,AttorneyID ,IsLeadAttInCase) " & vbCrLf & _
            "SELECT " & Form.CaseSubForm.CaseID & ", " & Form.AttorneySubform.AttorneyID & ", " & Form.AttorneySubform.IsLeadAttorneyInCase
   DoCmd.SetWarnings Off
   DoCmd.RunSQL strSQL
   DoCmd.SetWarnings On
End Sub

The only other thing you might want to add in would be checking to see whether that match/pair already exists in the CaseAtt table. You could build in a WHERE clause, or else build a SELECT statement and use a recordset to check for record-count <= 0 to continue. It's up to you.

Also, how you refer to the controls will depend upon where your button is, and how your fields are laid out. I'm assuming the button is on the main form, and then you have the caseID and attorneyID on their own subforms.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

By the way, I probably listed the syntax incorrectly on referencing your subforms. Here's one good reference. I regularly have to look it up, b/c I forget:

http://www.techrepublic.com/blog/msoffice/how-to-reference-an-access-subform/188

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[LegalTec]

Thank you for your help!