Téma 2.4
Dotazy – tvorba nových polí (vypočítané pole) Pomocí dotazu lze také vytvářet nová pole, která mají vazbu na již existující pole v databázi. Vznikne tedy nový sloupec, který se počítá podle vzorce. Například můžeme vytvořit nové pole zobrazující [Celkovou cenu], která bude sestavena vynásobením již existujících polí [cena za kus] a [počet kusů]. Tyto výpočty můžeme provést nejen u číselných ale i datových a textových typů dat. Výrazy - vzorce lze sestavovat ručně nebo pomocí Tvůrce výrazů.
Řešený příklad č. 241 Zadání: V databázi priklady_prevedeno.mdb vytvořte dotaz s názvem Tržba, který bude počítat tržbu (celkovou cenu) prodaného zboží ve dnech od 1. 2. 2000 do 28. 2. 2000. Obrazová ukázka řešení: Návrhové zobrazení dotazu:
Výsledek dotazu – zobrazení datového listu:
KIT PEF CZU v Praze
Stránka 1
Postup řešení: Otevřete databázi priklady_prevedeno.mdb - dvojklikem na jejím názvu. Pokud je v horní části databáze zobrazeno upozornění zabezpečení, kde byl zakázán určitý obsah, v databázi nastavte možnost – Povolit tento obsah. Před tvorbou dotazů je vždy nutné zkontrolovat, zda jsou u tabulek nastaveny relace. Pokud by relace nastaveny nebyly, musíme je vytvořit. Na kartě Databázové nástroje zvolte skupinu Zobrazit či skrýt a vyberte možnost Vztahy. Na kartě Vytvořit zvolte skupinu Jiné a vyberte možnost Návrh dotazu:
Otevře se vám návrhové zobrazení Dotaz 1, kde v dialogovém okně zvolíte a přidáte tabulky, z jejichž polí bude dotaz sestaven. V našem případě jsou to 3 tabulky Prodej, Výrobky, Výrobci.
Ze zobrazených tabulek vybíráme potřebná pole, která budou tvořit strukturu našeho dotazu. Jestliže chceme, aby dotaz ukazoval přehled tržeb výrobků prodaných za určité období, je nutno vybrat - Co se kdy prodalo a za kolik (Název výrobku, Datum prodeje, případně Název výrobce). Pole pro výpočet tržby sestavíme následovně. Do dalšího prázdného sloupce na řádek Pole napíšeme výraz:
Tržba:[Cena]*[Množství] Pravidla: Název budoucího pole , oddělovač dvojtečka , název pole v hranatých závorkách, * součin
KIT PEF CZU v Praze
Stránka 2
Výrazy můžete zapisovat nebo tvořit rovněž pomocí Tvůrce výrazů , kterého najdeme na kartě Návrh ve skupině Nastavení dotazu. (Při jeho spuštění musíme stát rovněž kurzorem na řádku Pole u prázdného sloupce). Pomocí tvůrce potřebná pole najdeme výběrem tabulky (1. sloupec – poklep na objekt Tabulky), kde pak v 2. sloupci vidíme její strukturu (seznam polí). Poklepem na zvoleném poli se jeho název včetně názvu tabulky oddělené vykřičníkem, vloží do výrazu v horní části dialogového okna Tvůrce výrazů (Stejným postupem vybíráme další objekty, funkce a operátory, potřebné pro sestavení celého výrazu). Kompletní výraz vložíme do řádku Pole potvrzením tlačítka OK.
Pro omezení prodeje na dny od 1. 2. 2000 do 28. 2. 2000 zapíšeme na řádek Kritéria ve sloupci Datum prodeje následující podmínku: Between #1.2.2000# And #28.2.2000# Výsledek dotazu zkontrolujeme v zobrazení datového listu. Dotaz uložíme pod názvem Tržba.
A jen pro doplnění – dotaz naleznete i pod názvem Příklad 18.
KIT PEF CZU v Praze
Stránka 3
Příklady výrazů - Vše zapisujeme do prázdného sloupce na řádek Pole! Datový typ Číslo, Měna, Datum Vzorový zápis:
Název výrazu: Název funkce ([Název tabulky]![Název pole]) Poznámka: Pokud ve výrazu použijeme funkci, tak má své argumenty vždy v kulaté závorce. V následující tabulce je uveden přehled agregačních funkcí, které můžeme používat při tvorbě dotazů. FUNKCE
POPIS
PRO POUŽITÍ S DATOVÝMI TYPY
Průměr (Avg)
Vypočítá průměrnou hodnotu pro sloupec. Sloupec musí obsahovat číselná data, měnové údaje nebo datum a čas. Hodnoty Null jsou ignorovány.
Číslo, Měna, Datum a čas
Počet (Count)
Vrátí počet položek ve sloupci.
Všechny datové typy s výjimkou opakujících se komplexních skalárních dat, jako jsou například sloupce s vícehodnotovými seznamy. Další informace o vícehodnotových seznamech naleznete v článcích Průvodce pro pole s více hodnotami a Přidání a změna vyhledávacího pole umožňujícího uložení více hodnot.
Maximum (Max)
Vrátí položku s nejvyšší hodnotou. V případě textových dat je nejvyšší hodnotou poslední hodnota v abecedě – malá a velká písmena nejsou v aplikaci Access rozlišována. Hodnoty Null jsou ignorovány.
Číslo, Měna, Datum a čas, Text?
Minimum (Min)
Vrátí položku s nejnižší hodnotou. V případě textových dat je nejnižší hodnotou první hodnota v abecedě – malá a velká písmena nejsou v aplikaci Access rozlišována. Hodnoty Null jsou ignorovány.
Číslo, Měna, Datum a čas, Text?
Směrodatná odchylka (StDev)
Určí míru rozptýlení hodnot od průměrné (střední) hodnoty. Další informace o použití této funkce naleznete v článku Zobrazení součtů sloupců v datovém listu.
Číslo, Měna
Součet (Sum)
Sečte položky ve sloupci. Pracuje pouze s číselnými a měnovými údaji.
Číslo, Měna
Rozptyl (Var)
Určí statistický rozptyl všech hodnot ve sloupci. Tuto funkci lze použít pouze u číselných a měnových údajů. Pokud tabulka obsahuje méně než dva řádky, je vrácena hodnota Null. Další informace o funkci Rozptyl naleznete v článku Zobrazení součtů sloupců v datovém listu.
Číslo, Měna
Příklady: – – –
Název výrazu: Sum([Název tabulky]![Název pole]) – součet pole Průměr: Avg([Výrobky]![Cena]) – průměr pole Výraz: Count(([Objednávky]![ČísloObjednávky]) - počet hodnot v poli
Aritmetické operátory ve výrazech: + * / ^
součet rozdíl součin děleno umocnění
KIT PEF CZU v Praze
Stránka 4
Další výpočty: – –
Název výrazu: [Výrobky]![Cena] * 1,19 Název výrazu: [Datum vrácení]-[Datum vypůjčení]
- přičítá k poli 19% - zjistí délku výpůjčky ve dnech
Datový typ Text: –
Celé jméno: [Jméno] &” ” & [Příjmení] výraz pro počítanou položku dotazu – spojuje dva textové řetězce v jeden, (mezi uvozovkami je mezera, jinak by řetězce navazovaly bez mezery).
Datový typ datum: – – – –
Day ([datumová položka]) Month ([datumová položka]) Year ([datumová položka]) Day([datum zkoušky])>20
zobrazí pouze den z datumu zobrazí pouze měsíc z datumu zobrazí pouze rok z datumu zobrazí pouze den z datumu po dvacátém
Použití logické funkce IIF, která není omezena datovým typem – univerzální použití: –
Body: IIf([známka]=1;30;IIf([známka]=2;20;10)) Sloupec s názvem Body zobrazuje :
30 bodů pro známku 1 20 bodů pro známku 2 10 bodů pro známku 3
Tyto a další příklady počítaných polí v dotazu najdete v přiložených databázích:
KIT PEF CZU v Praze
priklady_prevedeno.mdb zkevidence.accdb
Stránka 5
