如何查询可扩展的链接记录？ [英] How to query an extendable linking record?

问题描述

我正在使用MS Access 2007.如果我发布一个愚蠢的问题，请原谅我，因为我在Access和VBA中仍然是新手。


我有3个名为CRS的表，文档和DocumentInCRS.CRS表中的CRS记录可能属于CRS Open或CRS响应。在CRS表中有一个名为RESPONSE_TO_CRSID的字段，如果它是CRS响应，则它必须具有Response_TO_CRSID - 这些ID可能属于CRS公开或CRS回应。


假设：

CRS01 = CRS公开

CRS03 = CRS响应 - > RESPONSE_TO_CRSID：CRS01

CRS06 = CRS响应 - > RESPONSE_TO_CRSID：CRS03（但CRS03对CRS01的响应）

CRS10 = CRS响应 - > RESPONSE_TO_CRSID：CRS06（但CRS06对CRS03的响应，CRS03对CRS01的响应）


我不知道很多次关系会达到，但不会超过10。


CRS和Document之间的关系是多对多的.DocumentInCRS表充当桥表，将CRS表和Document表连接在一起。


对于那些CRS响应，我不会再将它们以前的文档记录存储在DocumentInCRS中，因为它是通过Response_TO_CRSID链接到它们对CRS的响应，因此它们将包含它们对CRS文档记录的响应。但是，如果有新文档添加到CRS响应，记录将添加到DocumentInCRS中。


使用SQL语句，我希望CRS10的结果包含CRS06，CRS03，CRS01及其自身的所有文档记录。 br />
CRS06和CRS03的情况相同，我可以将所有文件记录到CRS01。


有没有正确的方法来做到这一点？希望听到你们这些人是因为我真的被困了:(

非常感谢

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.The 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_CRSID,if it is a CRS Response, then it must have the Response_TO_CRSID - these ID might belong to CRS Open or CRS Response.

let say:
CRS01 = CRS Open
CRS03 = CRS Response -> RESPONSE_TO_CRSID: CRS01
CRS06 = CRS Response -> RESPONSE_TO_CRSID: CRS03 (but CRS03 response to CRS01)
CRS10 = CRS Response -> RESPONSE_TO_CRSID: 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.DocumentInCRS 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_CRSID 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

推荐答案

你好，beemomo。


一般方法是使用递归逻辑。

对于初学者，你可以阅读这个主题。


问候，

Fish

Hello, beemomo.

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

Regards,
Fish

Ok。


我将概述可能的解决方案的逻辑。

• 递归过程创建包含CRS和所有后续CRS的分隔列表。
• 递归程序正在应用于CRS表（如果需要，可以过滤）以获取分隔列表的数据集。
• 这样获得的recrdsset正在与Documents表连接（当然通过桥表）。列表中存在加入条件。
• 使用简单的VBA函数来检查条件。下面是一个如何完成的简单示例（数字列表）。
  
  
  元数据：
  
  
  [tblLists]
  
  txtList，Text（255）
  
  [tblNumbers]
  
  lngID，Long
  
  
  代码模块：
  

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:
  

展开 | 选择 | Wrap | 行号

实际上，中间分隔列表有点矫枉过正。


您可以在递归过程返回的条件上加入CRS表，该递归过程确定某个CRS是否是另一个CRS的后代。


Reg ards，

Fish

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

这篇关于如何查询可扩展的链接记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！