How to query an extendable linking record?

[beemomo]

Hi,I am using MS Access 2007.Please forgive me if i post a silly question, as i am still new in Access and VBA.

I have 3 tables named CRS, Document and DocumentInCRS.T he CRS record in CRS table might belong to a CRS Open Or CRS Response.In the CRS table there is a field named RESPONSE_TO_CRS ID,if it is a CRS Response, then it must have the Response_TO_CRS ID - these ID might belong to CRS Open or CRS Response.

let say:
CRS01 = CRS Open
CRS03 = CRS Response -> RESPONSE_TO_CRS ID: CRS01
CRS06 = CRS Response -> RESPONSE_TO_CRS ID: CRS03 (but CRS03 response to CRS01)
CRS10 = CRS Response -> RESPONSE_TO_CRS ID: CRS06 (but CRS06 response to CRS03, CRS03 response to CRS01)

I do not know many the times relationship will reach, but would not be more than 10.

The relationships between CRS and Document is many-to-many.DocumentIn CRS table acts as bridge table to link CRS table and Document table together.

For those CRS reponse, I wouldn't store their previous document records in DocumentInCRS again because it is link to their response-to CRS by Response_TO_CRS ID and thus they will contain their response-to CRS document records.However ,if there are new documents added to CRS Response,the record will be added in DocumentInCRS.

Using the SQL statement,I want the result for CRS10 to contain all the document records in CRS06, CRS03, CRS01 and itself.
Same case for CRS06 and CRS03 where I can get all the documents record until CRS01.

Is there a proper way to do this?Hope to hear from you guys as soon as possible because I am really stucked :(
Many thanks

[FishVal]

Hello, beemomo.

A general approach is to use recursive logic.
For starters you may read this thread.

Regards,
Fish

[FishVal]

Ok.

I will outline the logic of possible solution.

• A recursive procedure creates delimited list containing CRS and all subsequent CRSs.
• The recursive procedure is being applied to CRSs table (filtered if you want) to get dataset of delimited lists.
• Thus obtained recrdsset is being joined with Documents table (via bridge table certainly). Criteria for join is existance in list.
• A simple VBA function is used to check the criteria. Below is a simple example of how it could be done (for numbers list).
  
  Metadata:
  
  [tblLists]
  txtList, Text(255)
  
  [tblNumbers]
  lngID, Long
  
  Code module:
  [code=vb]
  Public Function IsNumberInList( strList As Variant, lngNumber As Variant) As Boolean
  
  Dim alngList As Variant
  Dim i As Integer
  
  IsNumberInList = False
  
  If IsNull(strList) Or IsNull(lngNumbe r) Then Exit Function
  
  alngList = Split(strList, ";")
  
  For i = 0 To UBound(alngList )
  If lngNumber = Val(alngList(i) ) Then
  IsNumberInList = True
  Exit Function
  End If
  Next i
  
  
  End Function
  [/code]
  
  Query:
  [code=sql]
  SELECT tblLists.txtLis t, tblNumbers.lngI D
  FROM tblLists INNER JOIN tblNumbers ON IsNumberInList( tblLists.txtLis t, tblNumbers.lngI D);
  [/code]

Regards,
Fish

P.S. Your question is not silly. I would say it quite interesting.

[FishVal]

Actually, intermediate delimited list is a bit overkill.

You could join CRSs table with itself on criteria returned by recursive procedure which determines whether a certain CRS is a descendant of another one.

Regards,
Fish

[beemomo]

hi FishVal,

I appreciate your helps a lot, please give me a little more time to understand the logic and then apply it into my database, i'll get back to you soon!

Thanks again

[beemomo]

Hi fish,

I should have describe how i displayed the documents details in a report by clicking a button earlier before i encounter the recursive problem. The result is correct for the first descendant of CRS OPEN only.

I have 3 comboxes and a button on my form:

cboCRSStatus2 - to select the CRS status : either open or response in CRS table.
cboProjectNo2 - to select the projectNo in CRS table (filtered by cboCRSStatus2)
cboCRSNO2 - to select the any of CRSNO2 with status open or response in CRS table (filtered by cboProjectNo2)

here my codes go in the button cmdCheck :

Private Sub cmdCheck_Click()
    Dim strSQL, quote, crsID, ResponseToCRSID, strUnion As String
    quote = "'"
    crsID = Me.cboCRSNO2.Column(1)
    ResponseToCRSID = Me.cboCRSNO2.Column(2)
    strUnion = "SELECT Document.DOC_ID, Document.TITLE, CRS.CRS_ID,CRS.CRS_NO, CRS.PROJECT_NO,CRS_OPEN, CRS_RESPONSE, RESPONSE_TO, CRS.CRS_INVOICED, CRS.INVOICE_NO FROM (Document INNER JOIN DocumentInCRS ON Document.DOC_ID = DocumentInCRS.DOC_ID) INNER JOIN CRS ON DocumentInCRS.CRS_ID = CRS.CRS_ID WHERE CRS.CRS_ID = " & quote & "" & crsID & "" & quote & " ORDER BY Document.DOC_NO ASC"

    If IsNull(Me.cboCRSStatus2.Value) And IsNull(Me.cboProjectNo2.Value) And IsNull(Me.cboCRSNO2.Value) Then
        MsgBox "Please select a CRS Status, a Project NO and a CRS NO.", vbOKOnly
    ElseIf cboCRSStatus2 = "Open" And Not IsNull(Me.cboProjectNo2.Value) And Not IsNull(Me.cboCRSNO2.Value) Then
        strSQL = "SELECT Document.DOC_ID, Document.TITLE, CRS.CRS_ID,CRS.CRS_NO, CRS.PROJECT_NO,CRS_OPEN, CRS_RESPONSE, RESPONSE_TO, CRS.CRS_INVOICED, CRS.INVOICE_NO FROM (Document INNER JOIN DocumentInCRS ON Document.DOC_ID = DocumentInCRS.DOC_ID) INNER JOIN CRS ON DocumentInCRS.CRS_ID = CRS.CRS_ID WHERE CRS.CRS_ID = " & quote & "" & crsID & "" & quote & " ORDER BY Document.DOC_NO ASC"
    
    CurrentDb.QueryDefs("DocumentCRS").sql = strSQL
    DoCmd.OpenQuery "DocumentCRS"
    
    On Error GoTo Err_cmdCheck_Click
        stDocName = "DocumentCRS"
        DoCmd.OpenReport stDocName, acViewReport
        
    ElseIf Me.cboCRSStatus2 = "Response" And Not IsNull(Me.cboProjectNo2.Value) And Not IsNull(Me.cboCRSNO2.Value) Then
        strSQL = "SELECT Document.Document.DOC_ID, Document.TITLE, CRS.CRS_ID,CRS.CRS_NO, CRS.PROJECT_NO, CRS_OPEN, CRS_RESPONSE, RESPONSE_TO, CRS.CRS_INVOICED, CRS.INVOICE_NO FROM (Document INNER JOIN DocumentInCRS ON Document.DOC_ID = DocumentInCRS.DOC_ID) INNER JOIN CRS ON DocumentInCRS.CRS_ID = CRS.RESPONSE_TO_CRSID WHERE CRS.RESPONSE_TO_CRSID = " & quote & "" & ResponseToCRSID & "" & quote & " ORDER BY Document.DOC_NO ASC UNION " & strUnion & ""
    
    CurrentDb.QueryDefs("DocumentCRS").sql = strSQL
    DoCmd.OpenQuery "DocumentCRS"
    
    On Error GoTo Err_cmdCheck_Click
        stDocName = "DocumentCRS"
        DoCmd.OpenReport stDocName, acViewReport
        
    End If
    
Exit_cmdCheck_Click:
    Exit Sub

Err_cmdCheck_Click:
    MsgBox Err.Description
    Resume Exit_cmdCheck_Click
End Sub

I am wondering is there another way to get the documents details like what I describe in my first post, using array perhaps, considering my form
controls design above? rather than the recursive procedure? i has spend a day to try to understand it but it still seems very complicated for me.

Thank you.

Regards,
beemomo

[FishVal]

Hello, beemomo.

Let me outline logic of solution I suggest you:

• [CRSs] table is joined with itself on criteria "both CRSs belong to the same response chain"
  [code=sql]
  SELECT [CRSs].[CRSID], [CRSs1].[CRSID] FROM [CRSs] INNER JOIN [CRSs] AS [CRSs1] ON ...<join criteria>...
  [/code]
  You could imagine this as combining [CRSs] table records each with each followed by filtering on <join criteria>.
• Now about <join criteria>.
  It has to get two values of [CRSID] - from [CRSs] table and its alias, and return True if the second value is the same [CRSID] or its descendant in "response-to" hierarchy.
• The best way to implement the logic of <join criteria> is to write VBA function. Below is the function pseudocode:
  [code=vb]
  Public Function IsDescendant(va rCRS1 As Variant, varCRS2 As Variant) As Boolean
  
  If <at least one argument is Null> Then Exit Function
  
  Dim varCRSResponseT o
  
  IsDescendant=Tr ue
  
  Do
  if varCRS1=varCRS2 Then Exit Function 'return True
  varCRS1 = <get [CRSID] varCRS1 responses to>
  Loop While <exist varCRS1>
  
  IsDescendant=Tr ue 'return false
  
  End Function
  [/code]
  
  The code scans "response-to" hierarchy down from varCRS1 until find varCRS2 (return True) or no more descendants could be found (return False)

Hope this makes sense.

Regards,
Fish

[beemomo]

Hi Fish

Sorry if i keep u waiting, i've had do some studies on the joining criteria, guess i will need time to implement it in the code as i am still weak at coding, i appreciate your helps and immediate responses a lot.

I will get back to you as soon as possible.

Thanks again.

[FishVal]

Hello, beemomo.

The pseudocode I've posted contains several typos.
Sorry for that, below is revised version.
:( it is much more easy to write real code. ;)

[code=vb]
Public Function IsDescendant(va rCRS1 As Variant, varCRS2 As Variant) As Boolean

If <at least one argument is Null> Then Exit Function

IsDescendant=Tr ue

Do
if varCRS1=varCRS2 Then Exit Function 'return True
varCRS1 = <get [CRSID] varCRS1 responses to>
Loop While <exist varCRS1>

IsDescendant=Fa lse 'return false

End Function
[/code]

Best regards,
Fish.

P.S. Feel free to ask if you have difficulties in coding.

[beemomo]

Hello fish,

Sorry again if i keep u waiting, I have found a solution which sounds easier for me.

I have added a new field - OPENFID in CRS table. When a user checked the chkOpen in the form, its CRS ID will be captured into the new field OPENFID. If there is CRS(s) Response later on which is related to the aforementioned CRS, then the OPENFID will be captured again when the user select the CRS which it is responsed to; from cboResponseTo (where I have selected the OPENFID in its rowsource as one of the field to be appeared in this combo box). The same logic is applied to the other descendants.

Since the CRS(s) Response will have the same OPENFID, then I can do a query to retrieved all the CRSs that hold the same OPENFID, and next
I can search for all the documents details of the CRS(s) above. Currently, this method just looks fine for me.

My code :

    Dim strSQL, strFID, Oparen, Cparen, quote, FID As String
    Oparen = "("
    Cparen = ")"
    quote = "'"
    crsID = Me.cboCRSNO2.Column(1)
    FID = Me.cboCRSNO2.Column(2)

    strFID = "SELECT CRS_ID FROM CRS WHERE OPENFID = " & quote & "" & FID & "" & quote & " and CRS_ID <= " & quote & "" & crsID & "" & quote & ""
    strSQL = "SELECT Document.DOC_NO, Document.DOC_ID, Document.TITLE,  CRS.CRS_ID,CRS.CRS_NO, CRS_OPEN, CRS_RESPONSE, RESPONSE_TO, CRS.CRS_INVOICED, CRS.INVOICE_NO FROM (Document INNER JOIN DocumentInCRS ON Document.DOC_ID = DocumentInCRS.DOC_ID) INNER JOIN CRS ON DocumentInCRS.CRS_ID = CRS.CRS_ID WHERE CRS.CRS_ID IN " & Oparen & "" & strFID & "" & Cparen & ""

However, I sincerely appreciate your helps and advices.Thank your for not only spending times to read my problem, but to show and explain the solution logics and function pseudocode patiently.I gain a new knowledge on recursive logic, many thanks again!

Regards,
beemomo

[FishVal]

Hello, beemomo.

Your solution is quite good but it has a fundamental flaw - data duplication.
Really, now "response-to" hierarchy is defined by [RESPONSE_TO_CRS ID] field and [OPENFID]. It will require a sophisticated logic to keep data consistent.

However, if "response-to" hierarchy is defined once in a manner you have described and further alteration of [RESPONSE_TO_CRS ID] value(s) in
records with defined [OPENFID] values is not expected, then your solution will live a long and happy life. ;)

Regards,
Fish

P.S. And ... You don't need to apologize. Really don't need. :)

[beemomo]

Thanks for your reply , Fish. Hopefully my solution will live long and happily as you said :) lol

Here I come with another problem, that would be good if you can point out my mistakes.

cboDocNo rowsource with example data:

DocNo Rev Title Status DocID

Safety Rev00 FS A Doc0002
Safety Rev01 FS B Doc0003
Safety Rev02 FS Q Doc0005

After I select the Safety Rev01 or Rev02, the combobox navigation cursor will point back to Safety Rev00.However, the data entered in table is correct (still the data from the row that I select). The bound column that I set is 1 (DocNo).

Why is this happening? I have tried googling around, but still cannot get the information that I need.

Is there a way to set the cursor to the position where it is selected?

[FishVal]

Hello, beemomo.

Here I come with another problem, that would be good if you can point out my mistakes.

cboDocNo rowsource with example data:

DocNo Rev Title Status DocID

Safety Rev00 FS A Doc0002
Safety Rev01 FS B Doc0003
Safety Rev02 FS Q Doc0005

After I select the Safety Rev01 or Rev02, the combobox navigation cursor will point back to Safety Rev00.However, the data entered in table is correct (still the data from the row that I select). The bound column that I set is 1 (DocNo).

Why is this happening? I have tried googling around, but still cannot get the information that I need.

Is there a way to set the cursor to the position where it is selected?

The easiest and the most appropriate way to solve it is to choose unique field as bound.
When you save [DocNo] in table via this combobox, how could you further distinguish between same [DocNo] value expected to have different meanings.

Regards,
Fish

[beemomo]

Hi Fish,

I will use DocID as bound column instead of DocNo.
Thank you very much for the helps along. Glad to be your student!

Cheers, :)
beemomo

[FishVal]

You are welcome. :)

Good luck.