Assalamu'alaikum Wr. Wb.
1. buat sebuah query UNION di file access kira-kira strukturnya : (simpan,
misal namanya qViewOut1)
SELECT 'Pembelian' as jenis, kol1,kol2,kolN FROM tblPembelian
UNION ALL
SELECT 'Penjualan' as jenis, kol1,kol2,kolN FROM tblPenjualan
UNION ALL
SELECT 'Mutasi' as jenis, kol1,kol2,kolN FROM tblMutasi
UNION ALL
SELECT 'Pengembalian' as jenis, kol1,kol2,kolN FROM tblPengembalian
2. buka Excel, buat sheet baru, lakukan get external data dari access untuk
ambil qViewOut1, tampilkan sebagai pivot table
3. atur pivot table agar tanggal di sisi rows, jenis di sisi columns, qty
dan jumlah yang dibutuhkan di sisi Values
sampai sini, data tanggal yang ditampilkan adalah seluruh data yang ada.
Jika data memiliki tanggal yang loncat (misal data hanya punya 1 lalu 17
lalu 19)
jika ingin tanggal tertentu atau bulan tertentu, bisa di filter di pivot
table atau dalam query saat akan ambil data dari access dengan get external
data tadi.
Jika ingin series tanggalnya utuh (tidak tergantung tanggal dalam data di
access, maka buat sheet baru dengan header (1 cell saja tidak merge)
bernama tanggal di A1 dan A2 mulai diisi data tanggal sebulan yang akan
ditampilkan.
Save workbook, buat link table seperti email lalu terhadap sheet kriteria
yang berisi data tanggal saja itu.
tambahkan pada qViewOut1 dengan query union si tabel kriteria ini.
Misal tabel hasil link diberi nama tLinkTanggal
tambahkan di qViewOut1 bunyi :
UNION ALL
SELECT 'Mutasi' as jenis,kolTanggal,0,0,0 FROM tLinkTanggal
save query
refresh pivot table di excel
klik kanan field tanggal dalam pivot, field setting, tab layout & print,
centang show item with no data,ok sampai kembali ke worksheet
Sampai sini, semua tanggal sebulan akan tampak beserta bulan-bulan lain.
Filter saat get external data atau filter pivot table bisa dilakukan untuk
memilih bulan tertentu.
Biasanya sampai sini masih kurang sesuai keinginan. Baiklah, kita mulai
yang lebih lanjut tentang hal ini, tetapi pastikan dulu hal-hal di atas
telah dipahami benang merahnya, bahwa pivot table bekerja menampilkan apa
saja yang ada dengan mudah ketika struktur datanya sederhana, yaitu
berbentuk fact table yang berisi dimensions dan measures
di Access buat query baru (misal diberi nama qViewOut2)
isi dengan query berbunyi :
SELECT * FROM qViewOut1
WHERE
nama_kol_tanggal
BETWEEN (SELECT min( kolTgl ) as tgl FROM tLinkTanggal)
AND (SELECT max( kolTgl ) as tgl FROM tLinkTanggal)
di Excel sheet baru lakukan get external data ambil dari access qViewOut2
dan ditampilkan sebagai pivot table. Atur pivot table termasuk setting
field tanggal agar show item with no data.
save workbook. Setiap kali ubah tabel kriteria, lakukan save workbook dan
refresh pivot table.
Sampai sini, susunan pivot table mestinya sudah bisa seperti yang
diharapkan.
Jika masih ndak puas, silakan pelajari query JOIN lebih dulu, kemudian
mulai buat query JOIN multi tables.
Kira-kira susunannya : (silakan tanya ke mas Aksan, bang Haer, atau bang
Opi cara convert bunyinya ke querynya access yang optimum)
SELECT ambil kolom yang dibutuhkan dengan menyertai nama alias si tabel
masing-masing misal
SELECT dt1.tanggal, dtjual.qty as Qty_Jual, dtjual.jumlahharga as
TotHarga_Jual, dtbeli.blablabla....,,,,,,
FROM tLinkTanggal as dt1
LEFT OUTER JOIN (SELECT tanggal, SUM(qty) as qty, SUM(jumlahharga) as
jumlahharga FROM tblPenjualan GROUP BY tanggal) as dtJual
ON dt1.tanggal=dtJual.Tanggal
LEFT OUTER JOIN (SELECT tanggal, SUM(qty) as qty, SUM(jumlahharga) as
jumlahharga FROM tblPembelian GROUP BY tanggal) as dtBeli
ON dt1.tanggal=dtBeli.Tanggal
dan join ke tabel selanjutnya
simpan query misal bernama qViewOut3
lalu get external data di Excel, bisa ditampilkan menjadi tabel atau pivot
table. Biasanya pivot tablenya sudah ndak terlalu luwes lagi.
Wassalamu'alaikum Wr. Wb.
Kid.
2013/4/20 Abdurrahman Suryadi <[email protected]>
> **
>
>
> Wa'alaikmslam Wr. Wb.
> Terima kasih atas jawabannya yg inspiratif Mr. Kid. Mohn maaf kalau
> prtanyaan saya tdk jelas.
> kasus aslinya seperti ini:
> Saya mempunyai minimal 4 transaksi dalam 4 table. yaitu tblPembelian,
> tblPenjualan, tblRetun, tblMutasi.
> Semua table saya group menurut tanggal kemudian qty dan JumlahHarga
> dijumlahkan. Data tersebut kemudian mau saya masukkan ke dalam excel. di
> excel sudah tersedia tanggal 1-3-2-13 s/d 31-3-2013. seperti format
> terlampir. bagaimana cara memasukkan data yg ada di setiap table ke setiap
> kolom sesuai dengan tanggal yg ada?
> File excel & access terlampir. Terima kasih
> Wassalamu'alaikum Wr. Wb.
>
>
> Pada 19 April 2013 21.15, Mr. Kid <[email protected]> menulis:
>
> **
>>
>>
>> Wa'alaikumussalam Wr. Wb.
>>
>> Bagaimana kalau dicoba begini :
>> 1. nama sheet kriteria diasumsikan bernama sheet1 (sesuaikan dengan
>> keadaan nyata) dan memiliki struktur tabel berkaidah database.
>> >> ada nama kolom yang rapat, records juga rapat, tipe data setiap kolom
>> adalah konsisten hanya 1 tipedata
>>
>> 2. tabel sumber data di Access bernama tblData dengan 4 fields.
>>
>> 3. save workbook dan buka file access
>>
>> 4. di file Access, lakukan :
>> >> ribbon External Data -> group Import -> Excel -> browse file Excel
>> yang disave dinomor 3 (file kerja) -> pilih Link blabla -> OK
>> >> pilih show worksheets -> pilih sheet1 (sheetnya kriteria) -> next ->
>> centang First row blabla -> beri nama (misal tblLinkKriteria) -> finish
>>
>> 5. di file Access, buat query Left Join dengan sisi kiri adalah
>> tblLinkKriteria (hasil nomor 4) dan sisi kanan adalah tblData dan atur join
>> keys nya
>> >> simpan query (misal dengan nama qViewKriteria)
>>
>> 6. tutup file access dan kembali ke Excel kerja (Excel yang di save di
>> nomor 3)
>>
>> 7. buat sheet baru (misal diberi nama ViewKriteria) lalu lakukan get
>> external data dari file access tadi dan ambil qViewKriteria
>>
>> 8. atur di VBA, setiap kali user pencet tombol di sheet1 :
>> >> A. save workbook
>> >> B. refresh all
>>
>> 9. save workbook lagi dan coba ubah nilai kriteria dan pencet tombol,
>> lalu lihat ke sheet ViewKriteria
>>
>> moga-moga imajinasi di atas ndak keliru banget dan bisa berhasil jika
>> dilakukan.
>>
>> Wassalamu'alaikum Wr. Wb.
>> Kid.
>>
>>
>>
>>
>>
>> 2013/4/19 Adi <[email protected]>
>>
>>> **
>>>
>>>
>>> Assalamu'alaikum Wr. Wb.
>>>
>>> Saya mempunyai data yg kompleks di access, dan sangat membutuhkan
>>> bantuan para master untuk membawanya ke excel. berikut contoh kasusnya
>>> (mudah2an bisa jelas tanpa attachment, saya butuh logikanya):
>>>
>>> 1. saya mempunya database anak2 di access (D:\Datasiswa.accdb)
>>> 2. dalam database ada table (tblData), terdiri dari 4 field: NoInduk,
>>> Nama, Alamat, tglLahir
>>> 3. Di excel saya sudah siapkan sebuah sheet1 yang di dalamnya ada data
>>> induk siswa.
>>> 4. Pertanyaanya, bagaimana mengambil data yang ada di tblData sehingga
>>> menyesuaikan tempatnya dengan data Induk yang ada di excel?
>>>
>>> Mudah2an bisa dipahami.
>>>
>>> Catatan: sebelum ini saya menggunakan fungsi copyrecordset yang saya
>>> tempatkan di sheet yang lain, kemudian melookup data yang ada ke Nomor
>>> Induk siswa yang sudah ada di sheet1.
>>>
>>> Terima kasih.
>>> Wassalamu'alaikum Wr. Wb.
>>>
>>>
>>
>
>