Skip to main content

Membuat Recursive Query di Access

Labels:
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.
view plainprint?
  1. Function membuatQueryRekursif(intMaxLevel As Integer) As String  
  2. Dim rs As dao.Recordset  
  3. Dim strSql, strsql1, strsql2, strsql3 As String  
  4.   
  5.     strsql1 = ""  
  6.     For x = 0 To intMaxLevel - 1  
  7.       If x > 0 Then  
  8.         strSql = "tblOrganisasi_" & x & "."  
  9.         If x = intMaxLevel - 1 Then strsql2 = "NamaDept " Else strsql2 = "NamaDept "  
  10.       Else  
  11.         strSql = "tblOrganisasi."  
  12.         strsql2 = "NamaDept"  
  13.       End If  
  14.       strsql1 = strsql1 & "iif(" & strSql & strsql2 & " Is Null,''," & strSql & strsql2  
  15.       If x = intMaxLevel - 1 Then strsql1 = strsql1 & ") AS Nama_Dept " Else strsql1 = strsql1 & " & Chr$(32) & Chr$(187) & Chr$(32)) & "  
  16.     Next x  
  17.     strsql1 = strSql & "Id, " & strsql1  
  18.     If intMaxLevel >= 3 Then  
  19.       strsql2 = ""  
  20.       For x = 0 To intMaxLevel - 2  
  21.         strsql2 = strsql2 & "("  
  22.       Next x  
  23.     End If  
  24.     If intMaxLevel <= 2 Then strsql2 = ""  
  25.     strsql3 = "SELECT " & strsql1 & " FROM " & strsql2 & "tblOrganisasi "  
  26.     For x = 0 To intMaxLevel - 1  
  27.       If x > 0 Then  
  28.         If x + 1 < intMaxLevel Then  
  29.           strSql = strSql & "RIGHT JOIN tblOrganisasi AS tblOrganisasi_" & x + 1 & " ON "  
  30.           strsql2 = "tblOrganisasi_" & x & ".Id=tblOrganisasi_" & x + 1 & ".Parent) "  
  31.           strSql = strSql & strsql2  
  32.         End If  
  33.       Else  
  34.         If intMaxLevel <= 2 Then  
  35.           strSql = "RIGHT JOIN tblOrganisasi AS tblOrganisasi_" & x + 1 & " ON tblOrganisasi.Id = tblOrganisasi_" & x + 1 & ".Parent "  
  36.         Else  
  37.           strSql = "RIGHT JOIN tblOrganisasi AS tblOrganisasi_" & x + 1 & " ON tblOrganisasi.Id = tblOrganisasi_" & x + 1 & ".Parent) "  
  38.         End If  
  39.       End If  
  40.     Next x  
  41.     membuatQueryRekursif = strsql3 & strSql  
  42. ' Akhir dari fungsi membuatQueryRekursif  
  43. ' Kode di bawah ini hanya digunakan untuk pengecekan saja, jadi bisa diabaikan dan dihapus, ini adalah awal penghapusan  
  44.   Set rs = CurrentDb.OpenRecordset(strsql3 & strSql)  
  45.   Debug.Print "Id      Nama Departemen"  
  46.   Debug.Print "-----------------------"  
  47.   Do While Not rs.EOF  
  48.     Debug.Print rs.Fields(0).Value & "       " & rs.Fields(1).Value  
  49.     rs.MoveNext  
  50.   Loop  
  51.   Debug.Print "-----------------------"  
  52. ' ini adalah akhir penghapusan  
  53. 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.
view plainprint?
  1. ? membuatQueryRekursif(4)  
  2. Id      Nama Departemen  
  3. -----------------------  
  4. 1       President Direktur  
  5. 2       President Direktur » Direktur Pemasaran  
  6. 3       President Direktur » Direktur Umum dan Keuangan  
  7. 4       President Direktur » Direktur Operasional  
  8. 5       President Direktur » Direktur Umum dan Keuangan » Manajer Keuangan  
  9. 6       President Direktur » Direktur Umum dan Keuangan » Manajer Akuntansi  
  10. 7       President Direktur » Direktur Operasional » Manajer Pendukung Teknis  
  11. 8       President Direktur » Direktur Operasional » Manajer Teknis  
  12. 9       President Direktur » Direktur Operasional » Manajer Pendukung Teknis » Manajer Jaringan  
  13. -----------------------  
  14. 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.

Labels

Comments

  1. Yanto Subroto22 October, 2020 01:24

    Saya kurang paham maksudmu. Tapi, kalau di buku yg kamu beli, ada fungsi query dng parameter, namanya qryExtendedPropertyTambahkan. Fungsi itu ada di halaman nomor 72. Mungkin itu bisa membantu.

    ReplyDelete
  2. Yanto Subroto22 October, 2020 19:07

    Di buku yg kamu beli itu ada form frmBlobList, sama nggak dng model parameter yg kamu inginkan. Di form itu, kita memasukkan pilihan, terus oleh access diquerykan dan sql server menerima dan mengirimkan data hasil query ke access.

    Sama nggak caranya?

    ReplyDelete

Post a Comment

Posting Terpopuler

Cara Mengatur dan Menggunakan ODBC untuk Mengakses Data Eksternal

4 comments
Read more

Fungsi Untuk Membuka Database di Access VBA

4 comments
Read more

Format Untuk Field Dengan Tipe Data Number dan Currency di MS Access

1 comment
Read more