Sloučení několika dotazů do jednoho výsledku pomocí sjednocovacího dotazu

V příkladu v předchozím oddílu se z databáze Northwind slučovala data jenom ze dvou tabulek. Ve sjednocovacím dotazu ale můžete velmi snadno sloučit tři nebo více tabulek. Když vyjdeme z předchozího příkladu, mohli byste třeba chtít do výstupu dotazu zahrnout také jména zaměstnanců. Mohli byste toho dosáhnout tak, že přidáte třetí dotaz a sloučíte ho s předchozím příkazem SQL pomocí dalšího klíčového slova UNION:

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];

Když zobrazíte výsledek v zobrazení Datový list, zobrazí se všichni zaměstnanci s ukázkovým názvem společnosti, což asi není moc užitečné. Pokud chcete, aby toto pole označovalo, jestli je daná osoba interní zaměstnanec, dodavatel nebo zákazník, můžete místo názvu společnosti zahrnout pevnou hodnotu. Příkaz SQL by vypadal takto:

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];

V zobrazení Datový list bude výsledek vypadat následovně. Access zobrazí těchto pět ukázkových záznamů:

Dotaz výše by se dal ještě zjednodušit, protože Access čte názvy výstupních polí jenom z prvního dotazu ve sjednocovacím dotazu. Tady uvidíte, jak jsme odebrali výstup ze sekcí druhého a třetího dotazu:

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];

Ve sjednocovacím dotazu v Accessu je řazení povolené jenom jednou, ale každý dotaz se dá filtrovat samostatně. Když vyjdeme ze sjednocovacího dotazu v předchozím oddílu, mohli bychom vytvořit takovýto příklad, kde bychom každý dotaz vyfiltrovali přidáním klauzule WHERE.

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];

Přepněte do zobrazení Datový list a zobrazí se přibližně takovéto výsledky:

Pokud jsou dotazy, které se mají sloučit, velmi odlišné, může nastat situace, kdy se ve výstupním poli musí zkombinovat data různých datových typů. V takovém případě sjednocovací dotaz nejčastěji vrátí výsledky jako datový typ Text, protože tento datový typ může ukládat text i čísla.

Abychom si vysvětlili, jak to funguje, použijeme sjednocovací dotaz Product Transactions (Transakce s výrobky) v ukázkové databázi Northwind. Otevřete tuto ukázkovou databázi a otevřete dotaz Product Transactions (Transakce s výrobky) v zobrazení Datový list. Posledních 10 záznamů by mělo vypadat nějak takto:

Předpokládejme, že chcete pole Quantity (Množství) rozdělit do dvou polí: Buy (Nákup) a Sell (Prodej). Dále předpokládejme, že pro pole bez hodnoty budete chtít použít pevnou hodnotu 0. Příkaz SQL pro tento sjednocovací dotaz bude vypadat takto:

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; 

Pokud přepnete do zobrazení Datový list, zobrazí se posledních deset záznamů teď takto:

Když budeme pokračovat v tomto příkladu, co když chcete, aby pole s nulou byla prázdná? Kód SQL můžete upravit tak, aby se místo nuly nezobrazovat nic, a to přidáním klíčového slova Null následujícím způsobem:

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;

Jak si ale můžete všimnout po přepnutí do zobrazení Datový list, vyskytují se teď neočekávané výsledky. Všechna pole ve sloupci Buy (Nákup) jsou vymazaná:

Dochází k tomu proto, že Access určuje datové typy polí z prvního dotazu. V tomto příkladu Null není číslo.

Co se stane, pokud zkusíte místo prázdných hodnot polí vložit prázdný řetězec? Příkaz SQL pro tento pokus může vypadat třeba takto:

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;

Když přepnete do zobrazení Datový list, uvidíte, že Access načetl hodnoty ve sloupci Buy (Nákup), ale převedl je na text. Že jde o textové hodnoty, můžete zjistit podle toho, že jsou v zobrazení Datový list zarovnané doleva. Prázdný řetězec v prvním dotazu není číslo, a proto se zobrazí takovéto výsledky. Také si můžete všimnout, že i hodnoty ve sloupci Sell (Prodej) jsou převedené na text, protože záznamy o nákupech obsahují prázdný řetězec.

Jak tento problém vyřešit?

Řešením je vynutit, aby dotaz očekával, že hodnotou pole bude číslo. Toho se dá dosáhnout pomocí tohoto výrazu:

IIf(False, 0, Null)

Podmínka, která se kontroluje, tedy False, nebude nikdy True, a proto výraz vždy vrátí Null, ale Access přesto ještě vyhodnotí obě možnosti výstupu a rozhodne, jestli má být výstup číselný nebo Null.

V našem příkladu můžeme tento výraz použít takto:

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;

Všimněte si, že není potřeba upravovat druhý dotaz.

Pokud přepnete do zobrazení Datový list, uvidíte teď výsledek, jaký potřebujete:

Alternativní metodou, jak dosáhnout stejného výsledku, je vložit před dotazy ve sjednocovacím dotazu ještě jeden dotaz:

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

Pro každé pole vrátí Access pevné hodnoty datového typu, který definujete. Samozřejmě nechcete, aby výstup tohoto dotazu kolidoval s výsledky, takže trik, kterému se vyhnout, je zahrnout klauzuli WHERE na hodnotu False:

WHERE False

Toto je trochu trik, protože výsledek je vždycky False a dotaz pak nevrací nic. Když tento příkaz zkombinujeme s existujícím kódem SQL, bude dokončený příkaz vypadat takto:

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;

Speciálním případem sjednocovacího dotazu je sloučení sady záznamů s jedním záznamem, který obsahuje součet hodnot jednoho nebo více polí.

Tady je další příklad, který můžete vytvořit v ukázkové databázi Northwind, abyste si vyzkoušeli získání součtu ve sjednocovacím dotazu.

1. Vytvořte jednoduchý nový dotaz, který zobrazí nákupy piva (to má v databázi Northwind kód výrobku 34). Použijte následující syntaxi 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. Přepněte do zobrazení Datový list a měly by se zobrazit čtyři nákupy:

   Date Received	Quantity
   1/22/2006	100
   1/22/2006	60
   4/4/2006	50
   4/5/2006	300

3. Abyste získali součet, vytvořte jednoduchý agregační dotaz pomocí následující syntaxe SQL:

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

4. Přepněte do zobrazení Datový list a měl by se zobrazit jenom jeden záznam:

   MaxOfDate Received	SumOfQuantity
   4/5/2006	510

5. Tyto dva dotazy můžete sloučit do sjednocovacího dotazu, aby se záznam s celkovým množstvím připojil za záznamy o nákupech:

   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. Přepněte do zobrazení Datový list a měly by se zobrazit čtyři nákupy následované záznamem se součtem množství:

   Date Received	Quantity
   1/22/2006	60
   1/22/2006	100
   4/4/2006	50
   4/5/2006	300
   4/5/2006	510

Tím jsme si pokryli základy přidávání součtů do sjednocovacího dotazu. Do obou dotazů byste mohli zahrnout také pevné hodnoty – třeba Podrobnosti a Celkem, abyste vizuálně odlišili záznam se součtem od předchozích záznamů. Postupy při používání pevných hodnot si můžete připomenout v oddílu Sloučení tří nebo více tabulek nebo dotazů ve sjednocovacím dotazu.

Sjednocovací dotazy v Accessu standardně zahrnují jenom jedinečné záznamy. Ale co když chcete zahrnout všechny záznamy? Tady by mohl být užitečný další příklad.

V předchozí části jsme si ukázali, jak ve sjednocovacím dotazu vytvořit součet. Upravte příkaz SQL tohoto sjednocovacího dotazu tak, aby zahrnoval výrobek s kódem 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];

Přepněte do zobrazení Datový list a měl by se zobrazit poněkud zavádějící výsledek:

Jeden záznam by samozřejmě neměl vrátit jako součet dvojnásobek své hodnoty.

Důvodem pro zobrazení tohoto výsledku je, že v jeden den se dvakrát prodal stejný počet čokolád – jak je zaznamenané v tabulce Purchase Order Details (Rozpis objednávek nákupu). Tady je výsledek jednoduchého výběrového dotazu, který zobrazuje oba záznamy v ukázkové databázi Northwind:

V dříve vedeném sjednocovacím dotazu si můžete všimnout, že není zahrnuté pole Purchase Order ID (Kód objednávky výrobku) a že dvě zahrnutá pole netvoří dva jedinečné záznamy.

Pokud chcete zahrnout všechny záznamy, použijte v kódu SQL namísto příkazu UNION příkaz UNION ALL. Bude to pravděpodobně mít dopad na řazení výsledků, takže možná budete chtít zahrnout také klauzuli ORDER BY, která by pořadí řazení určila. Tady je upravený kód SQL vycházející z předchozího příkladu:

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];

Když přepnete do zobrazení Datový list, měly by se zobrazit všechny podrobnosti a s nimi součet jako poslední záznam:

Běžným využitím sjednocovacího dotazu je, že slouží jako zdroj záznamů pro ovládací prvek pole se seznamem ve formuláři. Pomocí pole se seznamem můžete vybrat hodnotu a filtrovat záznamy ve formuláři. Příkladem by mohlo být filtrování záznamů o zaměstnancích podle jejich města.

Abychom si ukázali, jak by to mohlo fungovat, máme tu další příklad, který můžete vytvořit v ukázkové databázi Northwind.

1. Vytvořte jednoduchý výběrový dotaz pomocí této syntaxe SQL:

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

2. Přepněte do zobrazení Datový list a měly by se zobrazit následující výsledky:

   City	Filter
   Seattle	Seattle
   Bellevue	Bellevue
   Redmond	Redmond
   Kirkland	Kirkland
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle

3. Když se podíváte na tyto výsledky, možná vám nebudou připadat nějak zvlášť užitečné. Zkuste ale dotaz rozšířit a transformovat na sjednocovací dotaz pomocí následující syntaxe SQL:

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

4. Přepněte do zobrazení Datový list a měly by se zobrazit následující výsledky:

   City	Filter
   <All>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   Access sloučí devět záznamů zobrazených výše s pevnými hodnotami polí <All> a *.

   Vzhledem k tomu, že tato sjednocovací klauzule neobsahuje příkaz UNION ALL, vrátí Access jenom jedinečné záznamy, takže každé město se vrátí jenom jednou společně s pevnými identickými hodnotami.

5. Když teď máte dokončený sjednocovací dotaz, který název každého města zobrazuje jenom jednou a spolu s nimi zobrazuje i možnost, která efektivně vybere všechna města, můžete tento dotaz použít jako zdroj záznamů pro pole se seznamem ve formuláři. Když tento konkrétní příklad použijeme jako model, mohli byste ve formuláři vytvořit ovládací prvek pole se seznamem, nastavit vlastnost Šířka sloupce pro sloupec Filter na hodnotu 0 (nula), čímž by se sloupec skryl, a pak nastavit vlastnost Vázaný sloupec na hodnotu 1, která by označovala index druhého sloupce. Ve vlastnosti Filtr samotného formuláře pak můžete přidat kód jako v následujícím příkladu, který umožní aktivovat filtr formuláře pomocí hodnoty vybrané v ovládacím prvku pole se seznamem:

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

   Uživatel formuláře pak může vyfiltrovat záznamy ve formuláři podle názvu konkrétního města nebo vybrat možnost <All> a zobrazit všechny záznamy pro všechna města.

Začátek stránky