Membuat Recursive Query di Access

Recursive query atau query rekursif dalam SQL digunakan untuk membuat query secara bertingkat, biasanya hanya dengan satu tabel untuk dilipatgandakan dengan nama lain. Secara khusus, MS Access memang tidak menyediakan pernyataan SQL yang digunakan untuk query rekursif. Namun demikian, kita bisa membuat sendiri query rekursif itu.

Query rekursif sangat berguna bagi programmer untuk menjelaskan hirarki data, seperti hirarki organisasi (baik manajerial maupun departemental), kode produk, geografis, dan semua data yang mempunyai jenjang/tingkatan.

Di Access, query rekursif hanya disusun dengan menggunakan satu tabel yang kemudian diberi nama alias, sehingga tabel yang terlihat bisa menjadi lebih dari satu, seperti tampak pada gambar di bawah ini.

Query Design dari sebuah query rekursif

Pada gambar di atas, ada satu tabel asli yang bernama tblOrganisasi dan tiga tabel alias dari tblOrganisasi, yaitu tblOrganisasi_1, tblOrganisasi_2, dan tblOrganisasi_3. Join Properties antara satu tabel dengan tabel yang lain adalah sebagai berikut.

Join Properties pada query rekursif

Jadi, field Id pada tabel tblOrganisasi dihubungkan dengan field Parent pada tabel tblOrganisasi_1, field Id pada tabel tblOrganisasi_1 dihubungkan dengan field Parent pada tabel tblOrganisasi_2, demikian dan seterusnya.

Properti dan isi dari keempat tabel itu sama dan identik. Adapun tabelnya adalah sebagai berikut.

Design view dan datasheet view dari tabel TblOrganisasi

Bila digambarkan secara hirarkis, maka isi tabel tblOrganisasi pada gambar di atas adalah sebagai berikut. Gambar ini hanya memberikan penjelasan tentang organisasi secara ringkas saja.

Struktur atau hirarki organisasi yang ada di tabel tblOrganisasi

Ada 4 jenjang atau level yang terbentuk dari gambar hirarki di atas. Yang pertama adalah level Presiden Direktur. Selanjutnya, level 2 diisi oleh Direktur Pemasaran, Direktur Umum dan Keuangan, dan Direktur Operasional. Level 3 ada Manajer Keuangan, Manajer Akuntansi, Manajer Pendukung Teknis, dan Manajer Teknis. Terakhir adalah level 4 yang diisi oleh Manajer Jaringan. Keempat level ini mempengaruhi pembentukan query rekursif seperti yang ada di gambar 1 di atas. Dengan mengetahui kedalaman level ini, kita bisa menentukkan berapa banyak tabel yang dibutuhkan untuk membuat query rekursif. Bila ada empat level, maka kita juga perlu membuat empat tabel dalam query. Pada gambar 1 di atas, keempat tabel itu adalah tblOrganisasi, tblOrganisasi_1, tblOrganisasi_2, dan tblOrganisasi_3. Bila ada lima level, kita harus menambahkan satu tabel lagi. Bila hanya ada tiga level, maka kita harus mengurangi satu tabel. Demikian dan seterusnya.

Persoalan yang muncul saat membuat query rekursif di Access adalah kedalaman level yang dibutuhkan untuk membuat query rekursif ini. Access tidak memberikan peluang untuk membuat query secara dinamis, seperti halnya penggunaan bahasa SQL lainnya, misal Oracle, SQL Server, dan sebagainya.  Untuk menentukan kedalaman level yang dibutuhka, kita dapat menggunakan fungsi pada artikel yang berjudul Menggunakan rekursi untuk mengukur level kedalaman hirarki organisasi atau data. Jadi, silakan pelajari dan salin terlebih dahulu, fungsi yang ada di artikel itu.

Di Access, kita harus menuliskan kode VBA untuk membuat fungsi query rekursif, sehingga bisa digunakan secara dinamis dan sesuai dengan kebutuhan. Setelah mengetahui kedalaman level organisasi dengan menggunakan fungsi mengukurKedalamanHirarki, kita baru bisa membuat query rekursif yang dinamis dengan menggunakan fungsi berikut ini.

Function membuatQueryRekursif(intMaxLevel As Integer) As String
Dim rs As dao.Recordset
Dim strSql, strsql1, strsql2, strsql3 As String

    strsql1 = ""
    For x = 0 To intMaxLevel - 1
      If x > 0 Then
        strSql = "tblOrganisasi_" & x & "."
        If x = intMaxLevel - 1 Then strsql2 = "NamaDept " Else strsql2 = "NamaDept "
      Else
        strSql = "tblOrganisasi."
        strsql2 = "NamaDept"
      End If
      strsql1 = strsql1 & "iif(" & strSql & strsql2 & " Is Null,''," & strSql & strsql2
      If x = intMaxLevel - 1 Then strsql1 = strsql1 & ") AS Nama_Dept " Else strsql1 = strsql1 & " & Chr$(32) & Chr$(187) & Chr$(32)) & "
    Next x
    strsql1 = strSql & "Id, " & strsql1
    If intMaxLevel >= 3 Then
      strsql2 = ""
      For x = 0 To intMaxLevel - 2
        strsql2 = strsql2 & "("
      Next x
    End If
    If intMaxLevel <= 2 Then strsql2 = ""
    strsql3 = "SELECT " & strsql1 & " FROM " & strsql2 & "tblOrganisasi "
    For x = 0 To intMaxLevel - 1
      If x > 0 Then
        If x + 1 < intMaxLevel Then
          strSql = strSql & "RIGHT JOIN tblOrganisasi AS tblOrganisasi_" & x + 1 & " ON "
          strsql2 = "tblOrganisasi_" & x & ".Id=tblOrganisasi_" & x + 1 & ".Parent) "
          strSql = strSql & strsql2
        End If
      Else
        If intMaxLevel <= 2 Then
          strSql = "RIGHT JOIN tblOrganisasi AS tblOrganisasi_" & x + 1 & " ON tblOrganisasi.Id = tblOrganisasi_" & x + 1 & ".Parent "
        Else
          strSql = "RIGHT JOIN tblOrganisasi AS tblOrganisasi_" & x + 1 & " ON tblOrganisasi.Id = tblOrganisasi_" & x + 1 & ".Parent) "
        End If
      End If
    Next x
    membuatQueryRekursif = strsql3 & strSql
' Akhir dari fungsi membuatQueryRekursif
' Kode di bawah ini hanya digunakan untuk pengecekan saja, jadi bisa diabaikan dan dihapus, ini adalah awal penghapusan
  Set rs = CurrentDb.OpenRecordset(strsql3 & strSql)
  Debug.Print "Id      Nama Departemen"
  Debug.Print "-----------------------"
  Do While Not rs.EOF
    Debug.Print rs.Fields(0).Value & "       " & rs.Fields(1).Value
    rs.MoveNext
  Loop
  Debug.Print "-----------------------"
' ini adalah akhir penghapusan
End Function

Parameter yang digunakan dalam fungsi di atas adalah intMaxLevel . Paramater ini merupakan jumlah maksimum level kedalaman dari sebuah struktur organisasi atau hirarki data yang dihitung dengan menggunakan fungsi mengukurKedalamanHirarki. Jadi, untuk menjalankan fungsi membuatQueryRekursif, kita harus menghitung terlebih dahulu berapa level kedalaman struktur/hirarki organisasi atau data, hasilnya baru akan terlihat.

Bila gambar di atas mempunyai kedalaman empat level, maka dengan menggunakan fugsi membuatQueryRekursif, rincian dari masing-masing Id akan tampak seperti ini.

? membuatQueryRekursif(4)
Id      Nama Departemen
-----------------------
1       President Direktur
2       President Direktur » Direktur Pemasaran
3       President Direktur » Direktur Umum dan Keuangan
4       President Direktur » Direktur Operasional
5       President Direktur » Direktur Umum dan Keuangan » Manajer Keuangan
6       President Direktur » Direktur Umum dan Keuangan » Manajer Akuntansi
7       President Direktur » Direktur Operasional » Manajer Pendukung Teknis
8       President Direktur » Direktur Operasional » Manajer Teknis
9       President Direktur » Direktur Operasional » Manajer Pendukung Teknis » Manajer Jaringan
-----------------------
SELECT tblOrganisasi_3.Id, iif(tblOrganisasi.NamaDept Is Null,'',tblOrganisasi.NamaDept & Chr$(32) & Chr$(187) & Chr$(32)) & iif(tblOrganisasi_1.NamaDept  Is Null,'',tblOrganisasi_1.NamaDept  & Chr$(32) & Chr$(187) & Chr$(32)) & iif(tblOrganisasi_2.NamaDept  Is Null,'',tblOrganisasi_2.NamaDept  & Chr$(32) & Chr$(187) & Chr$(32)) & iif(tblOrganisasi_3.NamaDept  Is Null,'',tblOrganisasi_3.NamaDept ) AS Nama_Dept  FROM (((tblOrganisasi RIGHT JOIN tblOrganisasi AS tblOrganisasi_1 ON tblOrganisasi.Id = tblOrganisasi_1.Parent) RIGHT JOIN tblOrganisasi AS tblOrganisasi_2 ON tblOrganisasi_1.Id=tblOrganisasi_2.Parent) RIGHT JOIN tblOrganisasi AS tblOrganisasi_3 ON tblOrganisasi_2.Id=tblOrganisasi_3.Parent) 

Pernyataan SELECT query di bagian akhir merupakan hasil fungsi membuatQueryRekursif yang berupa string. Bagian atas merupakan hasil Debug.Print yang menjelaskan tabel tblOrganisasi yang telah diolah sedemikian rupa sehingga menjadi lebih mudah dipahami. Pada dasarnya, gambar struktur organisasi di atas sama dengan bentuk tabel hasil fungsi membuatQueryRekursif ini, garis komando yang menghubungkan satu bagian dengan bagian di bawahnya, sama dengan tanda » pada tabel.

Datasheet query dari query rekursif yang berasal dari tabel tblOrganisasi

Dalam bentuk datasheet view, hasil fungsi membuatQueryRekursif bila digunakan di SQL View pada Access akan tampak seperti di atas. Query ini, baik dalam bentuk query maupun string dapat digunakan sebagai sumber record source pada form atau report. Selain itu, hasil fungsi membuatQueryRekursif juga bisa digunakan sebagai row source sebuah combo box atau list box. Tampilan struktur organisasi seperti ini akan jauh lebih mudah dipahami daripada tampilan dalam bentuk tabel murni seperti yang ada di tblOrganisai.