Združevanje več tabel v en rezultat s poizvedbo za združevanje

V primeru iz prejšnjega razdelka, v katerem je bila uporabljena zbirka podatkov Northwind, so združeni podatki samo iz dveh tabel. Vendar pa lahko v poizvedbo za združevanje zelo preprosto združite tri ali več tabel. Če za izhodišče uporabite na primer prejšnji primer, lahko v rezultat poizvedbe vključite imena zaposlenih. To lahko naredite tako, da dodate tretjo poizvedbo in jo združite s prejšnjo izjavo SQL z dodatno ključno besedo UNION, in sicer tako:

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Ko si ogledate rezultat v pogledu podatkovnega lista, bodo navedeni vsi zaposleni z imenom vzorčnega podjetja, kar najbrž ni zelo uporabno. Če želite, da je v tem polju navedeno, ali je oseba interni zaposleni, zaposleni pri dobavitelju in zaposleni pri vaši stranki, lahko namesto imena podjetja vključite nespremenljivo vrednost. Izjava SQL bi bila podobna tej:

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Rezultati v pogledu podatkovnega lista pa so takšni. Access prikaže teh pet vzorčnih zapisov:

Zgornjo poizvedbo je mogoče še dodatno zmanjšati, saj Access v poizvedbi za združevanje prebere samo imena polj z rezultati iz prve poizvedbe. Tukaj smo odstranili rezultat iz odseke druge in tretje poizvedbe:

SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers

UNION

SELECT "In-house", [Last Name], [First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

V Accessovi poizvedbi za združevanje je razvrščanje po vrstnem redu dovoljeno samo enkrat, vendar je mogoče filtrirati vsako poizvedbo lahko posebej. Oglejte si spodnji primer filtriranja posamezne poizvedbe z dodajanjem stavka WHERE, za katerega je bila uporabljena poizvedba za združevanje iz prejšnjega razdelka.

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"

UNION

SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"

ORDER BY [Last Name], [First Name];

Preklopite na pogled podatkovnega lista, če želite prikazati rezultate, podobne tem:

Če se poizvedbe v združevanju med seboj zelo razlikujejo, bodo v polju z rezultati morda združeni podatki različnih podatkovnih tipov. V takem primeru poizvedba za združevanje najpogosteje vrne rezultate kot podatkovni tip besedila, saj ta podatkovni tip podpira tako besedilo kot tudi številke.

Za prikaz delovanja tega primera bomo uporabili poizvedbo za združevanje Transakcije izdelka v vzorčni zbirki podatkov Northwind. Odprite to vzorčno zbirko podatkov in nato odprite poizvedbo »Transakcije izdelka« v pogledu podatkovnega lista. Zadnjih deset zapisov bi moralo biti podobnih temu rezultatu:

Denimo, da želite polje »Količina« razdeliti na dve polji, in sicer »Nakup« in »Prodaja«. Denimo tudi, da želite za polje brez vrednosti imeti nespremenljivo ničelno vrednost. Izjava SQL za to poizvedbo za združevanje bo podoba tej:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC; 

Če preklopite na pogled podatkovnega lista, bo zadnjih deset zapisov zdaj prikazanih tako:

Kaj lahko naredite, če nadaljujete s tem primerom in želite, da so polja z vrednostjo nič prazna? Izjavo SQL lahko spremenite tako, da namesto vrednosti nič ne prikaže, in sicer tako, da dodate ključno »Null«, na primer tako:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Vendar pa boste zdaj morda dobili nepričakovan rezultat, kot ste morda opazili pri preklopu na pogled podatkovnega lista. V stolpcu »Nakup« so vsa polja počiščena:

To se zgodi, ker Access določi podatkovne tipe polj iz prve poizvedbe. V tem primeru »Null« ni število.

Kaj se torej zgodi, če poskusite vstaviti prazen niz za polja s praznimi vrednostmi? Izjava SQL za ta poskus je morda podobna tej:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Ko preklopite na pogled podatkovnega lista, boste opazili, da Access pridobi vrednosti iz stolpca »Nakupa«, vendar jih pretvori v besedilo. Te besedilne vrednosti prepoznate po poravnavi na levo v pogledu podatkovnega lista. Prazen niz v prvi poizvedbi ni število, zato vidite te rezultate. Opazili boste tudi, da so tudi vrednosti v polju »Prodaja« pretvorjene v besedilo, ker zapisi nakupa vsebujejo prazen niz.

Kako torej rešite to uganko?

Rešitev je, da poizvedbo prisilite, naj pričakuje, da je vrednost polja število. To lahko dosežete s tem izrazom:

IIf(False, 0, Null)

Pogoj za preverjanje False ne bo nikoli True, zato bo izraz vedno vrnil Null, vendar Access še vedno ovrednoti obe možnosti rezultatov in se odloči, ali bo rezultat število ali ničelna vrednost.

Ta izraz lahko v našem primeru uporabimo tako:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Upoštevajte, da druge poizvedbe ni treba spremeniti.

Če preklopite na pogled podatkovnega lista, boste videli želeni rezultat:

Drug način za pridobitev enakega rezultata, da poizvedbam v poizvedbi za združevanje vnaprej dodate še eno poizvedbo:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

Access za vsako polje vrne nespremenljive vrednosti za določen podatkovni tip. Seveda pa ne želite, da rezultat te poizvedbe vpliva na rezultate, zato morate v vrednost False vključiti stavek WHERE:

WHERE False

To je uporaben namig, saj je to vedno False, poizvedba pa ne vrne ničesar. Če to izjavo združite z obstoječo izjavo SQL, je končna izjava podobna tej:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

UNION

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Poseben primer za poizvedbo za združevanje je, da nabor zapisov združite z enim zapisom, ki vsebuje vsoto vrednost enega ali več polj.

Tukaj je še en primer, ki ga lahko ustvarite v vzorčni zbirki podatkov Northwind, da prikažete, kako pridobite skupno vsoto v poizvedbi za združevanje.

1. Ustvarite novo preprosto poizvedbo za prikaz nakupa piva (ID izdelka v zbirki podatkov Northwind je 34 ) v tej sintaksi SQL:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

2. Ko preklopite na pogled podatkovnega lista, bi morali biti prikazani štirje nakupi:

   Datum prejema	Količina
   22. 1. 2006	100
   22. 1. 2006	60
   4. 4. 2006	50
   5. 4. 2006	300

3. Če želite pridobiti skupno vsoto, ustvarite preprosto poizvedbo za zbiranje s to izjavo SQL:

   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))

4. Ko preklopite na pogled podatkovnega lista, bi moral biti prikazan samo en zapis:

   MaxOfDate Received	SumOfQuantity
   5. 4. 2006	510

5. Te dve poizvedbi združite v poizvedbo za združevanje, da v zapise nakupa dodate zapis s skupno količino:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   UNION
   
   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

6. Ko preklopite na pogled podatkovnega lista, bi morali biti prikazani štirje nakupi z vsoto posameznega nakupa, ki ji sledi zapis s skupno vsoto količine:

   Datum prejema	Količina
   22. 1. 2006	60
   22. 1. 2006	100
   4. 4. 2006	50
   5. 4. 2006	300
   5. 4. 2006	510

To opisuje osnove dodajanja skupnih vsot v poizvedbo za združevanje. Morda boste želeli vključiti tudi nespremenljive vrednosti v obe poizvedbi, kot sta »Podrobnosti« in »Vsota«, s katerima vizualno ločite zapis skupne vsote od drugih zapisov. Primer uporabe nespremenljivih vrednosti si lahko ogledate v razdelku Združevanje treh ali več tabel ali poizvedb v poizvedbo za združevanje.

Poizvedbe za združevanje v Accessu privzeto vključujejo samo razlikovalne zapise. Kaj pa če želite vključiti vse zapise? Tukaj bo morda uporaben še en primer.

V prejšnjem razdelku smo vam pokazali, kako v poizvedbi za združevanje ustvarite skupno vsoto. To poizvedbo za združevanje SQL spremenite tako, da vsebuje ID izdelka = 48:

SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION

SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Purchase Order Details].[Date Received];

Ko preklopite na pogled podatkovnega lista, bi moral biti prikazan nekoliko zavajajoč rezultat:

En zapis seveda ne vrne dvakratne količine v skupni vsoti.

Ta rezultat se prikaže, ker je bila na en dan enaka količina čokolade prodana dvakrat, kot je to zabeleženo v tabeli s podrobnostmi naročilnice. Tukaj je prikazan rezultat preproste poizvedbe za izbiranje, ki prikazuje oba zapisa v vzorčni zbirki podatkov Northwind:

V prej omenjeni poizvedbi za združevanje lahko vidite, da polje »ID naročilnice« ni vključeno in da polji ne sestavljata dveh razlikovalnih zapisov.

Če želite vključiti vse zapise, v izjavi SQL namesto UNIOIN uporabite UNION ALL. S tem boste zelo verjetno vplivali na razvrščanje rezultatov, zato priporočamo, da vključite tudi stavek ORDER BY, s katerim določite vrstni red razvrščanja. Tukaj je prikazana spremenjena izjava SQL, ki za izhodišče uporablja prejšnji primer:

SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION ALL

SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Total];

Ko preklopite na pogled podatkovnega lista, bi se morale poleg skupne vsote prikazati vse podrobnosti kot zadnji zapis:

Pogost način uporabe poizvedbe za združevanje je uporaba poizvedbe kot vira zapisov za kontrolnik kombiniranega polja v obrazcu. To kombinirano polje lahko uporabite za izbor vrednosti za filtriranje zapisov obrazca. Na primer za filtriranje zapisov zaposlenih glede na njihovo mesto.

Tukaj je še en primer, ki ga lahko ustvarite v vzorčni zbirki podatkov Northwind, da prikažete, kako bi ta primer lahko deloval.

1. Ustvarite preprosto poizvedbo za izbiranje s to sintakso SQL:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees;

2. Ko preklopite na pogled podatkovnega lista, bi morali biti prikazani ti rezultati:

   Mesto	Filter
   Slovenj Gradec	Slovenj Gradec
   Portorož	Portorož
   Ljubljana	Ljubljana
   Maribor	Maribor
   Slovenj Gradec	Slovenj Gradec
   Ljubljana	Ljubljana
   Slovenj Gradec	Slovenj Gradec
   Ljubljana	Ljubljana
   Slovenj Gradec	Slovenj Gradec

3. Ti rezultati morda niso dokaj uporabni. Vendar pa razširite poizvedbo in jo pretvorite v poizvedbo za združevanje z uporabo te izjave SQL:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees
   
   UNION
   
   SELECT "<All>", "*" AS Filter
   FROM Employees
   
   ORDER BY City;

4. Ko preklopite na pogled podatkovnega lista, bi morali biti prikazani ti rezultati:

   Mesto	Filter
   <Vse>	*
   Portorož	Portorož
   Maribor	Maribor
   Ljubljana	Ljubljana
   Slovenj Gradec	Slovenj Gradec

   Access izvede združevanje devetih zapisov, ki so bili že prikazani, z nespremenljivimi vrednostmi polja <Vse> in »*«.

   Ker ta stavek združevanje ne vsebuje možnosti UNION ALL, Access vrne samo razlikovalne zapise, kar pomeni, da vsako mesto vrnjeno le enkrat z enakimi nespremenljivimi vrednostmi.

5. Ko ustvarite poizvedbo za združevanje, ki vsako ime mesta prikaže le enkrat, poleg tega pa še možnost, ki učinkovito izbere vsa mesta, pa lahko to poizvedbo uporabite kot vir zapisa za kombinirano polje v obrazcu. Če ta določen primer uporabite kot model, lahko v obrazcu ustvarite kontrolnik kombiniranega polja, nastavite to poizvedbo kot vir zapisa, lastnost »Širina stolpca« za stolpec »Filter« nastavite na 0 (nič), da ga skrijete, in nato lastnost »Vezan stolpec« nastavite na 1, da označite kazalo drugega stolpca. V lastnost »Filter« v samem obrazcu lahko nato dodate kodo, ki je podobna spodnji, da aktivirate filter obrazca z uporabo vrednosti, ki je bila izbrana v kontrolniku kombiniranega polja:

   Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'"
   Me.FilterOn = True

   Uporabnik obrazca lahko nato filtrira zapise obrazca po imenu določenega mesta ali izbere <Vse>, da prikaže vse zapise za vsa mesta.

Na vrh strani