MS Access Query检索分层记录 [英] MS Access Query to retrieve hierarchical records

问题描述

我想在Select Query中从MS Access数据库中检索分层记录。任何人都可以帮助我吗？

就像在Oracle中连接到先前一样，我想在MS Access中使用类似的东西。



我有列名和这样的记录：



FIRST_NAME LAST_NAME FAMILY_ID PARENT_ID路径

----------------- -------------------------------------------------- ----------

Quackmore Duck 201 200 / Quackmore

Thelma Duck 203 201 / Quackmore / Thelma

Huey Duck 205 203 / Quackmore / Thelma / Huey

Dewey Duck 206 203 / Quackmore / Thelma / Dewey

Louie Duck 207 203 / Quackmore / Thelma / Louie

Donald Duck 204 201 / Quackmore / Donald





输出应该是这样的：

< br $>


FIRST_NAME LAST_NAME FAMILY_ID PARENT_ID PARENT_姓名

----------------------------------------- -----------------------------------

Quackmore Duck 201 200
Thelma Duck 203 201 Quackmore

Huey Duck 205 203 Thelma

Dewey Duck 206 203 Thelma

Louie Duck 207 203 Thelma

唐老鸭204 201 Quackmore





层次结构深度可达到任何级别。

请有人帮我快点。



提前感谢你......请....请...

解决方案

Access不支持。 Access也不支持存储过程。



要在Access中执行此操作，您必须获取顶级记录，然后迭代这些记录并查询孩子们，迭代那些并查询孩子，...，直到你没有得到任何记录。

我不确定这可能是多么相关...

 私人  Sub  LoadData 
 ' 创建数据表以填充已编辑的结果 
  Dim  MyFixedTable  as   new  datatable（ 编辑结果）
 ' 指定搜索条件 
  Dim  colToSearch  as   String  =   LAST_NAME 
  Dim  searchFor  as   String  =   Duck 
  Dim  expressionString  as   String  = colToSearch&   LIKE'& searchFor&  ' 
 
 '  。选择 
  Dim  returnedDataRows（） as  DataRow = 
 DatabaseForm.MyDataSet.MyDataBase。 Select （expressionString）
 
 ' 如果找到结果，将结果修复为新表 
  if  returnedDataRows.Length>  0  
 对于 idx =  0   to  returnedDataRows.length  -   1  
  Dim  DataRow2Send  as  DataRow = returnedDataRows（idx）
 FillMyFixedTable（MyFixedTable，DataRow2Send）
 下一步 
 FillInParents（MyFixedTable）
 结束 如果 
 结束  Sub  
 
 私有  Sub  FillMyFixedTable（ ByRef  FixedTable  as  DataTable， byval  NewRow  as  DataRow）
  Dim  newDRow  as  FixedTable.NewRow 
 newDRow.Item（ 0  ）= NewRow.item（ 0 ）
 newDRow.Item（ 1 ）= NewRow.item （ 1 ）
 newDRow.Item（ 2 ）= NewRow.item（ 2 ）
 newDRow.Item（ 3 ）= NewRow.item（ 3 ）
 FixedTable.Rows.Add（newDRow）
 结束  Sub  
 
 私有  Sub  FillInPArents（ ByRef  FixedTable  as  DataTable）
  for  idx =  0    FixedTable.Rows.Count  -   1  
  dim  ParentTableRow  as  dataRow = FixedTable.Rows.Find（FixedTable.Rows（idx）。项目（ 3 ））
 如果 不 ParentTableRow   没有 
 FixedTable.Rows（idx）.item（ 4 ）= ParentTableRow.Item（ 0 ）
  else  
 FixedTable.Rows（idx）.item（ 4 ）=   
  end   if  
 下一步 
 结束  Sub  

I want to retrieve hierarchical records from MS Access database in a Select Query. Can anyone help me ?
Like CONNECT TO PRIOR IN Oracle , I want something like that in MS Access.

I have column names and records like this:

FIRST_NAME LAST_NAME FAMILY_ID PARENT_ID Path
-----------------------------------------------------------------------------
Quackmore Duck 201 200 /Quackmore
Thelma Duck 203 201 /Quackmore/Thelma
Huey Duck 205 203 /Quackmore/Thelma/Huey
Dewey Duck 206 203 /Quackmore/Thelma/Dewey
Louie Duck 207 203 /Quackmore/Thelma/Louie
Donald Duck 204 201 /Quackmore/Donald


The output should be like this:


FIRST_NAME LAST_NAME FAMILY_ID PARENT_ID PARENT_NAME
----------------------------------------------------------------------------
Quackmore Duck 201 200
Thelma Duck 203 201 Quackmore
Huey Duck 205 203 Thelma
Dewey Duck 206 203 Thelma
Louie Duck 207 203 Thelma
Donald Duck 204 201 Quackmore


And the hierarchical structure depth may be upto any level.
Please someone help me quickly.

Thanking you in advance... Please ....please...

解决方案

Not supported in Access. Access also doesn't support stored procedures.

To do this in Access, you'll have to get the top level records, then iterate over those and query for the children, iterate over those and query for children, ..., until you don't get any records back.

I'm not sure how relevant this might be...

Private Sub LoadData
   'create datatable to fill with edited results
   Dim MyFixedTable as new datatable("edited results")
   'specify search criteria
   Dim colToSearch as String = "LAST_NAME"
   Dim searchingFor as String = "Duck"
   Dim expressionString as String = colToSearch & " LIKE '" & searchingFor & "'"

   '.select
   Dim returnedDataRows() as DataRow = 
      DatabaseForm.MyDataSet.MyDataBase.Select(expressionString)

   'if results found, fix results into new table
   if returnedDataRows.Length > 0
      For idx = 0 to returnedDataRows.length - 1
          Dim DataRow2Send as DataRow = returnedDataRows(idx)
          FillMyFixedTable(MyFixedTable, DataRow2Send)
      Next
      FillInParents(MyFixedTable)
   End If
End Sub

Private Sub FillMyFixedTable(ByRef FixedTable as DataTable, byval NewRow as DataRow)
   Dim newDRow as FixedTable.NewRow
   newDRow.Item(0) = NewRow.item(0)
   newDRow.Item(1) = NewRow.item(1)
   newDRow.Item(2) = NewRow.item(2)
   newDRow.Item(3) = NewRow.item(3)
   FixedTable.Rows.Add(newDRow)
End Sub

Private Sub FillInPArents(ByRef FixedTable as DataTable)
   for idx = 0 to FixedTable.Rows.Count - 1
       dim ParentTableRow as dataRow = FixedTable.Rows.Find(FixedTable.Rows(idx).Item(3))
       if not ParentTableRow is nothing
           FixedTable.Rows(idx).item(4) = ParentTableRow.Item(0)
       else
           FixedTable.Rows(idx).item(4) = "" 
       end if
   Next
End Sub

这篇关于MS Access Query检索分层记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！