Spajanje datoteka s višestrukim pristupom: Potpuni vodič za spajanje i konsolidaciju podataka

Prilikom rada s više Microsoft Access baza podataka , neizbježno se javlja potreba za njihovim objedinjavanjem bez gubitka podataka ili prekida veza. U stvarnom svijetu uobičajeno je imati različite kopije iste baze podataka , tabele odvojene projektima ili redovan uvoz iz eksternih sistema. Poznavanje načina njihovog efikasnog spajanja čini razliku između stabilnog sistema i haotične zbrke duplikata.

U ovom praktičnom vodiču, korak po korak, detaljno objašnjavamo sve što vam je potrebno: od toga kako JOIN-ovi funkcionišu u Accessu i njihovih vrsta, do trikova s ​​UNION upitima, rješavanja kolizija automatskog numerisanja, otkrivanja konflikata i preporuka kada su se dvije .accdb datoteke razvile odvojeno. Također uključujemo koristan savjet o kombinovanju PDF-ova iz VBA bez Adobe Acrobata , u slučaju da vaš radni tok to zahtijeva.

Šta zapravo znači spajanje više Access datoteka?

Spajanje nije samo kopiranje i lijepljenje. U praksi, to znači konsolidaciju sličnih tabela, održavanje referencijalnog integriteta i odlučivanje o kriterijima za rješavanje dupliciranih ili konfliktnih zapisa . Također uključuje razumijevanje kako Access povezuje tabele i kako se podaci iz različitih izvora kombinuju, nešto što se radi putem JOIN upita ili UNION upita kada želite slagati kompatibilne skupove rezultata.

Prije početka, dobra je ideja pregledati strukturu, ključeve i odnose svake datoteke. Jasno razumijevanje koje su tabele glavne, koje zavisne i koja polja djeluju kao ključevi (primarni i strani) uštedjet će vam mnogo problema. Minimalna priprema, poput omogućavanja kaskadnih ažuriranja na dobro definiranim odnosima, može spriječiti da se podređeni zapisi zaglave kada se identifikator promijeni.

Relacije i spajanja u Accessu: osnova svega

Baza podataka se gradi od tabela koje su međusobno povezane zajedničkim poljima. U Accessu, ovi odnosi su predstavljeni kao linije spajanja u prikazu dizajna upita: linija povezuje polja koja djeluju kao veza i definira kako se podaci spajaju. U odnosima jedan-prema-više, Access čak prikazuje 1 i simbol ∞ iznad linije ako ste primijenili referencijalni integritet.

Spojevi funkcioniraju poput pametnog filtera: oni definiraju koji su redovi na svakoj strani upareni i kako. Pored filtriranja, spoj uzrokuje da svaki par koji ispunjava uvjet postane jedan red u rezultatu. Stoga je ključno znati vrstu spoja koju koristite i, ako je potrebno, otvoriti Svojstva spoja (dvostrukim klikom na red) da biste prilagodili njegovo ponašanje.

Vrste spajanja u Accessu

Unutrašnji spoj

Unutrašnje spajanje vraća samo redove iz obje tabele koji imaju odgovarajuće vrijednosti u polju (poljima) spajanja. To je najčešći tip i Access ga automatski kreira kada otkrije kompatibilne odnose ili polja. Ako ne navedete tip, Access će podrazumevano koristiti INNER JOIN.

Kada dodate dvije tabele u upit, ako one dijele polje kompatibilnih tipova (i jedna od njih je primarni ključ), Access obično kreira spoj umjesto vas. Ako radite s upitima kao izvorima podataka, obično ćete morati ručno kreirati vezu prevlačenjem jednog polja na drugo u mreži dizajna, što crta liniju spajanja.

SQL sintaksa za INNER JOIN u FROM klauzuli, sa bilo kojim važećim relacijskim operatorom (jednako, nejednako, veće/manje od, itd.):

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

Imajte na umu da, osim u vrlo specifičnim slučajevima, najčešći operator je jednakost; korištenje drugih operatora stvara ono što je poznato kao nejednake kombinacije (ovo ćemo vidjeti u nastavku).

Lijevi i desni vanjski spoj (LEFT JOIN/RIGHT JOIN)

Vanjski spoj uključuje sve redove s jedne strane, čak i ako nemaju podudaranje na drugoj. Kod lijevog spoja (LEFT JOIN), navedeni su svi redovi iz lijeve tabele, a s desne samo odgovarajući redovi; kod desnog spoja (RIGHT JOIN) događa se suprotno. Ovo je vrlo korisno za očuvanje "ostavljenih" zapisa s jedne strane prilikom revizije pokrivenosti ili potpunosti.

Da biste pretvorili unutrašnji spoj u vanjski spoj, u prikazu dizajna dvaput kliknite na liniju spoja, odaberite opciju za uključivanje svih zapisa s jedne strane, kliknite U redu i vidjet ćete da linija prikazuje strelicu smjera koja pokazuje iz koje tabele su uključeni svi redovi.

Moguće je da u istom upitu pomiješate LEFT JOIN sa INNER JOIN, te Access neće znati koji da prvo izvrši. U tom slučaju, pojavit će se greška dvosmislenosti. Rješenje uključuje promjenu redoslijeda upita , dijeljenje na podupite ili pojašnjenje redoslijeda evaluacije preuređivanjem spajanja kako bi se izbjegla dvosmislenost.

SQL sintaksa LEFT/RIGHT JOIN u FROM klauzuli:

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

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

Potpuno vanjsko spajanje (FULL OUTER) korištenjem UNION-a

Access ne uključuje potpuno vanjsko spajanje kao takvo, ali ga možete emulirati korištenjem funkcije UNION za kombiniranje rezultata lijevog spajanja s rezultatom desnog spajanja i filtriranjem kako biste izbjegli dupliranje podudarnih redova. Ideja je jednostavna: kombinirajte sve redove s obje strane i uparite ih tamo gdje postoji podudaranje, ostavljajući null vrijednosti tamo gdje ga nema.

Tipična shema rada:

1. Napravite upit s LEFT JOIN u polju za spajanje.
2. Kopirajte SQL, promijenite LEFT u RIGHT i zalijepite ispod UNION-a.
3. U dijelu RIGHT, dodajte klauzulu WHERE koja provjerava da li je ključno polje lijeve tabele IS NULL kako ne bi došlo do dupliranja utakmica.

Na ovaj način replicirate efekat FULL OUTER-a u Accessu čak i bez izvorne podrške, održavajući konzistentan i potpun rezultat u oba smjera.

Unakrsni spojevi

Unakrsno spajanje uspoređuje svaki red u jednoj tabeli sa svakim redom u drugoj (Kartezijev proizvod). Ovo se često dešava kada dodate tabele u upit i zaboravite definirati njihov odnos, što rezultira hiljadama besmislenih kombinacija i dugim vremenom izvršavanja . Međutim, postoje korisni scenariji: na primjer, proučavanje svih mogućih kombinacija kupaca s različitim teorijskim stopama povrata (91 kupac x 5 stopa = 455 hipotetičkih redova).

Ako u upitu vidite neuobičajeno veliki broj zapisa, dvaput provjerite da li sve tabele imaju eksplicitne spojeve. Obično je jednostavno prevlačenje ispravnih polja dovoljno da se izbjegne slučajno unakrsno spajanje i da se vrati konzistentan rezultat.

Nejednaki spojevi (non-equi JOIN)

Unije ne moraju biti zasnovane na jednakosti. Možete koristiti operatore poput >, <, >=, <= ili <> za spajanje raspona, zastavica ili složenijih uvjeta. U Accessu se ove vrste unija ne dizajniraju iz vizualnog prikaza: morate se prebaciti na SQL prikaz , pronaći operator = i zamijeniti ga onim koji vam je potreban. Važno: Ako se prebacite na nejednakost, možda nećete moći ponovo otvoriti upit u dizajneru dok ne vratite operator na =.

UNION upiti: slaganje rezultata i usklađivanje tipova

UNION naredbe su savršeno vezivno sredstvo kada želite da složite rezultate više upita u jednu virtuelnu tabelu, pod uslovom da imaju isti broj kolona i kompatibilne tipove podataka. Ključne su kada trebate da oponašate FULL OUTER JOIN ili kada želite da kombinujete transakcije različitih tipova u istom prikazu.

Tipičan primjer: Imate upit za transakcije proizvoda i želite podijeliti količinu u dvije kolone, Kupovina i Prodaja. Prvi pristup je korištenje 0 u koloni koja se ne primjenjuje i slaganje dijela prodaje s dijelom kupovine. Ako kasnije zamijenite 0 sa Null kako biste izbjegli prikazivanje nula, možete naići na nuspojavu: Access zaključuje tip podataka svake kolone iz prve SELECT naredbe u UNION-u.

Ako prva SELECT naredba vrati NULL (nenumerički), Access može "odlučiti" da li je kolona tekst ili null, što utiče na ostale. Ako pokušate da stavite prazne nizove znakova "" u početnu SELECT naredbu, Access ih tretira kao tekst i, posljedično, pretvara cijelu kolonu u tekst (primijetit ćete ovo jer se u podatkovnom listu pojavljuju poravnati lijevo).

Jednostavan trik da se natjera Access da tretira kolonu kao numeričku i dozvoli Null vrijednosti: koristite izraz poput ovog, koji uvijek vraća Null, ali prisiljava na evaluaciju numeričkog izlaza:

IIf(False, 0, Null)

Primijenjeno na UNION kupovina i prodaja, validna struktura bi mogla biti:

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

Napredna alternativa: koristite predložak SELECT naredbe koji definira tipove i ne vraća redove. Ovo je SELECT obrazac sa WHERE FALSE , koji postavlja tip svake kolone bez utjecaja na rezultat.

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;

Ovaj pristup omogućava Accessu da ispravno prepoznaje numeričke tipove sa Null , izbjegavajući neželjene konverzije u tekst kada postoje prazni stringovi u bilo kojem dijelu UNION funkcije.

Vodič za korištenje: Spajanje dvije račvaste Access baze podataka

Klasičan scenario: Dvije osobe su počele unositi podatke u sigurnosnu kopiju i sada imate dvije gotovo identične datoteke s različitim promjenama. Prvo što treba prepoznati je da Access, osim ako to niste posebno planirali, ne sprema automatski polje "zadnja izmjena" za svaki zapis. Ovo možete dodati od sada koristeći Now() i događaje obrasca, ali ako se fork već dogodio, trebat će vam pravila da biste odlučili koja će se verzija svakog zapisa zadržati.

Preporučeni koraci za siguran nastavak:

1. Sigurnosna kopija i sažimanje/popravakDuplirajte sve i pokrenite Sažmi i Popravi na svakoj datoteci da biste krenuli od nule.
2. Struktura revizijeUporedite tabele i polja, identifikujte primarni ključ za svaku tabelu i zabilježite odnose. Ako je moguće, dodajte polje LastEditedDate i popunite ga najboljim informacijama koje imate (datumi narudžbe, datum kreiranja itd.).
3. Rješava kolizije automatskog numerisanjaAko obje baze podataka dijele AutoNumber ID-ove koji bi mogli biti u konfliktu, primijenite trik "shift". Promijenite polje AutoNumber u jednoj od baza podataka u Numeričko (dugi cijeli broj) i pokrenite ažuriranje dodajući maksimalni ID iz druge baze podataka. Na ovaj način garantujete da nijedan identifikator nije nagažen.

Primjer ažuriranja radi promjene ID-ova u glavnoj tabeli može biti nešto poput: dodavanje maksimalne vrijednosti otkrivene u drugoj bazi podataka. Ovo izbjegava duplikate, a zahvaljujući kaskadnim ažuriranjima u odnosima, podređene tabele se referenciraju na novi ID.

Zatim, uvezite sve tabele iz prilagođene baze podataka u "ciljnu" bazu podataka i kreirajte upit za dodavanje kako biste spojili skupove. Ako postoje konflikti (isti ID s različitim sadržajem), definirajte pravilo: zadržite zapis s najnovijim datumom ili zadržite oba sa sufiksom i označite jedan kao "zahtijeva pregled".

Za otkrivanje duplikata i konflikata, obrasci upita za podudarne/nepodudarne zapise su veoma korisni. Na primjer, da biste pronašli nepodudarne zapise na drugoj strani:

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

Da biste uporedili dvije verzije s istim ID-om, možete navesti redove u kojima se kolona od interesa razlikuje. U praksi je praktično kreirati prikaze "na čekanju usklađivanja" i rješavati slučajeve pojedinačno kada ne postoji nedvosmisleno polje za datum.

Na kraju, provjerite odnose u prozoru Odnosi (izbornik Alati). Uključite sve tabele i povežite ih pomoću ključeva. Označite polje Kaskadno ažuriranje kada je to prikladno: na ovaj način, ako promijenite identifikator u primarnoj tabeli, promjena će se proširiti na sekundarnu tabelu i nećete prekinuti veze.

Uvezite i spojite više tabela odjednom

U nekim radnim procesima, prozor za pristup podacima vam omogućava da odaberete do pet tabela u istoj operaciji uvoza i definišete parne spojeve. Proces uključuje odabir tabela , njihov uvoz u privremeno područje i spajanje dvije po dvije pomoću zajedničkog ključnog polja (na primjer, CustomerID). Ako se vrijednosti podudaraju, rezultat se podudara sa pojedinačnim zapisima.

U shemi s više od dvije tabele, "lijeva tabela" je prva u paru, a "desna tabela" je druga. Možete povezati više spojeva (Kupac s Narudžbama, a zatim Narudžbe s Proizvodom). Imajte na umu da ista tabela može biti lijeva tabela u jednom spoju, a desna tabela u drugom, ovisno o segmentu lanca. Također možete spojiti pomoću više polja kada jedno polje nije dovoljno jedinstveno.

Jedno ograničenje koje treba imati na umu: kod određenih izvora poput Apache Drilla, vizualni editor vam omogućava spajanje samo dvije tabele. Ako želite kombinirati tri ili više, učinite to u SQL modu i izbjegavajte zagrade u naredbi spajanja, jer ih taj specifični konektor ne podržava. To je vrlo specifičan detalj, ali je dobro znati kada podaci dolaze iz heterogenih izvora.

Uklonite pogrešne kombinacije

Ako Access kreira automatsko spajanje koje ne želite ili slučajno prevučete polja, slobodno ga izbrišite: u mreži dizajna kliknite na red i pritisnite Delete ili kliknite desnim tasterom miša i odaberite Izbriši . Održavanje samo potrebnih spajanja ključno je za izbjegavanje slučajnih kombinacija i dobijanje pouzdanih rezultata.

Dobre prakse pri korištenju INNER, LEFT i RIGHT JOIN

Dobro pravilo je testirati svaku kombinaciju jednostavnim filterima kako biste provjerili da li vraća očekivani broj redova. Ako pomiješate INNER i LEFT u istom upitu i Access označi dvosmislenost, podijelite problem: prvo izgradite INNER dio u sačuvanom upitu i koristite ga kao izvor u upitu koji će primijeniti LEFT, jasno definirajući sekvencu evaluacije.

Još jedan praktičan savjet: dokumentirajte u komentarima (svojstva upita) šta svaka kombinacija radi i zašto. Kada druge kolege otvore bazu podataka, cijenit će razlog za neobičan RIGHT JOIN ili nejednak spoj koji filtrira po rasponima.

SQL trikovi u Accessu za svakodnevnu upotrebu

Pored gore navedenih obrazaca (IIf(False,0,Null) i predložak SELECT sa WHERE False), imajte na umu da u Accessu možete koristiti relacijske operatore u spajanjima kako biste riješili scenarije koji uključuju preklapanje datuma ili segmenata. Dobro postavljena nejednakost često zamjenjuje složene podupite i omogućava vam da dohvatite tačno onaj skup redova koji vam je potreban.

A ako želite namjerno zadržati duplikate redova u UNION-u (na primjer, zato što trebate brojati pojavljivanja), zamijenite UNION sa UNION ALL . Access uklanja duplikate sa UNION, ali ne i sa UNION ALL, što može biti ključno ovisno o vašem konačnom izvještaju.

Dodatno: Kombinujte PDF iz Accessa sa VBA (bez Acrobata)

Neki ljudi trebaju generirati više PDF izvještaja i kombinirati ih u jedan dokument iz Accessa. Ako nemate Adobe Acrobat, postoje VBA uslužni programi koji to mogu riješiti u samo nekoliko linija koda. Jedno poznato rješenje nudi programer Albert Kallal , koji pruža jednostavan modul za spajanje PDF-ova iz Accessa bez oslanjanja na Acrobat. To je agilno rješenje za automatizaciju generiranja objedinjenih dosijea ili rezultata direktno iz vaših procesa baze podataka.

Opšta ideja je da mu se proslijedi lista putanja PDF datoteka i odredišni izlaz; modul se brine o njihovom spajanju. Na ovaj način integrišete spajanje PDF-ova u svoj radni tok kao još jedan korak, poput pokretanja upita ili izvoza izvještaja.

Sa svim gore navedenim, sada imate kompletnu mapu: razumijevanje kako Access spaja podatke (INNER/LEFT/RIGHT, nejednako i unakrsno), emuliranje FULL OUTER pomoću UNION upita, mjerenje utjecaja tipova podataka u UNION upitima, prilagođavanje odnosa i automatskih numeracija kako bi se izbjegli sukobi, te završavanje praktičnim automatizacijama poput spajanja PDF-ova. Kada savladate ove blokove, spajanje više Access datoteka prelazi iz muke u kontrolirani i ponovljivi proces, čak i kada timovi rade paralelno.