Gabungkan Beberapa File Akses: Panduan Lengkap untuk Menggabungkan dan Mengkonsolidasikan Data

Ketika Anda bekerja dengan beberapa database de Microsoft Access, cepat atau lambat muncul kebutuhan untuk menyatukan mereka tanpa kehilangan informasi atau memutus tautan. Di dunia nyata, hal ini umum terjadi salinan berbeda dari database yang sama, tabel terpisah per proyek, atau impor berkala dari sistem eksternal. Mengetahui cara menggabungkannya dengan baik akan membedakan antara sistem yang stabil dan sistem yang penuh dengan duplikat.

Dalam panduan praktis ini kami menguraikan, langkah demi langkah, semua yang Anda butuhkan: dari bagaimana bergabung dalam Access dan jenis-jenisnya, hingga trik dengan kueri UNION, resolusi tabrakan autonumber, deteksi konflik, dan rekomendasi ketika dua file .accdb berevolusi secara terpisah. Kami juga menyertakan ikhtisar bermanfaat tentang cocok PDF dari VBA dosa Adobe Acrobat, jika alur kerja Anda memerlukannya.

Apa sebenarnya arti penggabungan beberapa file Access?

Penggabungan bukan sekadar menyalin dan menempel. Dalam praktiknya, penggabungan berarti menggabungkan tabel homolog, menjaga integritas referensial, dan menentukan kriteria untuk menyelesaikan konflik. catatan duplikat atau bertentangan. Ini juga melibatkan pemahaman tentang bagaimana Access menghubungkan tabel dan bagaimana data dari berbagai sumber digabungkan, sesuatu yang dilakukan melalui JOIN dalam kueri atau melalui kueri UNION ketika Anda ingin menumpuk kumpulan hasil yang kompatibel.

Sebelum memulai, ada baiknya meninjau struktur, kunci, dan hubungan setiap berkas. Mengetahui tabel mana yang merupakan master, mana yang dependen, dan kolom mana yang bertindak sebagai kunci (utama dan asing) akan menghemat banyak kesulitan. Sedikit persiapan, seperti mengaktifkan pembaruan berjenjang Dalam hubungan yang terdefinisi dengan baik, hal ini dapat mencegah rekaman anak dibiarkan menggantung saat pengenal berubah.

Hubungan dan gabungan dalam Akses: dasar dari segalanya

Basis data dibangun dari tabel-tabel yang saling terhubung melalui kolom-kolom yang sama. Di Access, hubungan ini diterjemahkan menjadi baris-baris penghubung dalam Tampilan Desain Kueri: sebuah baris menghubungkan kolom-kolom yang berfungsi sebagai tautan dan menentukan bagaimana data digabungkan. Dalam hubungan satu-ke-banyak, Access bahkan menampilkan 1 dan simbol ∞ pada baris jika Anda telah menerapkan integritas referensial.

Gabungan berfungsi seperti filter cerdas: mereka menentukan baris mana di setiap sisi yang dicocokkan dan bagaimana caranya. Selain memfilter, gabungan ini menjadikan setiap pasangan yang memenuhi kondisi menjadi satu baris dalam hasil. Itulah mengapa penting untuk mengetahui jenis gabungan yang Anda gunakan dan, jika perlu, buka properti kombinasi (klik dua kali pada garis) untuk menyesuaikan perilaku.

Jenis gabungan di Access

Gabung dalam

Gabungan dalam hanya mengembalikan baris dari kedua tabel yang memiliki nilai yang cocok di kolom gabungan. Ini adalah tipe yang paling umum, dan Access membuatnya secara otomatis ketika mendeteksi hubungan atau kolom yang kompatibel. Jika Anda tidak menentukan tipenya, Access akan mengasumsikan INNER JOIN secara default.

Saat Anda menambahkan dua tabel ke kueri, jika keduanya berbagi bidang dengan tipe yang kompatibel (dan salah satunya adalah kunci utama), Access cenderung membuatkan gabungan untuk Anda. Jika Anda bekerja dengan kueri sebagai sumber, Anda sering kali harus membuat tautan secara manual dengan menyeret satu bidang ke bidang lainnya di kisi desain, yang akan menggambar garis penghubung.

Sintaks SQL INNER JOIN dalam klausa FROM, dengan operator relasional yang valid (sama dengan, tidak sama dengan, lebih besar/kurang dari, dll.):

FROM tabla1 INNER JOIN tabla2
  ON tabla1.campo1 = tabla2.campo2

Perlu dicatat bahwa, kecuali dalam kasus yang sangat spesifik, operator yang paling umum adalah kesetaraan; penggunaan operator lain menciptakan apa yang dikenal sebagai kombinasi yang tidak sama (kita akan melihatnya di bawah).

Gabungan luar kiri dan kanan (LEFT JOIN/RIGHT JOIN)

Gabungan luar mencakup semua baris dari satu sisi, meskipun tidak cocok di sisi lainnya. Dalam gabungan kiri, semua baris dari tabel kiri dicantumkan, dan hanya baris yang cocok dari kanan; dalam gabungan kanan, yang terjadi justru sebaliknya. Hal ini sangat berguna untuk mempertahankan catatan “yatim piatu” di satu sisi ketika Anda mengaudit cakupan atau kelengkapan.

Untuk mengubah gabungan dalam menjadi gabungan luar, dalam Tampilan Desain klik dua kali garis gabungan, pilih opsi untuk menyertakan semua rekaman di satu sisi, tekan OK dan Anda akan melihat bahwa garis tersebut menampilkan panah arah menunjukkan dari tabel mana semua baris disertakan.

Anda dapat mencampur LEFT JOIN dengan INNER JOIN dalam kueri yang sama, dan Access tidak tahu mana yang harus dieksekusi terlebih dahulu. Dalam hal ini, muncul kesalahan ambiguitas. Solusinya adalah menyusun ulang kueri, membaginya menjadi subkueri, atau memperjelas urutan evaluasi dengan mengatur ulang gabungan sehingga tidak ada ambiguitas.

Sintaks SQL GABUNG KIRI/KANAN dalam klausa FROM:

FROM tabla1 LEFT JOIN tabla2
  ON tabla1.campo1 = tabla2.campo2

FROM tabla1 RIGHT JOIN tabla2
  ON tabla1.campo1 = tabla2.campo2

Gabung luar penuh (FULL OUTER) menggunakan UNION

Access tidak menyertakan FULL OUTER JOIN, tetapi Anda dapat menirunya dengan menggunakan UNION untuk menggabungkan hasil LEFT JOIN dengan hasil RIGHT JOIN dan memfilternya untuk menghindari baris yang cocok dan duplikat. Idenya sederhana: gabungkan semua baris di kedua sisi dan mencocokkannya jika ada kecocokan, meninggalkan nilai null jika tidak ada kecocokan.

Skema kerja tipikal:

1. Bangun kueri dengan LEFT JOIN pada bidang gabung.
2. Salin SQL Anda, ubah KIRI ke KANAN dan tempel di bawah UNION.
3. Di bagian KANAN, tambahkan klausa WHERE yang memeriksa bahwa bidang kunci tabel kiri adalah IS NULL agar tidak terjadi duplikasi kecocokan.

Dengan cara ini Anda mereplikasi efek FULL OUTER di Access bahkan tanpa dukungan asli, mempertahankan hasil yang koheren dan lengkap dalam kedua cara.

Sambungan silang

Gabungan silang memasangkan setiap baris dalam satu tabel dengan setiap baris di tabel lainnya (produk Cartesian). Hal ini biasanya terjadi ketika Anda menambahkan tabel ke kueri dan lupa menentukan tautannya, sehingga menghasilkan ribuan gabungan yang tidak berarti dan waktu eksekusi yang lamaNamun, ada beberapa skenario yang bermanfaat: misalnya, mempelajari semua kemungkinan kombinasi pelanggan dengan persentase pengembalian teoretis yang berbeda (91 pelanggan dikalikan 5 persentase = 455 baris hipotetis).

Jika Anda melihat jumlah data yang tidak wajar dalam kueri Anda, periksa apakah semua tabel memiliki gabungan eksplisitnya. Biasanya, menyeret kolom yang benar sudah cukup untuk menghindari CROSS JOIN yang tidak disengaja dan kembali ke hasil yang konsisten.

Gabungan yang tidak sama (non-equi JOIN)

Penggabungan tidak harus didasarkan pada persamaan. Anda dapat menggunakan operator seperti >, <, >=, <=, atau <> untuk menggabungkan rentang, bendera, atau kondisi yang lebih kompleks. Di Access, jenis penggabungan ini tidak dirancang dari tampilan visual: Anda harus beralih ke Tampilan SQL, temukan operator = dan ganti dengan yang Anda butuhkan. Penting: Jika Anda mengubahnya menjadi pertidaksamaan, Anda mungkin tidak dapat membuka kembali kueri di perancang hingga Anda mengembalikan operator ke =.

Kueri UNION: menumpuk hasil dan menyelaraskan tipe

UNION adalah perekat yang sempurna ketika Anda ingin menumpuk hasil beberapa kueri ke dalam satu tabel virtual, asalkan memiliki jumlah kolom dan tipe yang kompatibel yang sama. UNION sangat penting ketika Anda perlu meniru GABUNG LUAR LENGKAP atau ketika Anda ingin menggabungkan transaksi yang sifatnya berbeda dalam tampilan yang sama.

Kasus umum: Anda memiliki kueri untuk transaksi produk dan ingin membagi kuantitas menjadi dua kolom, Beli dan Jual. Pendekatan pertama adalah menggunakan angka 0 di kolom yang tidak sesuai, dan menumpuk bagian penjualan dengan bagian pembelian. Jika nanti Anda mengganti angka 0 dengan Null Untuk menghindari menampilkan angka nol, Anda mungkin mengalami efek samping: Access menyimpulkan tipe data setiap kolom dari pernyataan SELECT pertama dalam UNION.

Jika SELECT pertama menghasilkan Null (non-numerik), Access dapat "memutuskan" bahwa kolom tersebut berupa teks atau null, yang memengaruhi sisanya. Jika Anda mencoba memasukkan string kosong "" di SELECT awal, Access akan memperlakukannya sebagai teks dan, dengan menyeretnya, mengubah seluruh kolom menjadi teks (Anda akan memperhatikan ini karena mereka muncul rata kiri pada lembar data).

Trik sederhana untuk memaksa Access untuk memperlakukan kolom sebagai numerik dan mengizinkan nilai Null: Gunakan ekspresi seperti ini, yang selalu mengembalikan Null tetapi memaksa keluaran numerik untuk dievaluasi:

IIf(False, 0, Null)

Diterapkan pada UNION pembelian dan penjualan, struktur yang valid bisa jadi:

SELECT , , , ,
       IIf(False, 0, Null) AS Buy,  AS Sell
FROM   
UNION
SELECT , , , ,
        AS Buy, Null AS Sell
FROM   
ORDER  BY  DESC;

Alternatif lanjutan: tambahkan SELECT "template" yang mendefinisikan tipe dan tidak mengembalikan baris. Ini adalah pola SELECT dengan DIMANA Salah, yang menetapkan jenis setiap kolom tanpa mengontaminasi hasilnya:

SELECT 0 AS , Date() AS ,
       "" AS , "" AS ,
       0 AS Buy, 0 AS Sell
FROM   
WHERE  False
UNION
SELECT , , , , Null AS Buy,  AS Sell
FROM   
UNION
SELECT , , , ,  AS Buy, Null AS Sell
FROM   
ORDER  BY  DESC;

Pendekatan ini membuat Access mengenali dengan benar tipe numerik dengan Null, menghindari konversi yang tidak diinginkan ke teks saat ada string kosong di bagian mana pun dari UNION.

Panduan Praktis: Menggabungkan Dua Basis Data Akses Bercabang

Skenario klasik: Dua orang mulai memasukkan data ke dalam cadangan, dan sekarang Anda memiliki dua file yang hampir identik dengan perubahan yang berbeda. Hal pertama yang perlu disadari adalah bahwa Access, kecuali Anda merencanakannya, tidak menyimpan bidang “terakhir diubah” otomatis per rekaman. Anda dapat menambahkannya sekarang dengan Now() dan acara formulir, tetapi jika fork sudah terjadi, Anda memerlukan aturan untuk menentukan versi mana dari setiap rekaman yang akan dipertahankan.

Langkah-langkah yang disarankan untuk melanjutkan dengan aman:

1. Pencadangan dan Pemadatan/Perbaikan:Gandakan semuanya dan jalankan Compact and Repair pada setiap berkas untuk memulai dari awal.
2. Struktur auditBandingkan tabel dan kolom, identifikasi kunci utama untuk setiap tabel, dan catat hubungannya. Jika memungkinkan, tambahkan kolom LastEditedDate dan isi dengan informasi terbaik yang Anda miliki (tanggal pemesanan, tanggal pembuatan, dll.).
3. Menyelesaikan tabrakan nomor otomatisJika kedua database berbagi ID AutoNumber yang berpotensi konflik, terapkan trik "shift". Ubah kolom AutoNumber di salah satu database menjadi Numerik (Integer Panjang) dan jalankan pembaruan dengan menambahkan ID maksimum dari database lainnya. Dengan cara ini, Anda menjamin bahwa tidak ada pengenal yang diinjak.

Contoh pembaruan untuk memindahkan ID di tabel utama bisa seperti ini: tambahkan ke nilai maksimum yang terdeteksi di database lain. Ini menghindari duplikat dan, berkat pembaruan berjenjang Dalam hubungan, tabel anak direferensikan ke ID baru.

Kemudian, impor semua tabel dari database yang disesuaikan ke database “target” dan buat kueri data terlampir untuk menggabungkan set. Jika terdapat konflik (ID yang sama dengan konten yang berbeda), tetapkan kebijakan: simpan rekaman dengan tanggal terbaru, atau simpan keduanya dengan akhiran dan tandai salah satunya sebagai "perlu ditinjau".

Untuk mendeteksi duplikat dan konflik, pola kueri data yang cocok/tidak cocok sangat berguna. Misalnya, untuk menemukan data yang tidak cocok di sisi lain:

SELECT A.*
FROM   TablaA AS A
LEFT JOIN TablaB AS B
  ON A.ID = B.ID
WHERE  B.ID IS NULL;

Dan untuk membandingkan dua versi dengan ID yang sama, Anda dapat mencantumkan baris-baris yang memiliki kolom yang berbeda. Dalam praktiknya, akan lebih mudah untuk membuat tampilan “menunggu rekonsiliasi” dan selesaikan kasus per kasus ketika tidak ada kolom tanggal yang jelas.

Terakhir, periksa relasi di jendela Relasi (menu Alat). Sertakan semua tabel dan hubungkan dengan kuncinya. Aktifkan Pembaruan berjenjang ketika itu masuk akal: jadi jika Anda mengubah pengenal pada yang utama, itu akan menyebar ke yang sekunder dan tidak memutus tautan.

Impor dan gabungkan beberapa tabel sekaligus

Dalam beberapa alur kerja, jendela akses data memungkinkan Anda memilih hingga lima tabel dalam operasi impor yang sama dan menentukan gabungan berpasangan. Proses ini terdiri dari: pilih tabel, bawa mereka ke area persiapan, dan gabungkan keduanya menggunakan kolom kunci yang sama (misalnya, IDPelanggan). Jika nilainya cocok, hasilnya akan cocok dengan masing-masing catatan.

Dalam skema dengan lebih dari dua tabel, "tabel kiri" adalah yang pertama dalam pasangan tersebut, dan "tabel kanan" adalah yang kedua. Anda dapat merangkai beberapa gabungan (Pelanggan dengan Pesanan, lalu Pesanan dengan Produk). Perhatikan bahwa tabel yang sama dapat di sebelah kiri dalam satu gabungan dan di sebelah kanan dalam gabungan lainnya, tergantung pada segmen penggabungannya. Anda juga dapat bergabung dengan lebih dari satu bidang ketika satu bidang tidak cukup unik.

Batasan yang perlu diingat: dengan sumber daya tertentu seperti Apache Drill, editor visual hanya memungkinkan penggabungan dua tabel. Jika Anda ingin menggabungkan tiga tabel atau lebih, lakukan di Modus SQL dan hindari tanda kurung dalam deklarasi gabungan, karena tanda kurung tersebut tidak didukung di konektor spesifik tersebut. Ini detail yang sangat spesifik, tetapi berguna untuk mengetahui kapan data berasal dari sumber yang heterogen.

Hilangkan kombinasi yang salah

Jika Access membuat gabungan otomatis yang tidak Anda inginkan, atau Anda membuat kesalahan saat menyeret bidang, hapus dengan aman: di kisi desain, klik baris dan tekan Hapus, atau klik kanan dan pilih menghapusMempertahankan hanya kombinasi yang diperlukan adalah kunci untuk menghindari persilangan yang tidak disengaja dan memperoleh hasil yang dapat diandalkan.

Praktik yang baik saat menggunakan INNER, LEFT dan RIGHT JOIN

Aturan praktisnya adalah menguji setiap kombinasi dengan filter sederhana untuk memastikan jumlah baris yang dihasilkan sesuai dengan yang diharapkan. Jika Anda mencampur INNER dan LEFT dalam kueri yang sama dan Access menandai ambiguitas, pisahkan masalahnya: pertama-tama buat bagian INNER dalam kueri tersimpan dan menggunakannya sebagai sumber dalam konsultasi yang akan menerapkan KIRI, membuat urutan evaluasi menjadi jelas.

Tips praktis lainnya: dokumentasikan dalam komentar (properti kueri) apa yang dilakukan setiap join dan alasannya. Ketika rekan kerja lain membuka database, mereka akan senang melihat alasan untuk RIGHT JOIN yang tidak biasa atau kombinasi yang tidak merata yang menyaring berdasarkan rentang.

Trik SQL di Access untuk Penggunaan Sehari-hari

Selain pola di atas (IIf(False,0,Null) dan templat SELECT dengan WHERE False), ingatlah bahwa di Access Anda dapat menggunakan operator relasional dalam gabungan untuk menyelesaikan skenario tumpang tindih tanggal atau segmen. Pertidaksamaan yang ditempatkan dengan baik sering kali menggantikan subkueri kompleks dan memungkinkan Anda memasukkan rangkaian baris yang Anda butuhkan.

Dan jika dalam UNION Anda ingin mempertahankan baris duplikat dengan sengaja (misalnya, karena Anda perlu menghitung kemunculan), ganti UNION dengan UNI SEMUAAccess menghapus duplikat dengan UNION, tetapi tidak dengan UNION ALL, yang dapat menentukan tergantung pada laporan akhir Anda.

Ekstra: Gabungkan PDF dari Access dengan VBA (tanpa Acrobat)

Beberapa orang perlu mengekstrak beberapa laporan PDF dan menggabungkannya menjadi satu dari Access. Jika Anda tidak memiliki Adobe Acrobat, ada utilitas VBA yang dapat menyelesaikan masalah ini hanya dalam beberapa baris. Pendekatan yang populer adalah pendekatan pengembang. Albert Kallal, yang menawarkan modul sederhana untuk menggabungkan PDF dari Access tanpa bergantung pada Acrobat. Ini adalah solusi yang efisien untuk mengotomatiskan pembuatan berkas terpadu atau hasil kerja langsung dari proses basis data Anda.

Ide umumnya adalah untuk meneruskannya dengan daftar rute File PDF dan keluaran tujuan; modul akan mengurus penggabungannya. Dengan cara ini, Anda mengintegrasikan Penggabungan PDF sebagai langkah selanjutnya, seperti meluncurkan kueri atau mengekspor laporan.

Dengan semua hal di atas, Anda kini memiliki peta yang lengkap: memahami cara Access menggabungkan data (INNER/LEFT/RIGHT, tidak sama, dan silang), mengemulasikan FULL OUTER dengan UNION, mengukur dampak tipe data dalam kueri UNION, menyesuaikan hubungan dan autonumber untuk menghindari bentrok, dan diakhiri dengan otomatisasi praktis seperti menggabungkan PDF. Setelah Anda menguasai blok-blok ini, menggabungkan beberapa file Access Ini berubah dari sebuah pengembaraan menjadi sebuah proses yang terkendali dan dapat diulang, bahkan dengan tim yang bekerja secara paralel.