Senin, 05 Oktober 2015

[belajar-excel] Digest Number 3588

6 Messages

Digest #3588

Messages

Sun Oct 4, 2015 6:51 am (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

Terima kasih Mr. Kid.Tadi penulisannya kurang "s", pada kolom Debit dan Kredit..seharusnyasSQL = 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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  Terima kasih Mr. Kid.
Berhasil....Jadi harus ada tambahan tanda &, ""(string) dan Description (untuk grupnya).
Salam.
HK
From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  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) & vbCrGROUP BY Account
Ketika macro dijalankan keluar message "Syntax Error" pada huruf yang berwarna merah. Dimana salahnya ya ?
Terima kasih.
Salam,HK

From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 dataSELECT your_key_columns, your_aggregate_expressionsFROM
       (

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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> 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


#yiv7450433646 #yiv7450433646 -- #yiv7450433646ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7450433646 #yiv7450433646ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7450433646 #yiv7450433646ygrp-mkp #yiv7450433646hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv7450433646 #yiv7450433646ygrp-mkp #yiv7450433646ads {margin-bottom:10px;}#yiv7450433646 #yiv7450433646ygrp-mkp .yiv7450433646ad {padding:0 0;}#yiv7450433646 #yiv7450433646ygrp-mkp .yiv7450433646ad p {margin:0;}#yiv7450433646 #yiv7450433646ygrp-mkp .yiv7450433646ad a {color:#0000ff;text-decoration:none;}#yiv7450433646 #yiv7450433646ygrp-sponsor #yiv7450433646ygrp-lc {font-family:Arial;}#yiv7450433646 #yiv7450433646ygrp-sponsor #yiv7450433646ygrp-lc #yiv7450433646hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7450433646 #yiv7450433646ygrp-sponsor #yiv7450433646ygrp-lc .yiv7450433646ad {margin-bottom:10px;padding:0 0;}#yiv7450433646 #yiv7450433646actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7450433646 #yiv7450433646activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7450433646 #yiv7450433646activity span {font-weight:700;}#yiv7450433646 #yiv7450433646activity span:first-child {text-transform:uppercase;}#yiv7450433646 #yiv7450433646activity span a {color:#5085b6;text-decoration:none;}#yiv7450433646 #yiv7450433646activity span span {color:#ff7900;}#yiv7450433646 #yiv7450433646activity span .yiv7450433646underline {text-decoration:underline;}#yiv7450433646 .yiv7450433646attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv7450433646 .yiv7450433646attach div a {text-decoration:none;}#yiv7450433646 .yiv7450433646attach img {border:none;padding-right:5px;}#yiv7450433646 .yiv7450433646attach label {display:block;margin-bottom:5px;}#yiv7450433646 .yiv7450433646attach label a {text-decoration:none;}#yiv7450433646 blockquote {margin:0 0 0 4px;}#yiv7450433646 .yiv7450433646bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv7450433646 .yiv7450433646bold a {text-decoration:none;}#yiv7450433646 dd.yiv7450433646last p a {font-family:Verdana;font-weight:700;}#yiv7450433646 dd.yiv7450433646last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7450433646 dd.yiv7450433646last p span.yiv7450433646yshortcuts {margin-right:0;}#yiv7450433646 div.yiv7450433646attach-table div div a {text-decoration:none;}#yiv7450433646 div.yiv7450433646attach-table {width:400px;}#yiv7450433646 div.yiv7450433646file-title a, #yiv7450433646 div.yiv7450433646file-title a:active, #yiv7450433646 div.yiv7450433646file-title a:hover, #yiv7450433646 div.yiv7450433646file-title a:visited {text-decoration:none;}#yiv7450433646 div.yiv7450433646photo-title a, #yiv7450433646 div.yiv7450433646photo-title a:active, #yiv7450433646 div.yiv7450433646photo-title a:hover, #yiv7450433646 div.yiv7450433646photo-title a:visited {text-decoration:none;}#yiv7450433646 div#yiv7450433646ygrp-mlmsg #yiv7450433646ygrp-msg p a span.yiv7450433646yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7450433646 .yiv7450433646green {color:#628c2a;}#yiv7450433646 .yiv7450433646MsoNormal {margin:0 0 0 0;}#yiv7450433646 o {font-size:0;}#yiv7450433646 #yiv7450433646photos div {float:left;width:72px;}#yiv7450433646 #yiv7450433646photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv7450433646 #yiv7450433646photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7450433646 #yiv7450433646reco-category {font-size:77%;}#yiv7450433646 #yiv7450433646reco-desc {font-size:77%;}#yiv7450433646 .yiv7450433646replbq {margin:4px;}#yiv7450433646 #yiv7450433646ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv7450433646 #yiv7450433646ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7450433646 #yiv7450433646ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7450433646 #yiv7450433646ygrp-mlmsg select, #yiv7450433646 input, #yiv7450433646 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv7450433646 #yiv7450433646ygrp-mlmsg pre, #yiv7450433646 code {font:115% monospace;}#yiv7450433646 #yiv7450433646ygrp-mlmsg * {line-height:1.22em;}#yiv7450433646 #yiv7450433646ygrp-mlmsg #yiv7450433646logo {padding-bottom:10px;}#yiv7450433646 #yiv7450433646ygrp-msg p a {font-family:Verdana;}#yiv7450433646 #yiv7450433646ygrp-msg p#yiv7450433646attach-count span {color:#1E66AE;font-weight:700;}#yiv7450433646 #yiv7450433646ygrp-reco #yiv7450433646reco-head {color:#ff7900;font-weight:700;}#yiv7450433646 #yiv7450433646ygrp-reco {margin-bottom:20px;padding:0px;}#yiv7450433646 #yiv7450433646ygrp-sponsor #yiv7450433646ov li a {font-size:130%;text-decoration:none;}#yiv7450433646 #yiv7450433646ygrp-sponsor #yiv7450433646ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv7450433646 #yiv7450433646ygrp-sponsor #yiv7450433646ov ul {margin:0;padding:0 0 0 8px;}#yiv7450433646 #yiv7450433646ygrp-text {font-family:Georgia;}#yiv7450433646 #yiv7450433646ygrp-text p {margin:0 0 1em 0;}#yiv7450433646 #yiv7450433646ygrp-text tt {font-size:120%;}#yiv7450433646 #yiv7450433646ygrp-vital ul li:last-child {border-right:none !important;}#yiv7450433646

Sun Oct 4, 2015 7:25 am (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

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 hendrikkarnadi@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Terima kasih Mr. Kid.
> Tadi penulisannya kurang "s", pada kolom Debit dan Kredit..
> seharusnya
> sSQL = sSQL & "Select Account, Description, Debit*s*, Credit*s *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' mr.nmkid@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *To:* BeExcel <belajar-excel@yahoogroups.com>
> *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 hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> 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' mr.nmkid@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *To:* BeExcel <belajar-excel@yahoogroups.com>
> *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
> <http://b-excel.blogspot.co.id/2013/07/belajarvba-004-tipe-data-variabel.html>.
> 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 hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> Terima kasih Mr. Kid.
>
> Berhasil....
> Jadi harus ada tambahan tanda &, ""(string) dan Description (untuk
> grupnya).
>
> Salam.
> HK
>
> ------------------------------
> *From:* "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *To:* BeExcel <belajar-excel@yahoogroups.com>
> *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 hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> 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' mr.nmkid@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *To:* BeExcel <belajar-excel@yahoogroups.com>
> *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 hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com> 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
> detail*nya).
>
> Terima kasih.
>
> Salam,
> HK
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

Sun Oct 4, 2015 8:50 am (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  Terima kasih Mr. Kid.Tadi penulisannya kurang "s", pada kolom Debit dan Kredit..seharusnyasSQL = 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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  Terima kasih Mr. Kid.
Berhasil....Jadi harus ada tambahan tanda &, ""(string) dan Description (untuk grupnya).
Salam.
HK
From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  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) & vbCrGROUP BY Account
Ketika macro dijalankan keluar message "Syntax Error" pada huruf yang berwarna merah. Dimana salahnya ya ?
Terima kasih.
Salam,HK

From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 dataSELECT your_key_columns, your_aggregate_expressionsFROM
       (

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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> 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


#yiv1998503557 -- #yiv1998503557ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1998503557 #yiv1998503557ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1998503557 #yiv1998503557ygrp-mkp #yiv1998503557hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1998503557 #yiv1998503557ygrp-mkp #yiv1998503557ads {margin-bottom:10px;}#yiv1998503557 #yiv1998503557ygrp-mkp .yiv1998503557ad {padding:0 0;}#yiv1998503557 #yiv1998503557ygrp-mkp .yiv1998503557ad p {margin:0;}#yiv1998503557 #yiv1998503557ygrp-mkp .yiv1998503557ad a {color:#0000ff;text-decoration:none;}#yiv1998503557 #yiv1998503557ygrp-sponsor #yiv1998503557ygrp-lc {font-family:Arial;}#yiv1998503557 #yiv1998503557ygrp-sponsor #yiv1998503557ygrp-lc #yiv1998503557hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1998503557 #yiv1998503557ygrp-sponsor #yiv1998503557ygrp-lc .yiv1998503557ad {margin-bottom:10px;padding:0 0;}#yiv1998503557 #yiv1998503557actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1998503557 #yiv1998503557activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1998503557 #yiv1998503557activity span {font-weight:700;}#yiv1998503557 #yiv1998503557activity span:first-child {text-transform:uppercase;}#yiv1998503557 #yiv1998503557activity span a {color:#5085b6;text-decoration:none;}#yiv1998503557 #yiv1998503557activity span span {color:#ff7900;}#yiv1998503557 #yiv1998503557activity span .yiv1998503557underline {text-decoration:underline;}#yiv1998503557 .yiv1998503557attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1998503557 .yiv1998503557attach div a {text-decoration:none;}#yiv1998503557 .yiv1998503557attach img {border:none;padding-right:5px;}#yiv1998503557 .yiv1998503557attach label {display:block;margin-bottom:5px;}#yiv1998503557 .yiv1998503557attach label a {text-decoration:none;}#yiv1998503557 blockquote {margin:0 0 0 4px;}#yiv1998503557 .yiv1998503557bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1998503557 .yiv1998503557bold a {text-decoration:none;}#yiv1998503557 dd.yiv1998503557last p a {font-family:Verdana;font-weight:700;}#yiv1998503557 dd.yiv1998503557last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1998503557 dd.yiv1998503557last p span.yiv1998503557yshortcuts {margin-right:0;}#yiv1998503557 div.yiv1998503557attach-table div div a {text-decoration:none;}#yiv1998503557 div.yiv1998503557attach-table {width:400px;}#yiv1998503557 div.yiv1998503557file-title a, #yiv1998503557 div.yiv1998503557file-title a:active, #yiv1998503557 div.yiv1998503557file-title a:hover, #yiv1998503557 div.yiv1998503557file-title a:visited {text-decoration:none;}#yiv1998503557 div.yiv1998503557photo-title a, #yiv1998503557 div.yiv1998503557photo-title a:active, #yiv1998503557 div.yiv1998503557photo-title a:hover, #yiv1998503557 div.yiv1998503557photo-title a:visited {text-decoration:none;}#yiv1998503557 div#yiv1998503557ygrp-mlmsg #yiv1998503557ygrp-msg p a span.yiv1998503557yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1998503557 .yiv1998503557green {color:#628c2a;}#yiv1998503557 .yiv1998503557MsoNormal {margin:0 0 0 0;}#yiv1998503557 o {font-size:0;}#yiv1998503557 #yiv1998503557photos div {float:left;width:72px;}#yiv1998503557 #yiv1998503557photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1998503557 #yiv1998503557photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1998503557 #yiv1998503557reco-category {font-size:77%;}#yiv1998503557 #yiv1998503557reco-desc {font-size:77%;}#yiv1998503557 .yiv1998503557replbq {margin:4px;}#yiv1998503557 #yiv1998503557ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1998503557 #yiv1998503557ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1998503557 #yiv1998503557ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1998503557 #yiv1998503557ygrp-mlmsg select, #yiv1998503557 input, #yiv1998503557 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1998503557 #yiv1998503557ygrp-mlmsg pre, #yiv1998503557 code {font:115% monospace;}#yiv1998503557 #yiv1998503557ygrp-mlmsg * {line-height:1.22em;}#yiv1998503557 #yiv1998503557ygrp-mlmsg #yiv1998503557logo {padding-bottom:10px;}#yiv1998503557 #yiv1998503557ygrp-msg p a {font-family:Verdana;}#yiv1998503557 #yiv1998503557ygrp-msg p#yiv1998503557attach-count span {color:#1E66AE;font-weight:700;}#yiv1998503557 #yiv1998503557ygrp-reco #yiv1998503557reco-head {color:#ff7900;font-weight:700;}#yiv1998503557 #yiv1998503557ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1998503557 #yiv1998503557ygrp-sponsor #yiv1998503557ov li a {font-size:130%;text-decoration:none;}#yiv1998503557 #yiv1998503557ygrp-sponsor #yiv1998503557ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1998503557 #yiv1998503557ygrp-sponsor #yiv1998503557ov ul {margin:0;padding:0 0 0 8px;}#yiv1998503557 #yiv1998503557ygrp-text {font-family:Georgia;}#yiv1998503557 #yiv1998503557ygrp-text p {margin:0 0 1em 0;}#yiv1998503557 #yiv1998503557ygrp-text tt {font-size:120%;}#yiv1998503557 #yiv1998503557ygrp-vital ul li:last-child {border-right:none !important;}#yiv1998503557

Sun Oct 4, 2015 8:54 am (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

Maaf, salahketik...
Maaf Mr. Kid telah merepotkan.....
seharusnyaMaaf Mr. Kid, saya telah merepotkan.....

Salam,HK

----- Forwarded Message -----
From: hendrik karnadi <hendrikkarnadi@yahoo.com>
To: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
Sent: Sunday, 4 October 2015, 22:50
Subject: Re: Fw: [belajar-excel] Perintah SQL untuk menggabungkan data sekaligus menjumlahkan

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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  Terima kasih Mr. Kid.Tadi penulisannya kurang "s", pada kolom Debit dan Kredit..seharusnyasSQL = 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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  Terima kasih Mr. Kid.
Berhasil....Jadi harus ada tambahan tanda &, ""(string) dan Description (untuk grupnya).
Salam.
HK
From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  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) & vbCrGROUP BY Account
Ketika macro dijalankan keluar message "Syntax Error" pada huruf yang berwarna merah. Dimana salahnya ya ?
Terima kasih.
Salam,HK

From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 dataSELECT your_key_columns, your_aggregate_expressionsFROM
       (

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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> 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


#yiv2979263520 -- #yiv2979263520ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2979263520 #yiv2979263520ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2979263520 #yiv2979263520ygrp-mkp #yiv2979263520hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2979263520 #yiv2979263520ygrp-mkp #yiv2979263520ads {margin-bottom:10px;}#yiv2979263520 #yiv2979263520ygrp-mkp .yiv2979263520ad {padding:0 0;}#yiv2979263520 #yiv2979263520ygrp-mkp .yiv2979263520ad p {margin:0;}#yiv2979263520 #yiv2979263520ygrp-mkp .yiv2979263520ad a {color:#0000ff;text-decoration:none;}#yiv2979263520 #yiv2979263520ygrp-sponsor #yiv2979263520ygrp-lc {font-family:Arial;}#yiv2979263520 #yiv2979263520ygrp-sponsor #yiv2979263520ygrp-lc #yiv2979263520hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2979263520 #yiv2979263520ygrp-sponsor #yiv2979263520ygrp-lc .yiv2979263520ad {margin-bottom:10px;padding:0 0;}#yiv2979263520 #yiv2979263520actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2979263520 #yiv2979263520activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2979263520 #yiv2979263520activity span {font-weight:700;}#yiv2979263520 #yiv2979263520activity span:first-child {text-transform:uppercase;}#yiv2979263520 #yiv2979263520activity span a {color:#5085b6;text-decoration:none;}#yiv2979263520 #yiv2979263520activity span span {color:#ff7900;}#yiv2979263520 #yiv2979263520activity span .yiv2979263520underline {text-decoration:underline;}#yiv2979263520 .yiv2979263520attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2979263520 .yiv2979263520attach div a {text-decoration:none;}#yiv2979263520 .yiv2979263520attach img {border:none;padding-right:5px;}#yiv2979263520 .yiv2979263520attach label {display:block;margin-bottom:5px;}#yiv2979263520 .yiv2979263520attach label a {text-decoration:none;}#yiv2979263520 blockquote {margin:0 0 0 4px;}#yiv2979263520 .yiv2979263520bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2979263520 .yiv2979263520bold a {text-decoration:none;}#yiv2979263520 dd.yiv2979263520last p a {font-family:Verdana;font-weight:700;}#yiv2979263520 dd.yiv2979263520last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2979263520 dd.yiv2979263520last p span.yiv2979263520yshortcuts {margin-right:0;}#yiv2979263520 div.yiv2979263520attach-table div div a {text-decoration:none;}#yiv2979263520 div.yiv2979263520attach-table {width:400px;}#yiv2979263520 div.yiv2979263520file-title a, #yiv2979263520 div.yiv2979263520file-title a:active, #yiv2979263520 div.yiv2979263520file-title a:hover, #yiv2979263520 div.yiv2979263520file-title a:visited {text-decoration:none;}#yiv2979263520 div.yiv2979263520photo-title a, #yiv2979263520 div.yiv2979263520photo-title a:active, #yiv2979263520 div.yiv2979263520photo-title a:hover, #yiv2979263520 div.yiv2979263520photo-title a:visited {text-decoration:none;}#yiv2979263520 div#yiv2979263520ygrp-mlmsg #yiv2979263520ygrp-msg p a span.yiv2979263520yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2979263520 .yiv2979263520green {color:#628c2a;}#yiv2979263520 .yiv2979263520MsoNormal {margin:0 0 0 0;}#yiv2979263520 o {font-size:0;}#yiv2979263520 #yiv2979263520photos div {float:left;width:72px;}#yiv2979263520 #yiv2979263520photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv2979263520 #yiv2979263520photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2979263520 #yiv2979263520reco-category {font-size:77%;}#yiv2979263520 #yiv2979263520reco-desc {font-size:77%;}#yiv2979263520 .yiv2979263520replbq {margin:4px;}#yiv2979263520 #yiv2979263520ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2979263520 #yiv2979263520ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2979263520 #yiv2979263520ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2979263520 #yiv2979263520ygrp-mlmsg select, #yiv2979263520 input, #yiv2979263520 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2979263520 #yiv2979263520ygrp-mlmsg pre, #yiv2979263520 code {font:115% monospace;}#yiv2979263520 #yiv2979263520ygrp-mlmsg * {line-height:1.22em;}#yiv2979263520 #yiv2979263520ygrp-mlmsg #yiv2979263520logo {padding-bottom:10px;}#yiv2979263520 #yiv2979263520ygrp-msg p a {font-family:Verdana;}#yiv2979263520 #yiv2979263520ygrp-msg p#yiv2979263520attach-count span {color:#1E66AE;font-weight:700;}#yiv2979263520 #yiv2979263520ygrp-reco #yiv2979263520reco-head {color:#ff7900;font-weight:700;}#yiv2979263520 #yiv2979263520ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2979263520 #yiv2979263520ygrp-sponsor #yiv2979263520ov li a {font-size:130%;text-decoration:none;}#yiv2979263520 #yiv2979263520ygrp-sponsor #yiv2979263520ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2979263520 #yiv2979263520ygrp-sponsor #yiv2979263520ov ul {margin:0;padding:0 0 0 8px;}#yiv2979263520 #yiv2979263520ygrp-text {font-family:Georgia;}#yiv2979263520 #yiv2979263520ygrp-text p {margin:0 0 1em 0;}#yiv2979263520 #yiv2979263520ygrp-text tt {font-size:120%;}#yiv2979263520 #yiv2979263520ygrp-vital ul li:last-child {border-right:none !important;}#yiv2979263520

Sun Oct 4, 2015 12:35 pm (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

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 hendrikkarnadi@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> 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' mr.nmkid@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *To:* BeExcel <belajar-excel@yahoogroups.com>
> *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 hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> Terima kasih Mr. Kid.
> Tadi penulisannya kurang "s", pada kolom Debit dan Kredit..
> seharusnya
> sSQL = sSQL & "Select Account, Description, Debit*s*, Credit*s *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' mr.nmkid@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *To:* BeExcel <belajar-excel@yahoogroups.com>
> *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 hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> 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' mr.nmkid@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *To:* BeExcel <belajar-excel@yahoogroups.com>
> *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
> <http://b-excel.blogspot.co.id/2013/07/belajarvba-004-tipe-data-variabel.html>.
> 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 hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> Terima kasih Mr. Kid.
>
> Berhasil....
> Jadi harus ada tambahan tanda &, ""(string) dan Description (untuk
> grupnya).
>
> Salam.
> HK
>
> ------------------------------
> *From:* "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *To:* BeExcel <belajar-excel@yahoogroups.com>
> *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 hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> 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' mr.nmkid@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *To:* BeExcel <belajar-excel@yahoogroups.com>
> *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 hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com> 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
> detail*nya).
>
> Terima kasih.
>
> Salam,
> HK
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

Sun Oct 4, 2015 7:11 pm (PDT) . Posted by:

"Hendrik Karnadi" hendrikkarnadi

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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> </div><div>Date:05/10/2015 02:34 (GMT+07:00) </div><div>To: BeExcel <belajar-excel@yahoogroups.com> </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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

Terima kasih Mr. Kid.

Berhasil....
Jadi harus ada tambahan tanda &, ""(string) dan Description (untuk grupnya).

Salam.
HK

From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

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' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
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 hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> 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

============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Tidak ada komentar:

Posting Komentar