ACCESS FEED: Re: [belajar-access] Merge Data dari banyak Database

Minggu, 23 September 2012

Re: [belajar-access] Merge Data dari banyak Database

Makasih Pak infonya. Masalahnya, dalam satu folder nama database bisa berubah2 dan jumlahnya bisa ratusan database. Kalo harus link satu persatu kelamaan ya, bisa tidak kalo link ke semua database yg ada di folder tsb secara otomatis. Karena tiap week akan create folder dan dalam folder tsb akan otomatis create database dengan nama yang berbeda-beda.

Thanks sebelumnya.

--- On Sat, 9/22/12, Sumiyanto Surabaya <sumiyanto@yahoo.com> wrote:

From: Sumiyanto Surabaya <sumiyanto@yahoo.com>
Subject: Re: [belajar-access] Merge Data dari banyak Database [1 Attachment]
To: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Date: Saturday, September 22, 2012, 6:34 PM

Bisa di coba Pertama buat database baru, kemudian link table arahkan ke data-1, pilih table 1form dan 2form, ulangi sampai ke data-2,3,4 sehingga terbentuk link table ke 4 database tersebut.

kedua buat query union all;

SELECT [1Form1].ID, [1Form1].HHCODE, [1Form1].SEQNO, [1Form1].Pago, [1Form1].ITEMCODE, [1Form1].QUANTITY, [1Form1].UNITPRICE, [1Form1].TOTALPAID, [1Form1].PROMO, [1Form1].REMARKS, [1Form1].CategoryCode, [1Form1].Incomplete
FROM 1Form1;

union all SELECT [1Form2].ID, [1Form2].HHCODE, [1Form2].SEQNO, [1Form2].Pago, [1Form2].ITEMCODE, [1Form2].QUANTITY, [1Form2].UNITPRICE, [1Form2].TOTALPAID, [1Form2].PROMO, [1Form2].REMARKS, [1Form2].CategoryCode, [1Form2].Incomplete
FROM 1Form2;

union all SELECT [1Form3].ID, [1Form3].HHCODE, [1Form3].SEQNO, [1Form3].Pago, [1Form3].ITEMCODE, [1Form3].QUANTITY, [1Form3].UNITPRICE, [1Form3].TOTALPAID, [1Form3].PROMO, [1Form3].REMARKS, [1Form3].CategoryCode, [1Form3].Incomplete
FROM 1Form3;

UNION ALL SELECT [1Form4].ID, [1Form4].HHCODE, [1Form4].SEQNO, [1Form4].Pago, [1Form4].ITEMCODE, [1Form4].QUANTITY, [1Form4].UNITPRICE, [1Form4].TOTALPAID, [1Form4].PROMO, [1Form4].REMARKS, [1Form4].CategoryCode, [1Form4].Incomplete

FROM 1Form4;

dan

SELECT [2Form1].Filenama, [2Form1].PAGo, [2Form1].HHCODE, [2Form1].SEQNO, [2Form1].PRIMARYBUYER, [2Form1].PURCHASEDATE, [2Form1].PURCHASETIME, [2Form1].STORETYPE, [2Form1].STORENAME, [2Form1].ACV, [2Form1].COMPANION1, [2Form1].COMPANION2, [2Form1].COMPANION3, [2Form1].COMPANION4, [2Form1].COMPANION5, [2Form1].COMPANION6, [2Form1].COMPANION7, [2Form1].COMPANION8, [2Form1].COMPANION9, [2Form1].COMPANION10, [2Form1].REMARKS, [2Form1].IDENTRY, [2Form1].TypeOfPurchase, [2Form1].TotalNoOfTrip1, [2Form1].entrydate, [2Form1].statushh, [2Form1].TotalNoOfTrip2, [2Form1].Fol
FROM 2Form1;
UNION ALL SELECT [2Form2].Filenama, [2Form2].PAGo, [2Form2].HHCODE, [2Form2].SEQNO, [2Form2].PRIMARYBUYER, [2Form2].PURCHASEDATE, [2Form2].PURCHASETIME, [2Form2].STORETYPE, [2Form2].STORENAME, [2Form2].ACV, [2Form2].COMPANION1, [2Form2].COMPANION2, [2Form2].COMPANION3, [2Form2].COMPANION4, [2Form2].COMPANION5, [2Form2].COMPANION6, [2Form2].COMPANION7, [2Form2].COMPANION8, [2Form2].COMPANION9, [2Form2].COMPANION10, [2Form2].REMARKS, [2Form2].IDENTRY, [2Form2].TypeOfPurchase, [2Form2].TotalNoOfTrip1, [2Form2].entrydate, [2Form2].statushh, [2Form2].TotalNoOfTrip2, [2Form2].Fol
FROM 2Form2;
UNION ALL SELECT [2Form3].Filenama, [2Form3].PAGo, [2Form3].HHCODE, [2Form3].SEQNO, [2Form3].PRIMARYBUYER, [2Form3].PURCHASEDATE, [2Form3].PURCHASETIME, [2Form3].STORETYPE, [2Form3].STORENAME, [2Form3].ACV, [2Form3].COMPANION1, [2Form3].COMPANION2, [2Form3].COMPANION3, [2Form3].COMPANION4, [2Form3].COMPANION5, [2Form3].COMPANION6, [2Form3].COMPANION7, [2Form3].COMPANION8, [2Form3].COMPANION9, [2Form3].COMPANION10, [2Form3].REMARKS, [2Form3].IDENTRY, [2Form3].TypeOfPurchase, [2Form3].TotalNoOfTrip1, [2Form3].entrydate, [2Form3].statushh, [2Form3].TotalNoOfTrip2, [2Form3].Fol
FROM 2Form3;
UNION ALL SELECT [2Form4].Filenama, [2Form4].PAGo, [2Form4].HHCODE, [2Form4].SEQNO, [2Form4].PRIMARYBUYER, [2Form4].PURCHASEDATE, [2Form4].PURCHASETIME, [2Form4].STORETYPE, [2Form4].STORENAME, [2Form4].ACV, [2Form4].COMPANION1, [2Form4].COMPANION2, [2Form4].COMPANION4, [2Form4].COMPANION4, [2Form4].COMPANION5, [2Form4].COMPANION6, [2Form4].COMPANION7, [2Form4].COMPANION8, [2Form4].COMPANION9, [2Form4].COMPANION10, [2Form4].REMARKS, [2Form4].IDENTRY, [2Form4].TypeOfPurchase, [2Form4].TotalNoOfTrip1, [2Form4].entrydate, [2Form4].statushh, [2Form4].TotalNoOfTrip2, [2Form4].Fol
FROM 2Form4;

atau lihat attachment. queryUnion1 dan queryUnion2.

Thank you | Terima Kasih | Matur Suksema

Sumiyanto

http://cakyanto.wordpress.com/
http://mugi.or.id/blogs/sumiyanto/

From: scan smg <scan_smg@yahoo.com>
To: belajar-access@yahoogroups.com
Sent: Saturday, 22 September 2012 2:09 PM
Subject: [belajar-access] Merge Data dari banyak Database [1 Attachment]

Ass.wr.wb

Mohon info ilmunya, bagaimana cara menggabungkan data dari all database yang ada dalam 1 folder. Contoh database terlampir. Terima kasih sebelumnya.

Wass.wr.wb