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

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

Někdy můžete chtít zobrazit záznamy z jedné tabulky nebo dotazu společně se záznamy z jedné nebo více dalších tabulek a vytvořit z nich jednu sadu záznamů – seznam se všemi záznamy ze dvou nebo více tabulek. K tomuto se v Accessu používá sjednocovací dotaz.

Abyste mohli efektivně pochopit sjednocovací dotazy, měli byste napřed mít povědomí o navrhování základních výběrových dotazů v Accessu. Další informace o návrhu výběrových dotazů najdete v tématu Vytvoření jednoduchého výběrového dotazu.

Poznámka: Obsah tohoto článku platí pro desktopové databáze Accessu. Ve webových databázích a webových aplikacích Accessu se sjednocovací dotazy nedají vytvořit ani použít.

Prostudování funkčního příkladu sjednocovacího dotazu

Pokud jste nikdy dříve nevytvářeli sjednocovací dotaz, může být užitečné napřed prostudovat funkční příklad v accessové šabloně Northwind. Ukázkovou šablonu Northwind můžete vyhledat na úvodní stránce Accessu kliknutím na Soubor > Nový nebo si můžete stáhnout kopii přímo z tohoto umístění: ukázková šablona Northwind.

Až Access otevře databázi Northwind, zavřete formulář přihlašovacího dialogu, který se zobrazí jako první, a pak rozbalte navigační podokno. Klikněte na horní část navigačního podokna a pak vyberte Typ objektu. Tím se všechny objekty databáze uspořádají podle svého typu. Potom rozbalte skupinu Dotazy a uvidíte dotaz s názvem Product Transactions (Transakce s výrobky).

Sjednocovací dotazy se snadno odlišují od jiných dotazovacích objektů, protože mají speciální ikonu připomínající dva propletené kruhy, která představuje sadu vzniklou spojením dvou sad:

Snímek obrazovky s ikonou sjednocovacího dotazu v Accessu

Na rozdíl od běžných výběrových a akčních dotazů nejsou tabulky ve sjednocovacím dotazu v relaci. To znamená, že k vytvoření nebo úpravě sjednocovacího dotazu nejde použít grafického návrháře dotazů v Accessu. Všimnete si toho, když otevřete sjednocovací dotaz z navigačního podokna. Access ho otevře a zobrazí výsledky v zobrazení Datový list. V nabídce u tlačítka Zobrazení na kartě Domů nebude při práci se sjednocovacími dotazy dostupná možnost Návrhové zobrazení. Při práci se sjednocovacími dotazy můžete přepínat jenom mezi zobrazením Datový list a zobrazením SQL.

Pokud chcete pokračovat ve zkoumání tohoto příkladu sjednocovacího dotazu, klikněte na Domů > Zobrazení > Zobrazení SQL, aby se zobrazila syntaxe SQL, která dotaz definuje. V této ukázce jsme do kódu SQL přidali několik mezer, abyste snadněji odlišili různé části, ze kterých se sjednocovací dotaz skládá.

Váš prohlížeč nepodporuje video. Nainstalujte si Microsoft Silverlight, Adobe Flash Player nebo Internet Explorer 9.

Pojďme syntaxi SQL tohoto sjednocovacího dotazu z databáze Northwind prozkoumat podrobněji:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity]
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity]
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

První a třetí části tohoto příkazu SQL jsou v podstatě dva výběrové dotazy. Tyto dotazy načítají dvě různé sady záznamů, jednu z tabulky Product Orders (Objednávky výrobků) a jednu z tabulky Product Purchases (Nákupy výrobků).

Druhou částí tohoto příkazu SQL je klíčové slovo UNION, které Accessu dává pokyn, že tento dotaz má sloučit tyto dvě sady záznamů.

Poslední část tohoto příkazu SQL určuje řazení sloučených záznamů pomocí příkazu ORDER BY. V tomto příkladu Access seřadí všechny záznamy podle pole Order Date (Datum obj) v sestupném pořadí.

Poznámka: Sjednocovací dotazy jsou v Accessu vždycky jen pro čtení – v zobrazení Datový list nemůžete změnit žádné hodnoty.

Vytvoření sjednocovacího dotazu vytvořením a sloučením výběrových dotazů

Sjednocovací dotaz sice můžete vytvořit tak, že přímo zapíšete syntaxi SQL v zobrazení SQL, ale možná bude jednodušší vytvořit ho po částech pomocí výběrových dotazů. Pak můžete tyto části SQL zkopírovat a vložit do sloučeného sjednocovacího dotazu.

Pokud byste se místo čtení jednotlivých kroků radši podívali na příklad ve videu, přeskočte na další oddíl: Videoukázka tvorby sjednocovacího dotazu.

  1. Na kartě Vytvoření klikněte ve skupině Dotazy na tlačítko Návrh dotazu.

  2. V dialogovém okně Zobrazit tabulku poklikejte na tabulku obsahující pole, která chcete zahrnout. Tabulka bude přidána do okna návrhu dotazu.

  3. Zavřete dialogové okno Zobrazit tabulku.

  4. V okně návrhu dotazu poklikejte na každé pole, které chcete zahrnout. Při výběru polí dbejte na to, abyste do všech výběrových dotazů přidali stejný počet polí a ve stejném pořadí. Věnujte velkou pozornost datovým typům polí a ujistěte se, zda jsou kompatibilní s datovými typy polí na stejné pozici v ostatních dotazech, které slučujete. Pokud například první výběrový dotaz obsahuje pět polí a první pole obsahuje data typu Datum a čas, zajistěte, aby všechny ostatní výběrové dotazy, které slučujete, také obsahovaly pět polí a jejich první pole bylo typu Datum a čas atd.

  5. Pokud chcete, můžete k polím přidat kritéria zadáním příslušných výrazů do řádku Kritéria v mřížce polí.

  6. Po dokončení přidávání polí a kritérií polí je vhodné spustit výběrový dotaz a ověřit si jeho výstup. Na kartě Návrh klikněte ve skupině Výsledky na tlačítko Spustit.

  7. Přepněte dotaz do návrhového zobrazení.

  8. Uložte výběrový dotaz a nechejte jej otevřený.

  9. Tento postup opakujte pro každý výběrový dotaz, který chcete přidat.

Po vytvoření výběrových dotazů je na řadě jejich sloučení. V tomto kroku můžete vytvořit sjednocovací dotaz zkopírováním a vložením příkazů SQL.

  1. Na kartě Vytvoření klikněte ve skupině Dotazy na tlačítko Návrh dotazu.

  2. Zavřete dialogové okno Zobrazit tabulku.

  3. Na kartě Návrh klikněte ve skupině Dotaz na možnost Sjednocovací. Access skryje okno návrhu dotazu a zobrazí kartu objektu Zobrazení SQL. V tomto okamžiku je karta objektu Zobrazení SQL prázdná.

  4. Klikněte na kartu prvního výběrového dotazu, který chcete sloučit v sjednocovacím dotazu.

  5. Na kartě Domů klikněte na Zobrazení > Zobrazení SQL.

  6. Zkopírujte příkaz SQL pro výběrový dotaz. Klikněte na kartu sjednocovacího dotazu, který jste začali vytvářet.

  7. Vložte příkaz SQL pro výběrový dotaz na kartu objektu Zobrazení SQL sjednocovacího dotazu.

  8. Odstraňte středník (;) na konci příkazu jazyka SQL pro výběrový dotaz.

  9. Stisknutím Enter přesuňte kurzor o jeden řádek dolů a na novém řádku zadejte UNION.

  10. Klikněte na kartu dalšího výběrového dotazu, který chcete sloučit v sjednocovacím dotazu.

  11. Opakujte kroky 5 až 10 tohoto postupu, dokud nezkopírujete a nevložíte všechny příkazy SQL pro výběrové dotazy do okna Zobrazení SQL sjednocovacího dotazu. U posledního příkazu SQL výběrového dotazu neodstraňujte středník ani za něj nic nezadávejte.

  12. Na kartě Návrh klikněte ve skupině Výsledky na tlačítko Spustit.

Výsledky sjednocovacího dotazu se zobrazí v zobrazení Datový list.

Videoukázka tvorby sjednocovacího dotazu

Tady je příklad, který můžete reprodukovat v ukázkové databázi Northwind. Tento sjednocovací dotaz shromažďuje jména lidí z tabulky Customers (Zákazníci) a slučuje je se jmény lidí z tabulky Suppliers (Informace o dodavatelích). Pokud si to chcete vyzkoušet, opakujte tyto kroky ve své kopii ukázkové databáze Northwind.

Váš prohlížeč nepodporuje video. Nainstalujte si Microsoft Silverlight, Adobe Flash Player nebo Internet Explorer 9.

Tady jsou kroky potřebné k vytvoření tohoto příkladu:

  1. Vytvořte dva výběrové dotazy s názvy Dotaz1 a Dotaz2, které budou mít jako zdroje dat tabulky Customers (Zákazníci) a Suppliers (Informace o dodavatelích). Jako zobrazované hodnoty použijte pole First Name (Jméno) a Last Name (Příjmení).

  2. Vytvořte nový dotaz s názvem Dotaz3, který zpočátku nebude mít žádný zdroj dat, a nastavte ho jako sjednocovací dotaz tak, že na kartě Návrh kliknete na možnost Sjednocovací.

  3. Zkopírujte a vložte příkazy SQL z karet Dotaz1 a Dotaz2 na kartu Dotaz3. Nezapomeňte odebrat nadbytečný středník a vložit klíčové slovo UNION. Potom můžete zkontrolovat výsledky v zobrazení Datový list.

  4. Přidejte do jednoho z dotazů klauzuli pro řazení a pak vložte příkaz ORDER BY do zobrazení SQL sjednocovacího dotazu. Všimněte si, že při připojování klauzule pro řazení do sjednocovacího dotazu (Dotaz3) se napřed odeberou středníky a pak názvy tabulek a nechají se jenom názvy polí.

  5. Finální příkaz SQL, který sloučí a seřadí jména pro tento ukázkový sjednocovací dotaz, je následující:

    SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
    FROM Customers
    
    UNION
    
    SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
    FROM Suppliers
    
    ORDER BY [Last Name], [First Name];

Pokud umíte dobře zapisovat syntaxi SQL, můžete samozřejmě napsat vlastní příkaz SQL sjednocovacího dotazu přímo do zobrazení SQL. I tak ale pro vás může být postup se zkopírováním a vožením kódu SQL z jiných objektů dotazů užitečný. Jednotlivé dotazy můžou být mnohem složitější než ukázky jednoduchých výběrových dotazů, které jsme tu použili. Může být výhodné napřed vytvořit a pečlivě otestovat jednotlivé dotazy a až pak je sloučit do sjednocovacího dotazu. Pokud sjednocovací dotaz nepůjde spustit, budete moct upravovat každý dotaz zvlášť, dokud nebude v pořádku, a pak znovu vytvořit sjednocovací dotaz s opravenou syntaxí.

Ve zbývajících oddílech tohoto článku najdete další informace a tipy a triky k používání sjednocovacích dotazů.

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ů:

Employment

Last Name

First Name

In-house

Freehafer

Nancy

In-house

Giussani

Laura

Supplier

Glasson

Stuart

Customer

Goldschmidt

Daniel

Customer

Gratacos Solsona

Antonio

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:

Employment

Last Name

First Name

Supplier

Andersen

Elizabeth A.

In-house

Freehafer

Nancy

Customer

Hasselberg

Jonas

In-house

Hellung-Larsen

Anne

Supplier

Hernandez-Echevarria

Amaya

Customer

Mortensen

Sven

Supplier

Sandberg

Mikael

Supplier

Sousa

Luis

In-house

Thorpe

Steven

Supplier

Weiler

Cornelia

In-house

Zare

Robert

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:

Product ID

Order Date

Company Name

Transaction

Quantity

77

1/22/2006

Supplier B

Purchase

60

80

1/22/2006

Supplier D

Purchase

75

81

1/22/2006

Supplier A

Purchase

125

81

1/22/2006

Supplier A

Purchase

200

7

1/20/2006

Company D

Sale

10

51

1/20/2006

Company D

Sale

10

80

1/20/2006

Company D

Sale

10

34

1/15/2006

Company AA

Sale

100

80

1/15/2006

Company AA

Sale

30

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:

Product ID

Order Date

Company Name

Transaction

Buy

Sell

74

1/22/2006

Supplier B

Purchase

20

0

77

1/22/2006

Supplier B

Purchase

60

0

80

1/22/2006

Supplier D

Purchase

75

0

81

1/22/2006

Supplier A

Purchase

125

0

81

1/22/2006

Supplier A

Purchase

200

0

7

1/20/2006

Company D

Sale

0

10

51

1/20/2006

Company D

Sale

0

10

80

1/20/2006

Company D

Sale

0

10

34

1/15/2006

Company AA

Sale

0

100

80

1/15/2006

Company AA

Sale

0

30

Když budeme s tímto příkladem pokračovat, co budeme dělat v případě, že budeme chtít, aby pole s nulou byla prázdná? Příkaz SQL můžete změnit tak, aby místo nuly nezobrazoval nic, přidáním klíčového slova Null. Bude pak vypadat nějak takto:

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á:

Product ID

Order Date

Company Name

Transaction

Buy

Sell

74

1/22/2006

Supplier B

Purchase

 

 

77

1/22/2006

Supplier B

Purchase

 

 

80

1/22/2006

Supplier D

Purchase

 

 

81

1/22/2006

Supplier A

Purchase

 

 

81

1/22/2006

Supplier A

Purchase

 

 

7

1/20/2006

Company D

Sale

 

10

51

1/20/2006

Company D

Sale

 

10

80

1/20/2006

Company D

Sale

 

10

34

1/15/2006

Company AA

Sale

 

100

80

1/15/2006

Company AA

Sale

 

30

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.

Product ID

Order Date

Company Name

Transaction

Buy

Sell

74

1/22/2006

Supplier B

Purchase

20

 

77

1/22/2006

Supplier B

Purchase

60

 

80

1/22/2006

Supplier D

Purchase

75

 

81

1/22/2006

Supplier A

Purchase

125

 

81

1/22/2006

Supplier A

Purchase

200

 

7

1/20/2006

Company D

Sale

 

10

51

1/20/2006

Company D

Sale

 

10

80

1/20/2006

Company D

Sale

 

10

34

1/15/2006

Company AA

Sale

 

100

80

1/15/2006

Company AA

Sale

 

30

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:

Product ID

Order Date

Company Name

Transaction

Buy

Sell

74

1/22/2006

Supplier B

Purchase

20

 

77

1/22/2006

Supplier B

Purchase

60

 

80

1/22/2006

Supplier D

Purchase

75

 

81

1/22/2006

Supplier A

Purchase

125

 

81

1/22/2006

Supplier A

Purchase

200

 

7

1/20/2006

Company D

Sale

 

10

51

1/20/2006

Company D

Sale

 

10

80

1/20/2006

Company D

Sale

 

10

34

1/15/2006

Company AA

Sale

 

100

80

1/15/2006

Company AA

Sale

 

30

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

Access pro každé pole vrátí pevnou hodnotu s datovým typem, který jste nadefinovali. Vy ale samozřejmě nechcete, aby výstup tohoto dotazu kolidoval se skutečně hledanými výsledky. Kouzlo, jak se tomu vyhnout, spočívá v zahrnutí klauzule WHERE nastavené na 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;

Poznámka: Sloučený dotaz v tomto příkladu používajícím databázi Northwind vrací 100 záznamů, zatímco dva jednotlivé dotazy vrací 58 a 43 záznamů, což je celkem 101. Příčinou této nesrovnalosti je, že dva záznamy nejsou jedinečné. Informace o tom, jak tuto situaci vyřešit pomocí příkazu UNION ALL, najdete v oddílu Práce s jedinečnými záznamy ve sjednocovacích dotazech pomocí příkazu UNION ALL.

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:

Date Received

Quantity

1/22/2006

100

1/22/2006

200

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:

Purchase Order ID

Product

Quantity

100

Northwind Traders Chocolate

100

92

Northwind Traders Chocolate

100

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:

Date Received

Total

Quantity

1/22/2006

 

100

1/22/2006

 

100

1/22/2006

Total

200

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

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×