Many-to-Many Relationship to same table

[joleary]

I am working on a software requirements database and want to be able to relate requirements to one another, so that the reader can easily see what other requirements may duplicate or impact a given requirement. These requirements do not have a hierarchical relationship, I am trying to merely associate them as peers of one another.

I have set up an intersection table tix_ReqReq with the following structure:
ID (Autonum)
ReqIDA (the originating requirement, i.e., the requirement displayed on the main form to which other requirements are associated via a subform)
ReqIDB (the requirement associated to the originating requirement)

I can display (in a form and report) the requirements associated with the originating requirement, but want to be able to leverage the relationship specified in both directions. For example, if the table has the following values

ID ReqIDA ReqIDB
1 512 613
2 512 522
3 613 499
4 199 211

I want to be able to see on the form/report for Req 512, that it is related to requirements 613 and 522 (straightforward) and on the form/report for Req 613 to see that it is related to requirements 512 and 499 (harder). How do I make this work?

Thanks in advance.

 

[Banana]

Read up on self-join to get a primer: Allen has a great article on this.

The bitch of this is SQL doesn't quite handle recursive queries so well, so if it can go very very deep (e.g. 20 requirements), the query you would be look at would be quite a ditzy and slow. Now, you could either circumvent this by either 1) using VBA, or 2) decide on an arbitrary limit for the query and handle the deeper levels in some other ways.

HTH.

 

[joleary]

Thanks for your response. I think this is a bit different from a self join, in that there is a many-to-many relationship.

One thing I think we have going for us here is that we really only need the one level of the relationship, but we need it in both directions (e.g., if a relationship is identified from 512 to 613, it also identifies a relationship between 613 and 512 automatically).

Make sense?

 

[georgedwilkinson]

Yes, I have this peppered throughout all my original databases. SQL Server handles this a lot better than Access because you can just write a trigger to insert the "other" side. But be careful if you use a trigger since you could get caught in a recursive loop.

Typically when I have a table with a M:M join to itself, I typically have a "to designator" and a "from designator". So if I join my Parties table to itself with a "PartyRelationships" table as the junction table, I'll have the following mandatory fields in PartyRelationships:
FromPartyID
FromPartyRoleTypeID
ToPartyID
ToPartyRoleTypeID

This table has other stuff (Description, From & To Dates, ValidFlag, ID -- since this relationship is used all over the place).

But the interesting thing about this approach is that I can define (I actually use a much more complex setup than I'm describing) that there are only certain relationship types that are allowed. So if the relationship between 2 parties is Customer to Vendor, I never allow a relationship called Vendor to Customer (like I said, it is much more complex than I'm writing here). This keeps me from having to insert 2 rows into this table.

I'm not sure if this helps. I have quite a bit of experience implementing this.

Now, as to your initial post, have you considered using a sub-form for the "other" side? This is how I implement this. Your main form will be for one side of the relationship and your sub-form will be for the junction table, not displaying the main side data but displaying the "other" side data. You can easily create the relationship in this arrangement.

Does this help at all?

 

[joleary]

Thanks George, feels like we're on the right track. Is there a way to implement a trigger in Access (to follow the structure you are outlining)?

Best, Jim

 

[georgedwilkinson]

Not directly in the database like you would a real RDBMS. You can put code in your after insert on your main form to insert the other row. Make sure to take the other row out in your form's after del confirm (I think).

If somebody enters the data directly into a table or query though, there is no way to handle it automatically.

You can also create an update query that runs every now and then that inserts the "other" side. There are risks with both of these approaches, as there are in using triggers.

Just be careful that it's dummy proof.

 

[joleary]

Thanks George, that worked. To help the next person trying to solve a similar problem, here is the code I wrote to create the mirror record, as well as to delete the mirror when deleting the main record.

Best, Jim

Private Sub Form_AfterInsert()
Dim curDB As Database
Dim recSet As Recordset
'create a mirror of the requirement association so that the
'relationship is available to all
Set curDB = CurrentDb
Set recSet = curDB.OpenRecordset("tix_ReqReq")
With recSet
.AddNew
'add variables here
!ToReqID = Me.FromReqID ' these are intentionally switched to create the mirror
!FromReqID = Me.ToReqID
!Note = Nz(Me.Note, "")
.Update
End With
recSet.Close
Set recSet = Nothing
End Sub

Private Sub Form_Delete(Cancel As Integer)
'handle the deletion of both the record selected and its mirror
Dim fromReq As Integer
Dim toReq As Integer
Dim curDB As Database
Dim recSet As Recordset
Dim sSQL As String

On Error GoTo errHandler

'copy the ReqIDs to an unbound field so they can be used later
' Display custom dialog box.
If MsgBox("Are you sure you want to delete this record?", vbYesNo) = vbYes Then
'delete the mirror record first
'note that we don't delete the primary record
'in the code because it is already being deleted
fromReq = Me.ToReqID ' these are intentionally switched to delete the mirror
toReq = Me.FromReqID
sSQL = "SELECT * FROM tix_ReqReq WHERE (fromReqID = " & fromReq & " AND toReqID = " & toReq & ");"
Set curDB = CurrentDb
Set recSet = curDB.OpenRecordset(sSQL)
If Not recSet.EOF Then
With recSet
.Delete
End With
End If
recSet.Close
Set recSet = Nothing
Set curDB = Nothing
Else
DoCmd.CancelEvent 'cancel the deletion if the user doesn't select ok
End If

errHandler:
'MsgBox Err.Description
Resume Next

End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
' Suppress default Delete Confirm dialog box.
Response = acDataErrContinue
End Sub

 

[georgedwilkinson]

Excellent! I usually only use SQL Server but if I need this for an Access only project, I'll put it to use.

Thanks for posting back.