2 Dotazy Příklad 1 Access 2007 OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová TÉMA: Výběrové dotazy Sekretářka společnosti „Naše zahrada“ pracuje s rozsáhlými tabulkami. Pro přehlednější práci s daty používá řazení a filtraci dat na základě různých kritérií. Dále potřebuje získat určité vypočítané údaje. Vše uložila ve formě dotazů pro pozdější použití. Zadání: Otevřete databázi VyberoveDotazy.accdb. 1. Pomocí průvodce vytvořte výběrový dotaz Seznam zákazníků, který zobrazí z tabulky Zákazníci pouze pole: Jméno, Příjmení, Město a Země. 2. V návrhovém zobrazení vytvořte výběrový dotaz Seznam prodejců, který zobrazí z tabulky Prodejci pouze pole: Prodejci, Ulice, Město a PSČ. a. Nastavte v dotazu filtr, který zobrazí pouze prodejce z Ostravy a Karviné. b. Záznamy v dotazu seřaďte podle data narození, pole nezobrazujte. 3. V návrhovém zobrazení vytvořte výběrový dotaz Výrobky nad 50 Euro, který zobrazí z tabulky Typy kategorií pole NázevKategorie a z tabulky Výrobky pole NázevVýrobku a CenavKč. a. Přidejte do dotazu vypočítané pole CenavE, které vypočítá cenu v eurech (použijte kurz 28 Kč/1€). Pro pole nastavte titulek na Cena v EUR a formát na Euro. b. Do dotazu přidejte další vypočítané pole Sleva, které zobrazí text ano, pokud bude CenavE > 200,00 €, jinak zobrazí text ne. c. Přidejte do dotazu podmínku, která zobrazí pouze záznamy, kde hodnota pole CenavE > 50,00 €. 4. V návrhovém zobrazení vytvořte výběrový dotaz Souhrny prodejů, který zobrazí z tabulky Prodejci pole Město a Prodej. a. Vytvořte souhrn, který zobrazí objem Prodejů za jednotlivá Města (použijte součet), poli přidejte titulek Objem prodejů. b. Do dotazu přidejte další agregační funkci pro zobrazení počtu Prodejů ve Městě, poli přidejte titulek Počet prodejů. 5. Zavřete databázi. Řešení 2 Dotazy Příklad 1 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 jednoduchým dotazem, tlačítko OK. V dialogovém okně Průvodce jednoduchým dotazem zvolit ze seznamu tabulku dle zadání, označit postupně pole dle zadání a pomocí tlačítka >> přesunout pole do dotazu, tlačítko Další. Změnit název dotazu dle zadání, tlačítko Dokončit. Bude vytvořen výběrový dotaz, který bude zobrazovat pouze vybraná pole z tabulky. Pozn. Pokud byla v posledním kroku zatržena položka Otevřít dotaz pro zobrazení informací, bude automaticky zobrazen výsledek dotazu v zobrazení datového listu. Záznamy zůstávají stále uloženy v tabulce, pouze se zobrazí při spuštění dotazu. Dotaz lze spustit také poklepáním na něj (nebo kliknutím pravým tlačítkem myši na dotaz v Navigačním podokně/Otevřít). 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. Do jednotlivých sloupců návrhové mřížky umístit z tabulky pole dle zadání – uchopit myší pole, přetáhnout na sloupec (nebo dvakrát kliknout na pole v tabulce). Pozn. Výsledek dotazu si lze již nyní prohlédnout v zobrazení datového listu – karta Dotazy – nástroje/záložka Návrh/skupina Výsledky/šipka příkazu Zobrazení/Datového listu. Zpět do návrhového zobrazení se lze přepnout obdobným způsobem. a. Nastavení filtru – ve sloupci Město vepsat do řádku Kritéria: text Ostrava, text Karviná vepsat do řádku nebo: ve stejném sloupci. Pozn. Pro filtrování textu lze použít také zástupné znaky (*,?). b. Nastavení řazení záznamů – přidat přetažením myší další pole dle zadání, nastavit řazení v seznamu Řadit v příslušném sloupci, odtrhnout položku Zobrazit. Pozn. Některá pole mohou být pouze pomocná, nemusí být proto v dotazu zobrazena. 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). 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. Do dotazu budou přidány obě tabulky. Tabulky jsou již propojeny pomocí relace, není tedy třeba relaci vytvářet. Pozn. Pokud dotaz zobrazuje data z více tabulek, musí být tyto propojeny pomocí relace. Do jednotlivých sloupců návrhové mřížky umístit z tabulek pole dle zadání – uchopit myší pole, přetáhnout na sloupec (nebo dvakrát kliknout na pole v tabulce). a. Přidání vypočítaného pole: vytvořit pole pomocí Tvůrce výrazů – kliknout do řádku Pole v prvním prázdném sloupci, karta Dotazy – nástroje/záložka Návrh/skupina 2 Dotazy Příklad 1 Access 2007 OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová Nastavení dotazu/příkaz Tvůrce (nebo kliknout pravým tlačítkem myši v prázdném sloupci na řádek Pole:/Sestavit…). Spustí se Tvůrce výrazů. Vepsat výraz [Výrobky]![CenavKč]/28 (ve spodní části okna zobrazit seznam Tabulky/Výrobky/CenavKč, tlačítko Vložit, připsat /28), tlačítko OK. Kliknout mimo políčko (automaticky se před vytvořený výraz vloží název pole Výraz 1). Pozn. Výraz lze do pole ve sloupci vepsat také přímo ručně. Přepsat název pole Výraz 1 na název pole dle zadání: CenavE. Nastavení titulku a formátu pole – kliknout do příslušného sloupce v návrhové mřížce (na příslušné pole), karta Návrh – nástroje/záložka Návrh/skupina Zobrazit či skrýt/příkaz Seznam vlastností. V pravé části se zobrazí podokno úloh Seznam vlastností. Do pole Titulek vepsat titulek dle zadání, v poli Formát zvolit ze seznamu položku Euro. Pozn. Název pole bývá většinou bez mezer a diakritiky, do titulku lze použít i češtinu a mezery. Výsledek dotazu si lze již nyní prohlédnout v zobrazení datového listu – karta Dotazy – nástroje/záložka Návrh/skupina Výsledky/příkaz Zobrazení/Datového listu. Zpět do návrhového zobrazení se lze přepnout obdobným způsobem. Uložit dotaz – tlačítko Uložit na panelu nástrojů Rychlý přístup. b. Přidání vypočítaného pole: vytvořit pole pomocí Tvůrce výrazů – kliknout do řádku Pole: v prvním prázdném sloupci, karta Dotazy – nástroje/záložka Návrh/skupina Nastavení dotazu/příkaz Tvůrce (nebo kliknout pravým tlačítkem myši v prázdném sloupci na řádek Pole:/Sestavit…). Spustí se Tvůrce výrazů. Vepsat výraz IIf([CenavE]>200;"ano";"ne"). Postup: ve spodní části okna zobrazit seznam Funkce/Běh programu/IIf, tlačítko Vložit. Kliknout v horním okně na text <>, ve spodní části okna zobrazit seznam Dotazy/Výrobky nad 50 Euro/CenavE, tlačítko Vložit. Pozn. Aby se v dotazu Výrobky nad 50 Euro zobrazilo nově vytvořené pole CenavE, musí být dotaz po úkolu 3a uložen. Místo textu <> vepsat >200, místo textu <> vepsat „ano“, místo textu <> vepsat „ne“, tlačítko OK. Kliknout mimo políčko (automaticky se před vytvořený výraz vloží název pole Výraz 1). Přepsat název pole Výraz 1 na název dle zadání: Sleva. Zkontrolovat fungování dotazu viz bod a. c. Přidání podmínky: do pole Kritéria: ve sloupci CenavE vepsat >50. 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. 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 tabulku dle zadání, tlačítko Přidat, tlačítko Zavřít. Do jednotlivých sloupců návrhové mřížky umístit z tabulky obě pole dle zadání – uchopit myší pole, přetáhnout na sloupec (nebo dvakrát kliknout na pole v tabulce). a. Vytvoření souhrnu: karta Dotazy – nástroje/záložka Návrh/skupina Zobrazit či skrýt/příkaz Souhrny. Do návrhové mřížky bude přidán řádek Souhrn. V políčku Souhrn: pro sloupec Prodej nastavit agregační funkci Sum (pro součet). Nastavení titulku – označit sloupec, karta Dotazy – nástroje/záložka Nástroje/skupina Zobrazit či skrýt/příkaz Seznam vlastností. V podokně úloh Seznam vlastností nastavit vlastnost Titulek dle zadání. 2 Dotazy Příklad 1 Access 2007 OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová b. Do dalšího sloupce přetáhnout ještě jednou pole Prodej, v řádku Souhrn: nastavit agregační funkci Count (pro počet). Pro pole nastavit titulek obdobně jako v bodě a. 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). 5. 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í