È ansible creare una query ricorsiva in Access?

Ho un tavolo di job

 Id ParentID jobName jobStatus 

La radice ParentID è 0.

È ansible in Access creare una query per trovare una radice per un dato job ? Il database è MDB senza tabelle collegate. La versione di Access è del 2003. Un job può essere di diversi livelli in profondità.

No, non lo è. Le query ricorsive sono supportate in SQL Server dopo SServer 2005, ma non in Access.

Se conosci in anticipo il numero di leve, potresti scrivere un qry, ma non sarebbe ricorsivo.

In SQL Server, viene utilizzato CTE (un’estensione SQL) per questo: vedere http://blog.crowe.co.nz/archive/2007/09/06/Microsoft-SQL-Server-2005—CTE-Example- di-un-simple.aspx

SQL regolare tuttavia non ha il supporto di ricorsività.

In Access è ansible creare una query per trovare la radice del proprio lavoro. Non dimenticare la potenza delle funzioni VBA. È ansible creare una funzione ricorsiva in un modulo VBA e utilizzarne il risultato come campo di output nella query.

Esempio:

 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 

È ansible chiamare questa funzione ricorsiva dalla query utilizzando il generatore di query o semplicemente digitando il nome della funzione con argomenti in un campo di query.

Produrrà la radice.

(Riconosco che l’OP ha ora un anno, ma sono costretto a rispondere quando tutti dicono che l’imansible è ansible).

Non è ansible eseguire query in modo ricorsivo.

Puoi anche fare un numero arbitrario di join di sinistra, ma sarai in grado di salire su tutti i livelli quanti ne hai aggiunti.

Oppure puoi utilizzare il “Modello di set nidificato” di Celko per recuperare tutti i genitori. Ciò richiederà la modifica della struttura della tabella, in modo da rendere più complicati gli inserti e gli aggiornamenti.

Questo non può essere fatto usando puro SQL in Access, ma un piccolo VBA va molto lontano.

Aggiungi un riferimento a Microsoft Scripting Runtime ( Strumenti -> Riferimenti … ).

Ciò presuppone che l’ID sia univoco e che non ci siano cicli: ad es. Il genitore di A è B, ma il genitore di 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 

OK, ecco il vero affare. In primo luogo, qual è il pubblico di destinazione per la tua query .. un modulo? rapporto? Funzione / proc?

Modulo: hai bisogno di aggiornamenti? usa il controllo di treeview mentre goffamente funzionerà bene. Report: nell’evento open utilizzare un modulo di parametri per impostare il livello “Boss Job”, quindi gestire la ricorsione in vba e riempire un recordset con i dati nell’ordine desiderato . imposta il recordset dei report su questo recordset pieno ed elabora il report. Funzione / Procedura: funziona più o meno come un carico di dati descritto nel rapporto sopra. Tramite il codice, gestire il “tree walking” necessario e memorizzare il set di risultati nell’ordine desiderato in un recordset ed elaborarlo secondo necessità.

Il contributo di Zev mi ha dato una grande quantità di ispirazione e apprendimento. Tuttavia, è necessario apportare alcune modifiche al codice. Tieni presente che la mia tabella si chiama “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 

E c’è un’altra funzione utile nello stesso contesto. “ChildHasParent” restituisce true, se il bambino corrisponde al ParentID fornito in qualsiasi livello di nidificazione.

 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