2 Dotazy Příklad 2 Access 2007 OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová TÉMA: Křížové dotazy Sekretářka společnosti „Naše zahrada“ potřebuje vytvořit souhrny informací dle různých pohledů a kritérií. Jelikož se předpokládá, že jde o souhrny často používané, uloží si je pomocí křížových dotazů. Zadání: Otevřete databázi KrizoveDotazy.accdb. 1. Vytvořte pomocí průvodce křížový dotaz Zákazníci dle regionů, který zobrazí počty zákazníků dle jednotlivých zemí v konkrétních regionech. a. V hlavičkách řádků zobrazte pole Země z tabulky Zákazníci. b. V hlavičkách sloupců zobrazte pole Region z tabulky Zákazníci. c. Pro určení počtu zákazníků použijte pole KódZákazníka z tabulky Zákazníci, nechte zahrnout součty řádků. 2. Vytvořte v návrhovém zobrazení křížový dotaz Objednávky dle regionů, který zobrazí počty objednávek dle jednotlivých regionů a zemí příjemce. a. V hlavičkách řádků zobrazte pole ZeměPříjemce z tabulky Objednávky. b. V hlavičkách sloupců zobrazte pole RegionPříjemce z tabulky Objednávky. c. Pro určení počtu objednávek použijte pole KódObjednávky z tabulky Objednávky. d. Do hlaviček řádků přidejte pole pro celkový součet počtů objednávek dle jednotlivých zemí, poli změňte Titulek na Počet objednávek. 3. Vytvořte křížový dotaz Objem objednávek dle kategorií, který zobrazí objem objednávek pro jednotlivé kategorie výrobků dle zemí a regionů. a. V hlavičkách řádků zobrazte pole ZeměPříjemce a RegionPříjemce z tabulky Objednávky. b. V hlavičkách sloupců zobrazte pole NázevKategorie z tabulky Kategorie. c. Pro zjištění objemu objednávek (součet) vytvořte vypočítané pole na základě polí JednotkováCena, Množství a Sleva z tabulky RozpisObjednávek. Poli nastavte formát měna. d. Výsledky dotazu seřaďte dle polí ZeměPříjemce a RegionPříjemce. 4. Zavřete databázi. Řešení 2 Dotazy Příklad 2 Access 2007 OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová Řešení: Po spuštění aplikace MS Access (tlačítko Start/Všechny programy/Microsoft Office/Microsoft Office Access 2007) bude zobrazeno podokno Vítá vás aplikace Microsoft Office Access. V pravé části okna Otevřít aktuální databázi vybrat odkaz Další… a najít soubor dle zadání (nebo v již otevřené aplikaci MS Access tlačítko Office/Otevřít). 1. Karta Vytvořit/skupina Jiné/příkaz Průvodce dotazem/v dialogovém okně Nový dotaz zvolit položku Průvodce křížovým dotazem, tlačítko OK. V dialogovém okně Průvodce křížovým dotazem zvolit ze seznamu tabulku dle zadání, tlačítko Další. a. Označit dle zadání pole, které dle kterého budou vytvořeny skupiny v hlavičkách řádků a pomocí tlačítka >> je přesunout do dotazu, tlačítko Další. b. Označit dle zadání pole, které dle kterého budou vytvořeny skupiny v hlavičkách sloupců a pomocí tlačítka >> je přesunout do dotazu, tlačítko Další. c. Označit dle zadání pole, dle kterého se budou počítat souhrnné údaje pro jednotlivé skupiny, v části Funkce zvolit funkci Počet, nechat zatrženo Ano, zahrnout součty řádků, tlačítko Další. Změnit název dotazu dle zadání, tlačítko Dokončit. Pokud byla v posledním kroku zatržena položka Otevřít dotaz, bude automaticky zobrazen výsledek dotazu v zobrazení datového listu. Takto vytvořený dotaz lze kdykoliv upravit v návrhovém zobrazení viz bod 2. Zavřít dotaz tlačítkem Zavřít (X v pravém horním rohu okna dotazu). 2. Karta Vytvořit/skupina Jiné/příkaz Návrh dotazu/bude vytvořen prázdný dotaz. Přidat do dotazu tabulku, ze které budou vybírány záznamy - v dialogovém okně Zobrazit tabulku označit tabulku dle zadání, tlačítko Přidat, tlačítko Zavřít. Do dotazu bude přidána tabulka. Změnit typ dotazu – karta Dotazy – nástroje/záložka Návrh/skupina Typ dotazu/příkaz Křížový dotaz. Změní se názvy řádků v návrhové mřížce. a. Do návrhové mřížky umístit (např. přetažením myší) pole ZeměPříjemce, v řádku Křížová tabulka: zvolit pro toto pole položku hlavička řádku. Pozn. Do řádku Souhrn: se automaticky vloží položka Seskupit, protože podle tohoto pole budou vytvořeny skupiny. b. Do návrhové mřížky umístit (např. přetažením myší) pole RegionPříjemce, v řádku Křížová tabulka: zvolit pro toto pole položku hlavička sloupce. Pozn. Do řádku Souhrn: se automaticky vloží položka Seskupit, protože podle tohoto pole budou vytvořeny skupiny. c. Do návrhové mřížky umístit (např. přetažením myší) pole KódZákazníka, v řádku Křížová tabulka: zvolit pro toto pole položku hodnota. V řádku Souhrn: zvolit pro toto pole položku Count (počet). d. Do dalšího sloupce návrhové mřížky umístit ještě jednou pole ZeměPříjemce, v řádku Křížová tabulka: zvolit pro toto pole položku hlavička řádku. V řádku Souhrn: zvolit položku Count (počet). Změna titulku – karta Dotazy – nástroje/záložka návrh/skupina Zobrazit či skrýt/příkaz Seznam vlastností, do příslušného pole vepsat titulek dle zadání. Uložení dotazu – tlačítko Office/Uložit (nebo tlačítko Uložit na panelu nástrojů Rychlý přístup), vepsat název dotazu dle zadání. Zobrazení výsledku dotazu - karta Dotazy – 2 Dotazy Příklad 2 Access 2007 OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová nástroje/záložka Návrh/skupina Výsledky/příkaz Zobrazení/Datového listu. Zavřít dotaz tlačítkem Zavřít (X v pravém horním rohu okna dotazu). 3. Karta Vytvořit/skupina Jiné/příkaz Návrh dotazu/bude vytvořen prázdný dotaz. Přidat do dotazu tabulky, ze kterých budou vybírány záznamy - v dialogovém okně Zobrazit tabulku označit postupně tabulky dle zadání, tlačítko Přidat, tlačítko Zavřít. Změnit typ dotazu – karta Dotazy – nástroje/záložka Návrh/skupina Typ dotazu/příkaz Křížový dotaz. Změní se názvy řádků v návrhové mřížce. a. Do návrhové mřížky umístit pole ZeměPříjemce a RegionPříjemce z tabulky objednávky, u obou polí v řádku Křížová tabulka: zvolit položku hlavička řádku. Do řádku Souhrn: se automaticky vloží položka Seskupit. b. Do návrhové mřížky umístit pole NázevKategorie z tabulky Kategorie, v řádku Křížová tabulka: zvolit pro toto pole položku hlavička sloupce. Do řádku Souhrn: se automaticky vloží položka Seskupit. c. Do dalšího sloupce vložit vypočítané pole pomocí Tvůrce výrazů – kliknout do řádku Pole: ve sloupci, karta Dotazy – nástroje/záložka Návrh/skupina Nastavení dotazu/příkaz Tvůrce (nebo kontextová nabídka pole/Sestavit…/spustí se Tvůrce výrazů. Vytvořit výraz: Sum([Rozpis objednávek+!*JednotkováCena+**Rozpis objednávek+!*Množství+*(1-*Rozpis objednávek+!*Sleva+)). Postup pro vytvoření výrazu: ve spodní části okna vložit funkci Sum – zobrazit seznam Funkce/Vestavěné funkce/Agregační SQL/Sum, tlačítko Vložit, označit výraz <>, zobrazit seznam Tabulky/Rozpis objednávek/Jednotková cena, tlačítko Vložit, vepsat znak *, ve spodní části okna kliknout na Množství, tlačítko Vložit, vepsat znaky (1-, ve spodní části okna kliknout na Sleva, tlačítko Vložit, vepsat znak ), tlačítko OK. Vypočítané pole bude vytvořeno. V řádku Souhrn: nastavit položku Výraz, v řádku Křížová tabulka: zvolit hodnota. Jelikož je ve vytvořeném výrazu použito pole z tabulky RozpisObjednávek, musíme ji do dotazu také přidat – karta Dotazy – nástroje/záložka Návrh/skupina Nastavení dotazu/příkaz Zobrazit tabulku/označit tabulku, tlačítko Přidat. Pokud v dotazu používáme data z více tabulek, musí být tyto provázány pomocí relací. Tabulka Kategorie však propojená na ostatní tabulky není. Přidáme tedy do dotazu ještě tabulku Výrobky, aby bylo zajištěno propojení dat v tabulkách (i přesto, že data z tabulky Výrobky v dotazu nebudou použita). Pozn. Způsob propojení tabulek lze zjistit na kartě Databázové nástroje/skupina Zobrazit či skrýt/příkaz Vztahy. Nastavení formátu pro vypočítané pole – kliknout do příslušného sloupce, karta Dotazy – nástroje/záložka Návrh/skupina Zobrazit či skrýt/příkaz Seznam vlastností/v podokně Seznam vlastností nastavit formát dle zadání. d. U příslušných polí nastavit způsob řazení v řádku Řadit. Uložení dotazu – tlačítko Office/Uložit (nebo tlačítko Uložit na panelu nástrojů Rychlý přístup), vepsat název dotazu dle zadání. Zobrazení výsledku dotazu - karta Dotazy – nástroje/záložka Návrh/skupina Výsledky/příkaz Zobrazení/Datového listu. Zavřít dotaz tlačítkem Zavřít (X v pravém horním rohu okna dotazu). 4. Zavřít databázi tlačítkem Office/Zavřít databázi (nebo tlačítko Zavřít – x v pravém horním rohu okna). Zpět na zadání