Někdy můžete chtít zkombinovat záznamy z jedné tabulky nebo dotazu se záznamy z jedné nebo více dalších tabulek do jednoho výsledku. To funguje při sjednocovacím dotazu v Accessu.
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.
Prostudování funkčního příkladu sjednocovacího dotazu
Pokud jste nikdy dříve nevytvářeli sjednocovací dotaz, může pomoci, když si napřed prostudujete funkční příklad v accessové šabloně Northwind. Ukázkovou šablonu Northwind můžete vyhledat na úvodní stránce Accessu výběrem položky Soubor>nový. Kopii si můžete stáhnout také přímo z ukázkové šablony Northwind.
Až Access otevře databázi Northwind, zavřete přihlašovací dialog, který se zobrazí jako první, a rozbalte navigační podokno. Vyberte horní část navigačního podokna a poté vyberte typ objektu . Tím se všechny databázové objekty uspořádají podle typu. Potom rozbalte skupinu Dotazy a uvidíte dotaz s názvem Transakce s produkty.
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:
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 pomocí grafického návrháře dotazů v Accessu nemůžete vytvářet ani upravovat sjednocovací dotazy. Pokud otevřete sjednocovací dotaz z navigačního podokna, Access ho otevře a zobrazí výsledky v zobrazení Datový list. V části Zobrazení na kartě Domů si všimněte, že při práci se sjednocovacími dotazy není k dispozici návrhové zobrazení . Mezi zobrazením Datový list a zobrazením SQL se dá přepínat.
Pokud chcete pokračovat ve zkoumání tohoto příkladu sjednocovacího dotazu, klikněte na položku Domovské>zobrazení>Zobrazení SQL , aby se zobrazila syntaxe SQL , která dotaz definuje. V této ukázce jsme do této SQL ilustrace přidali několik mezer, abyste snadněji odlišili různé části, ze kterých se sjednocovací dotaz skládá.
Podívejme se podrobněji na SQL syntaxi tohoto sjednocovacího dotazu z databáze Northwind:
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 SQL příkazu je klíčové slovo, které Accessu UNION říká, že má tyto dvě sady záznamů zkombinovat.
Poslední část tohoto SQL příkazu určuje pořadí 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 syntaxi SQL zapíšete přímo 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.
- Na kartě Vytvoření klikněte ve skupině Dotazy na tlačítko Návrh dotazu.
- Poklikejte na tabulku obsahující pole, která chcete zahrnout. Tabulka bude přidána do okna návrhu dotazu.
- 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.
- 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í.
- 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.
- Přepněte dotaz do návrhového zobrazení.
- Uložte výběrový dotaz a nechejte jej otevřený.
- Tento postup opakujte pro každý výběrový dotaz, který chcete přidat.
Teď, když jste vytvořili výběrové dotazy, je čas je sloučit. V tomto kroku vytvoříte sjednocovací dotaz zkopírováním a vložením SQL příkazů.
- Na kartě Vytvoření klikněte ve skupině Dotazů na tlačítko Návrh dotazu.
- 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 prázdná.
- Klikněte na kartu prvního výběrového dotazu, který chcete sloučit v sjednocovacím dotazu.
- Na kartě Domů klikněte na Zobrazit>zobrazení SQL.
- Zkopírujte
SQLpříkaz pro výběrový dotaz. Klikněte na kartu sjednocovacího dotazu, který jste začali vytvářet. - Vložte
SQLpříkaz výběrového dotazu na kartu objektu Zobrazení SQL sjednocovacího dotazu. - Odstraňte středník (
;) na konci příkazu výběrového dotazuSQL. - Stisknutím klávesy Enter přesuňte kurzor o jeden řádek dolů a pak pište
UNIONna nový řádek. - Klikněte na kartu dalšího výběrového dotazu, který chcete sloučit v sjednocovacím dotazu.
- Opakujte kroky 5 až 10 tohoto postupu, dokud nezkopírujete a nevložíte všechny
SQLpříkazy pro výběrové dotazy do okna Zobrazení SQL sjednocovacího dotazu. U posledního výběrového dotazu neodstraňujte středník ani zaSQLněj nic nezadávejte. - 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.
Tady jsou kroky potřebné k vytvoření tohoto příkladu:
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í).
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í.
Zkopírujte a vložte příkazy SQL z karet Dotaz1 a Dotaz2 na kartu Dotaz3. Nezapomeňte odebrat nadbytečný
UNIONstředník a přidat klíčové slovo. Potom můžete zkontrolovat výsledky v zobrazení Datový list.Přidejte do jednoho z dotazů klauzuli pro řazení a potom
ORDER BYvložte příkaz do sjednocovacího dotazu v zobrazení SQL. 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í.Poslední
SQL, která sloučí a seřadí jména pro tento příklad sjednocovacího dotazu, 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 SQL syntaxi, můžete napsat vlastní SQL příkaz sjednocovacího dotazu přímo v 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ů.
Sloučení tří nebo více tabulek nebo dotazů ve sjednocovacím dotazu
V příkladu v předchozí části, který používá databázi Northwind, se slučují 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 zobrazovalo, jestli je daná osoba interní zaměstnanec, dodavatel nebo zákazník, můžete místo názvu společnosti zahrnout pevnou hodnotu . Vypadá to SQL 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 můžete ještě zjednodušit, protože Access čte názvy výstupních polí jenom z prvního dotazu ve sjednocovacím dotazu. Tady se odebere 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];
Filtrování ve sjednocovacích dotazech
Ve sjednocovacím dotazu v Accessu je řazení povolené jenom jednou, ale každý dotaz můžete filtrovat jednotlivě. Když vyjdeme ze sjednocovacího dotazu v předchozím oddílu, měli bychom zmínit následující příklad, který filtruje každý dotaz přidáním WHERE klauzule.
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 |
Kombinování datových typů
Pokud jsou dotazy, které slučujete, 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 rozdělit pole Quantity (Množství) do dvou polí: Buy (Nákup) a Sell (Prodej). Dále předpokládejme, že pro pole bez hodnoty chcete použít pevnou hodnotu 0. Takhle bude vypadat SQL tento sjednocovací dotaz:
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 budete chtít, aby pole s nulovými hodnotami byla prázdná? Přidáním klíčového Null slova můžete změnit tak, aby místo nuly SQL nezobrazovalo nic, jak je znázorněno tady:
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 se pokusíte místo prázdných hodnot polí vložit prázdný řetězec? Tento SQL pokus může vypadat 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. Dá se to udělat pomocí tohoto výrazu:
IIf(False, 0, Null)
Podmínka, která se má zkontrolovat, Falseje nikdy True, takže výraz vždy vrátí Null. Access však stále vyhodnocuje obě možnosti výstupu a zachází s výstupem jako s číselným 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;
Druhý dotaz upravovat nemusíte.
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 definovali. Samozřejmě nechcete, aby výstup tohoto dotazu kolidoval s výsledky. Trik, jak se tomu vyhnout, spočívá v zahrnutí klauzule WHERE na False:
WHERE False
To je malý trik. Protože podmínka je vždy nepravdivá, dotaz 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
V tomto příkladu vrátí sloučený dotaz v databázi Northwind 100 záznamů, zatímco dva jednotlivé dotazy vrátí 58 a 43 záznamů, což je celkem 101. K tomuto rozdílu dochází, protože dva záznamy nejsou jedinečné. Informace o řešení tohoto scénáře pomocí příkazu UNION ALL najdete v UNION ALLtématu Práce s jedinečnými záznamy ve sjednocovacích dotazech pomocí příkazu UNION ALL.
Přidání součtů ve sjednocovacím dotazu
Zvláštní použití sjednocovacího dotazu spočívá ve 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.
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];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 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))Přepněte do zobrazení Datový list a měl by se zobrazit jenom jeden záznam:
MaxOfDate Received SumOfQuantity 4/5/2006 510 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];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.
Práce s jedinečnými záznamy ve sjednocovacích dotazech pomocí příkazu UNION ALL
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 tento sjednocovací dotaz SQL tak, aby obsahoval Product ID = 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 samozřejmě nevrátí dvojnásobek celkového množství.
Tento výsledek vidíte, protož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 můžete vidět, že není zahrnuté pole Kód objednávky nákupu a že dvě pole netvoří dva jedinečné záznamy.
Pokud chcete zahrnout všechny záznamy, použijte UNION ALL místo UNION v .SQL To bude mít s největší pravděpodobností vliv na řazení výsledků, takže možná budete chtít zahrnout ORDER BY i klauzuli určující pořadí řazení. Tady je upravená SQL na základě 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 |
Použití sjednocovacího dotazu k filtrování záznamů ve formuláři pomocí ovládacího prvku pole se seznamem
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.
Vytvořte jednoduchý výběrový dotaz pomocí následující
SQLsyntaxe:SELECT Employees.City, Employees.City AS Filter FROM Employees;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 Když se podíváte na tyto výsledky, možná vám nebudou připadat nějak zvlášť užitečné. Rozbalte ale dotaz a změňte ho na sjednocovací pomocí následujícího
SQLpostupu:SELECT Employees.City, Employees.City AS Filter FROM Employees UNION SELECT "<All>", "*" AS Filter FROM Employees ORDER BY City;Přepněte do zobrazení Datový list a měly by se zobrazit následující výsledky:
City Filter <Vše> * Bellevue Bellevue Kirkland Kirkland Redmond Redmond Seattle Seattle Access sloučí devět záznamů zobrazených výše s pevnými hodnotami <polí Všechny> a *. Vzhledem k tomu, že tato sjednocovací klauzule
UNION ALLneobsahuje , vrátí Access jenom jedinečné záznamy. To znamená, že každé město se vrátí jenom jednou s pevnými identickými hodnotami.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.
FilterDo vlastnosti samotného formuláře potom můžete přidat kód podobný následujícímu, který aktivuje filtr formuláře pomocí hodnoty vybrané v ovládacím prvku pole se seznamem:Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'" Me.FilterOn = TrueUživatel formuláře pak může filtrovat záznamy ve formuláři podle názvu konkrétního města nebo vybrat Vše> a zobrazit <všechny záznamy pro všechna města.