Many-to-Many Referencing Same Table (without hierarchy)

  • Thread starter Thread starter jimo
  • Start date Start date
J

jimo

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.
 
First build a union query to give you the "matchups" of the numbers; let's
call it qrytix_ReqReq (I'm u:

SELECT T1.ReqIDA, T1.ReqIDB
FROM tix_ReqReq AS T1
UNION ALL
SELECT T2.ReqIDB, T2.ReqIDA
FROM tix_ReqReq AS T2;


Then build a second query to give you the desired matches; let's call it
qrytix_ReqReq_Results:

SELECT tix_ReqReq.ReqIDA, qrytix_ReqReq.ReqIDB
FROM tix_ReqReq INNER JOIN qrytix_ReqReq
ON tix_ReqReq.ReqIDA = qrytix_ReqReq.ReqIDA;
 
Back
Top