Terima kasih Mr.Kid atas pencerahannya. Kelihatannya tebelnya dibuat pake Excel....
Saya akan coba googling lagi file aslinya. Terima kasih. Salam, HK Sent from Samsung Mobile <div>-------- Original message --------</div><div>From: "'Mr. Kid' [email protected] [belajar-excel]" <[email protected]> </div><div>Date:05/10/2015 02:34 (GMT+07:00) </div><div>To: BeExcel <[email protected]> </div><div>Subject: Re: Fw: [belajar-excel] Perintah SQL untuk menggabungkan data sekaligus menjumlahkan </div><div> </div>wkwkkwk... Pak HK, Hukum Dasar : Setiap kolom pasti hanya punya 1 datatype. Antar kolom dalam satu tabel (misal N kolom) bisa jadi kolom1 berdatatype yang berbeda dengan kolom2 dst... tetapi bisa juga sama Apakah hal berikut melanggar hukum dasar di atas ? Mengapa dengan Account data type yang "tidak sama" kolom Debits dan Credits masih bisa "dijumlahkan" (SUM) dengan "GROUP BY Account, Description". Jadi, Setiap KOLOM yang digunakan dalam suatu query PASTI memiliki 1 datatype saja. Jadi, isinya (field) di kolom tersebut mau tidak mau harus yang berdatatype sama dan sesuai dengan datatype kolom tersebut. Kolom ke-1 bisa jadi tidak sama datatype-nya dengan kolom ke-2. Tapi kolom ke-2 PASTI memiliki 1 datatype saja. Jadi, isi data di kolom ke-2 tersebut mau tidak mau harus yang berdatatype sama dan sesuai dengan datatype kolom ke-2. Contoh : tabel A berisi kolom Account dan Nilai Account Nilai ABC 100 100 500 D1Y 0 (lihat, Account 100 adalah rata kiri alias bertipe text) TIDAK MUNGKIN terjadi : Account Nilai ABC 100 -> Nilai 100 rata kanan alias bertipe numerik ? TIDAK MUNGKIN 100 ABC D1Y XYZ (lihat, Account 100 adalah rata kiri alias bertipe text) yang mungkin terjadi adalah : TIDAK MUNGKIN terjadi : Account Nilai ABC 100 -> menjadi bertipe text (lihat, rata kiri) 100 ABC D1Y XYZ Tabel berkaidah database tidak peduli nilai datanya apa.... Tabel berkaidah database hanya peduli DATATYPE si nilai data sesuai dengan datatype si kolom atau tidak. Contoh : > Tabel B memiliki 2 kolom, Nama_Negara dan Nilai Nama_Negara Nilai Indonesia 100 Jerman 200 > Tabel C memiliki 2 kolom, Ket_Invoice dan Qty_Penjualan_in_pcs Ket_Invoice Qty_Penjualan_in_pcs Invoice no Y12RT 1 Lunas Inv K98YT 12 Saya bisa membuat tabel D berisi 2 kolom Kolom1 dan Kolom2 yang menampung tabel B dan C Kolom1 Kolom2 Indonesia 100 Jerman 200 Invoice no Y12RT 1 Lunas Inv K98YT 12 Tabel berkaidah database tidak peduli, apakah data itu sebenarnya data nilai per negara atau data informasi qty per invoice Asal datatypenya sama (Kolom1 bertipe text, Kolom2 bertipe numerik) maka bisa masuk. 2015-10-04 22:50 GMT+07:00 hendrik karnadi [email protected] [belajar-excel] <[email protected]>: Terima kasih Mr. Kid. Maaf Mr. Kid telah merepotkan..... Ternyata hanya Account Data Type dari salah satu file sumber (Expenditures) yang berupa Text, lainnya Numerik. Setelah Account data type pada semua file sumber saya jadikan text memang macronya bisa jalan. Masih ada satu pertanyaan yang masih belum bisa saya jawab... Mengapa dengan Account data type yang "tidak sama" kolom Debits dan Credits masih bisa "dijumlahkan" (SUM) dengan "GROUP BY Account, Description". Terima kasih. Salam, HK From: "'Mr. Kid' [email protected] [belajar-excel]" <[email protected]> To: BeExcel <[email protected]> Sent: Sunday, 4 October 2015, 21:25 Subject: Re: Fw: [belajar-excel] Perintah SQL untuk menggabungkan data sekaligus menjumlahkan Data type yang digunakan kolom Account apa ? Kalau bukan string (char atau varchar), maka ndak perlu pakai ", kalau bertipe datetime, berarti bisa jadi perlu #, dsb. Silakan disabarkan untuk bisa benar-benar memahami datatype dan seluruh karakteristik setiap datatype. Regard, Kid 2015-10-04 20:51 GMT+07:00 hendrik karnadi [email protected] [belajar-excel] <[email protected]>: Terima kasih Mr. Kid. Tadi penulisannya kurang "s", pada kolom Debit dan Kredit.. seharusnya sSQL = sSQL & "Select Account, Description, Debits, Credits From " & oLr.Range(1) & vbCr & "WHERE Account = ""240""" Namun sekarang muncul error message "Data type mismatch in criteria expression" Mohon petunjuk lebih lanjut. Terima kasih. Salam, HK From: "'Mr. Kid' [email protected] [belajar-excel]" <[email protected]> To: BeExcel <[email protected]> Sent: Sunday, 4 October 2015, 16:06 Subject: Re: Fw: [belajar-excel] Perintah SQL untuk menggabungkan data sekaligus menjumlahkan Pak HK, Error description berbunyi No Value given for one or more required parameters biasanya karena ada nama kolom yang disebut di bagian select dalam query yang tidak dapat ditemukan dan dianggap sebagai sebuah variabel tertentu yang akan diberi suatu nilai konstanta. Coba periksa kembali nama-nama kolom bagian select dan cocokkan dengan dataset sumbernya. Regard, Kid 2015-10-04 7:14 GMT+07:00 hendrik karnadi [email protected] [belajar-excel] <[email protected]>: Mr. Kid, Mau nanya sedikit lagi tentang penulisan datatype string di Macro. Masih dengan contoh kasus yang sama, untuk merekap Debits dan Kredits. Bagaimana penulisan macronya bila yang ingin ditampilkan adalah details dari Account tertentu ? Saya coba seperti ini (dengan dua apostroph mengapit Account yang menjadi Criteria), sSQL = sSQL & "Select Account, Description, Debit, Credit From " & oLr.Range(1) & vbCr & "WHERE Account = ""240""" Tapi ketika dijalankan ada error mesaage "No Value given for one or more required parameters" Untuk lebih jelasnya saya lampirkan kembali .zip filenya (Consolidate(Detail_ERR) beserta file2 sumbernya), yang macronya telah saya ubah seperti di atas. Mohon dapat diberikan petunjuk lebih lanjut. Terima kasih. Salam, HK ----- Forwarded Message ----- From: "'Mr. Kid' [email protected] [belajar-excel]" <[email protected]> To: BeExcel <[email protected]> Sent: Thursday, 1 October 2015, 11:19 Subject: Re: [belajar-excel] Perintah SQL untuk menggabungkan data sekaligus menjumlahkan loh... kan tentang datatype string ada disini. Mulai dari cara penyimpanan data teks (string) ke sebuah variabel, menggabungkan (concatenate) banyak data teks (string), menggabungkan (concatenate) variabel bertipe string dengan suatu data teks, dan berbagai contoh lainnya. Termasuk untuk datatype lainnya. sedangkan untuk penambahan Description, adalah sekedar mengikuti logika. Coba deh dibayangkan, ada data produk dan tahun pembuatannya, beserta nilai penjualannya. Produk Th_Prod Nilai A 1990 100 A 1990 500 A 2000 300 B 1990 250 B 2015 350 B 2015 150 C 2014 30 C 2014 10 1. Bagaimana hasil total nilai per produk ? 2. Bagaimana hasil total nilai per tahun pembuatan ? 3. Bagaimana hasil total nilai per produk per tahun pembuatan ? 4. Coba buat tabel outputnya secara manual, apa beda dari ke-3 output di atas ? 5. Bisakah menampilkan total nilai per produk soal nomor 1 di atas tapi disertai informasi tahun pembuatannya yang per record output nomor 1 hanya memuat 1 nilai tahun pembuatan tertentu ? 6. Kenapa pada kasus Anda ditambahkan Description ? dari alur logika di atas, maka muncullah struktur query group by yang dengan tegas menyatakan bahwa bagian group by diisi dengan your_key_columns_without_columns_aliasses dan bukan your_tables_or_views_keys Regard, Kid 2015-10-01 11:02 GMT+07:00 hendrik karnadi [email protected] [belajar-excel] <[email protected]>: Terima kasih Mr. Kid. Berhasil.... Jadi harus ada tambahan tanda &, ""(string) dan Description (untuk grupnya). Salam. HK From: "'Mr. Kid' [email protected] [belajar-excel]" <[email protected]> To: BeExcel <[email protected]> Sent: Thursday, 1 October 2015, 10:42 Subject: Re: [belajar-excel] Perintah SQL untuk menggabungkan data sekaligus menjumlahkan Pak HK, sSQL = sSQL & "Select Account, Description, Sum(Debits) as Total_Debit, Sum(Credits) as Total_Credit From " & oLr.Range(1) & vbCr & "GROUP BY Account,Description" Regard, Kid 2015-10-01 10:11 GMT+07:00 hendrik karnadi [email protected] [belajar-excel] <[email protected]>: Mr. Kid, Terima kasih atas penjelasannya. Saya coba ganti perintah : sSQL = sSQL & "Select * From " & oLr.Range(1) dengan sSQL = sSQL & "Select Account, Description, Sum(Debits) as Total_Debit, Sum(Credits) as Total_Credit From " & oLr.Range(1) & vbCr GROUP BY Account Ketika macro dijalankan keluar message "Syntax Error" pada huruf yang berwarna merah. Dimana salahnya ya ? Terima kasih. Salam, HK From: "'Mr. Kid' [email protected] [belajar-excel]" <[email protected]> To: BeExcel <[email protected]> Sent: Thursday, 1 October 2015, 8:58 Subject: Re: [belajar-excel] Perintah SQL untuk menggabungkan data sekaligus menjumlahkan Hai Pak HK, 1. Query dasar agregat yang memiliki keys (agregat per anything) tanpa filter terhadap hasil agregat SELECT your_key_columns, your_aggregat_ecpression FROM your_source GROUP BY your_key_columns_without_columns_aliasses contoh : total penjualan per tahun (berdasar tanggal invoice) per produk SELECT produk_id, year(tgl_invoice) as tahun, sum(penjualan) as total_jual FROM dataINV GROUP BY produk_id, year(tgl_invoice) --> tanpa aliasses 2. Query dasar UNION [ALL] Query1 UNION [ALL] Query2 UNION [ALL] . . UNION [ALL] QueryN dengan setiap Query# berupa select query yang memiliki susunan sesuai kebutuhan. Urutan kolom dan tipe datanya dari setiap Query# adalah tetap, dengan definisi kolom di Query1 yang menjadi nama kolom output 3. Susunan select query dasar SELECT your_columns [, your_aggregate_expressions] FROM your_source [ [JOIN your_join1 ON your_join1_relations] . . [JOIN your_joinN ON your_joinN_relations] ] [ WHERE your_filter_expressions ] [ GROUP BY your_columns_except_aggregate_expressions_without_columns_aliasses [HAVING your_aggregate_filter_expressions] --> berisi filter terhadap kolom-kolom agregat, dan bukan terhadap key columns ] [ UNION [ALL] your_another_query2 . . UNION [ALL] your_another_queryN ] [ ORDER BY your_sort_expressions ] gunakan klausa ORDER BY secara bijak dan usahakan dihindari kecuali penting sekali diseakhir mungkin suatu proses, karena klausa ORDER BY termasuk computer resources eater 4. source dari query lain (subquery di bagian FROM) SELECT your_dataset_alias#.your_columns FROM ( queryS ) as your_dataset_alias1 [another query clauses] queryS bisa berisi select query dasar seperti di-nomor 3 tanpa klausa ORDER BY subquery suatu query seperti queryS juga bisa diletakkan pada bagian sumber data yang di-JOIN setiap Query# dalam UNION [ALL] bisa berisi subquery suatu query seperti queryS ----------------------- Prinsip dasar ber-query : 1. ketahui sumber data yang akan diproses 2. pahami alur komputasi yang dibutuhkan 3. susun query berdasar 1 dan 2 ----------------------- Jadi, menggabungkan data hasil agregat adalah hal yang berbeda dengan mengagregat hasil gabungan data > menggabungkan data hasil agregat SELECT your_key_columns, your_aggregate_expressions FROM your_source1 GROUP BY your_key_columns_without_columns_aliasses UNION [ALL] SELECT your_key_columns, your_aggregate_expressions FROM your_source2 GROUP BY your_key_columns_without_columns_aliasses . . UNION [ALL] SELECT your_key_columns, your_aggregate_expressions FROM your_sourceN GROUP BY your_key_columns_without_columns_aliasses hasilnya bisa jadi ada your_columns yang sama dengan nilai your_aggregate apa saja jika dengan [ALL] atau ada hasil aggregate yang hilang jika tanpa [ALL] > mengagregat hasil gabungan data SELECT your_key_columns, your_aggregate_expressions FROM ( SELECT your_key_columns FROM your_source1 UNION [ALL] SELECT your_key_columns FROM your_source2 . . UNION [ALL] SELECT your_key_columns FROM your_sourceN ) as your_dataset_alias GROUP BY your_key_columns_without_columns_aliasses Regard, Kid On Wed, Sep 30, 2015 at 5:36 PM, hendrik karnadi [email protected] [belajar-excel] <[email protected]> wrote: Dear Be-Excelers, Berikut adalah perintah Macro yang saya dapat dari internet (file terlampir) untuk menggabungkan data (non unique) dari beberapa file yang ada dalam satu folder (Path), Sub Consolidate() Dim sSQL As String 'SQL String Dim oLr As ListRow 'Worksheets Row Dim cn As Object 'Connection Dim rs As Object 'Recordset ' Create SQL For Each oLr In Sheet1.ListObjects("Worksheets").ListRows If sSQL <> "" Then sSQL = sSQL & vbCr & "Union All" & vbCr 'tidak unik sSQL = sSQL & "Select * From " & oLr.Range(1) Next sSQL = Replace(sSQL, "<Path>", ThisWorkbook.Path) ' Create Connection Objects Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & ThisWorkbook.FullName & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" rs.Open sSQL, cn Debug.Print sSQL If Sheet1.ListObjects.Count > 1 Then Sheet1.ListObjects(2).Delete Sheet1.ListObjects.Add( _ SourceType:=xlSrcQuery, _ Source:=rs, _ Destination:=Sheet1.Range("C6")).QueryTable.Refresh rs.Close cn.Close Set rs = Nothing Set cn = Nothing End Sub Yang ingin saya tanyakan adalah bagaimana bunyi perintah SQL nya (warna biru) jika yang ingin ditampilkan adalah total per no rek (bukan detailnya). Terima kasih. Salam, HK
