Можно ли создать рекурсивный запрос в Access?

Ответ 1

Нет, это не так. Рекурсивные запросы поддерживаются в SQL Server после SServer 2005, но не в Access.

Если вы заранее знаете количество рычагов, вы можете написать qry, но это не будет рекурсивным.

В SQL Server для этого используется CTE (расширение SQL): см. http://blog.crowe.co.nz/archive/2007/09/06/Microsoft-SQL-Server-2005---CTE-Example-of-a-simple.aspx

Обычный SQL, однако, не поддерживает рекурсию.

Ответ 2

В Access можно создать запрос для поиска корня вашего заданного задания. Не забывайте о мощности функций VBA. Вы можете создать рекурсивную функцию в модуле VBA и использовать ее результат как поле вывода в вашем запросе.

Пример:

Public Function JobRoot(Id As Long, ParentId As Long) As Long
   If ParentId = 0 Then
      JobRoot = Id
      Exit Function
   End If

   Dim Rst As New ADODB.Recordset
   Dim sql As String
   sql = "SELECT Id, ParentID FROM JobTable WHERE Id = " & ParentId & ";"
   Rst.Open sql, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

   If Rst.Fields("ParentID") = 0 Then
      JobRoot = Rst.Fields("Id")
   Else
      JobRoot = JobRoot(Id, Rst.Fields("ParentID"))    ' Recursive.
   End If

   Rst.Close
   Set Rst = Nothing
End Function

Вы можете вызвать эту рекурсивную функцию из своего запроса, используя построитель запросов или просто введя имя функции с аргументами в поле запроса.

Он даст корень.

(Я узнаю, что OP уже год, но я вынужден отвечать, когда все говорят, что невозможно).

Ответ 3

Вы не можете рекурсивно запрашивать.

Вы можете либо сделать какое-то произвольное количество левых объединений, но только сможете подняться на столько уровней, сколько у вас есть.

Или вы можете использовать Celko "Вложенная модель набора" для извлечения всех родителей. Это потребует изменения вашей структуры таблицы, что упростит вставки и обновления.

Ответ 4

ОК, так что здесь РЕАЛЬНАЯ сделка. Во-первых, какова целевая аудитория вашего запроса.. форма? отчет? Функция/прок?

Форма: нужны обновления? используйте управление древовидной структурой, в то время как неуклюже оно будет работать хорошо. Отчет: в открытом событии используйте форму параметра, чтобы установить уровень "Boss Job", затем обработайте рекурсию в vba и заполните набор записей данными в желаемом порядке. установите набор записей отчетов на этот заполненный набор записей и обработайте отчет.  Функция/процедура: работает практически так же, как и загрузка данных, описанная в отчете выше. С помощью кода обработайте необходимую "прогулку по дереву" и сохраните результирующий набор в нужном порядке в наборе записей и при необходимости обработайте его.

Ответ 5

Это невозможно сделать с использованием чистого SQL в Access, но немного VBA проходит долгий путь.

Добавьте ссылку на Время выполнения сценариев Microsoft (Инструменты → Ссылки...).

Это предполагает, что идентификатор уникален и что нет циклов: например. Родитель - B, но родитель B - A.

Dim dict As Scripting.Dictionary

Function JobRoot(ID As Long) As Long
    If dict Is Nothing Then
        Set dict = New Scripting.Dictionary
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("SELECT ID, ParentID FROM Job", dbOpenForwardOnly, dbReadOnly)
        Do Until rs.EOF
            dict(rs!ID) = rs!ParentID
            rs.MoveNext
        Loop
        Set rs = Nothing

        Dim key As Variant
        For Each key In dict.Keys
            Dim possibleRoot As Integer
            possibleRoot = dict(key)
            Do While dict(possibleRoot) <> 0
                possibleRoot = dict(possibleRoot)
            Loop
            dict(key) = possibleRoot
        Next
    End If
    JobRoot = dict(ID)
End Function

Sub Reset() 'This needs to be called to refresh the data
    Set dict = Nothing
End Sub

Ответ 6

Вклад Зева дал мне много вдохновения и обучения. Однако необходимо внести некоторые изменения в код. Обратите внимание, что моя таблица называется "tblTree".

Dim dict As Scripting.Dictionary

Function TreeRoot(ID As Long) As Long
    If dict Is Nothing Then
        Set dict = New Scripting.Dictionary ' Requires Microsoft Scripting Runtime
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("tblTree", dbOpenForwardOnly, dbReadOnly)
        Do Until rs.EOF
            dict.Add (rs!ID), (rs!ParentID)
            rs.MoveNext
        Loop
        Set rs = Nothing
    End If

    TreeRoot = ID

    Do While dict(TreeRoot) <> 0    ' Note: short version for dict.item(TreeRoot)
        TreeRoot = dict(TreeRoot)
    Loop
End Function

И есть еще одна полезная функция в том же контексте. "ChildHasParent" возвращает true, если дочерний элемент соответствует указанному ParentID на любом уровне вложенности.

Function ChildHasParent(ID As Long, ParentID As Long) As Boolean
    If dict Is Nothing Then
        Set dict = New Scripting.Dictionary ' Requires Microsoft Scripting Runtime
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("tblTree", dbOpenForwardOnly, dbReadOnly)
        Do Until rs.EOF
            dict.Add (rs!ID), (rs!ParentID)
            rs.MoveNext
        Loop
        Set rs = Nothing
    End If

    ChildHasParent = False

    Do While dict(ID) <> 0    ' Note: short version for dict.item(TreeRoot)
        ID = dict(ID)
        If ID = ParentID Then
            ChildHasParent = True
            Exit Do
        End If
    Loop
End Function