4.6. Tvorba dotazu

Tato kapitola popisuje tvorbu dotazu (Query). Dotazy nám umožňují skládat informace z několika tabulek do potřebného tvaru, vybírat jen vhodné záznamy a řadu dalších operací. Dotazy umožňují i řadu jiných operací s tabulkami, opravy údajů, rušení záznamů apod. Jsou úzce vázány na jazyk SQL (Structured Query Language). Jsou vlastně jeho zapojením do prostředí programu Microsoft Access. I když se autoři uchýlili k některým odchylkám oproti jeho normě ANSI.

Vytváření dotazů probíhá v okně, jehož dolní část připomíná zobrazení výsledku dotazu (Datasheet), do jednotlivých sloupců umisujeme požadované položky z tabulek, kritéria výběru apod. tento postup je obvykle nazýván Query by Example - tvorba dotazu podle příkladu.

Popisem zobrazení výsledku dotazu se nebudeme zabývat, nebo je stejný jako práce s tabulkou.

Nejprve opět popíšeme menu při tvorbě dotazu (vynecháme činnosti Window a Help), pak se budeme věnovat konkrétním příkladům.

4.6.1. Menu File

Při prvním uložení objektu příkazem Save je uživatel dotázán na jméno objektu, není tedy nutno používat příkaz Save As.

4.6.2. Menu Edit

4.6.3. Menu View

4.6.4. Menu Query

4.6.5. Dotaz typu Select

Nejčastěji používaným dotazem bude bezesporu typ Select. Výsledkem dotazu je virtuální tabulka hodnot (nazývaná Datasheet, v programu také Dynaset). Vytvoří se z jedné nebo více zdrojových tabulek (nebo výsledků jiných dotazů) výběrem požadovaných položek a záznamů vyhovujících zadaným podmínkám. Tento typ vysvětlíme podrobně, u ostatních typů se na něj budeme odkazovat. Na obr. 25 je zobrazeno prostředí programu při tvorbě dotazu. V horní části obrazovky je zobrazen výsledek dotazu, který má stejný vzhled jako tabulka. V nadpisu okna vidíme také označení jeho typu (Select Query).

Okno pro tvorbu dotazu má dvě části. V horní je seznam tabulek, se kterými dotaz pracuje. Zařadíme je sem činností Query-Add Table z menu. Program nám nabídne seznam použitelných objektů a z nich postupně vybereme (Add) potřebné.

Obr. 25 Tvorba dotazu typu Select

Tabulky v okně vhodně rozmístíme a nastavíme vazby mezi nimi. Např. z tabulky TOsoby bude položka ID mít vazbu do tabulky TMísta na položku Osoba. Tím zajistíme, že výsledkem dotazu může být seznam osob i s jejich zaměstnáními. Obdobně vazba mezi tabulkami TKalendář a SMěsíce umožní, abychom datum svátku uváděli s českým názvem měsíce.

Vazbu mezi tabulkami nastavíme myší tak, že uchopíme (levým tlačítkem) položku z nadřízené tabulky a položíme na příslušnou položku podřízené tabulky. Bez použití myši nelze vazbu vytvořit. Vytvořená vazba má také přiřazen druh vazby (Join Properties). Nastavení vyvoláme stejnojmennou činností z menu nebo dvojím zmáčknutím myši (Double Click) na vazbě. Jsou možné tři druhy vazeb, jak jsme viděli na konci předchozí kapitoly.

V pravé části obr. 25 je otevřeno okno s globálními vlastnostmi dotazu. Jsou to :

Description popis dotazu. Vypisuje se do stavového řádku na dolním okraji okna.

Output All Fields zda na výstupu budou všechny položky zdrojových tabulek (Yes) nebo ne (No).

Top Values určuje, kolik záznamů má obsahovat výsledný dotaz, např. jen prvních 5, případně prvních 20%.

Unique Values určuje, zda budou zobrazovány jen unikátní hodnoty položek.

Unique Records určuje, zda se duplicitní záznamy zobrazí jednou (Yes) nebo vícekrát (No).

Run Permissions spuštění s parametry Owner's - autora, User's - uživatele.

Source Database název a cesta ke zdrojové databázi, aktuální databáze se zadává "(current)".

Source Connect Str způsob spojení s externí databází.

Record Locks způsob uzamčení záznamů (No Locks, All Records, Edited Record).

ODBC Timeout čas pro odpojení od ODBC.

V dolní části okna provádíme vlastní tvorbu dotazu. Jednotlivé sloupce přitom odpovídají sloupcům jednotlivých položek ve výsledku dotazu. Sloupec obsahuje několik řádků s určenými vlastnostmi. Jsou to :

Field název položky. Zadáme sem název položky, se kterou budeme v dotazu pracovat. Buď ji přeneseme myší z horní části z příslušné tabulky nebo vybereme z nabídky možných položek (tlačítko pro její vyvolání se objeví na pravé straně, jakmile se kurzor přesune do tohoto pole). Můžeme zadat také znak hvězdička "*", ten zastupuje všechny položky zvolené tabulky. Pokud jméno položky není jednoslovné, uzavírá se do hranatých závorek. Jako položka může vystupovat také jakákoliv operace s položkami. Jejich výsledkem pak vzniká virtuální položka, která není uložena v žádné tabulce. Například věk osoby určíme jako výsledek operace Format(Now()-[Datum],"yy"). Tato operace může obsahovat také funkce definované uživatelem v programu (Module). Pokud s touto položkou chceme dále pracovat (v programu, v jiných dotazech apod.), musíme jí určit jméno. To zapíšeme před její definici a oddělíme dvojtečkou, např. : Věk : Format(Now()-[Datum],"yy").

Table jméno tabulky odkud je položka. Tento řádek se zapojuje/odpojuje činností menu View-Table Names. Doporučujeme ho zapojit, jinak může docházet k záměnám stejnojmenných položek. Např. pokud zapisujeme jméno položky z klávesnice, bude zapsána položka z tabulky, ve které je v horní části okna kurzor. Pokud v dotazu použijete položky stejného názvu pocházející z různých tabulek, bude druhá v pořadí rozšířena o název tabulky, nebo identifikátor položky (název) musí být jednoznačný. Pokud se chcete vyvarovat nejednoznačnosti v názvech položek, změňte v dotazu jejich název : Jméno : [Jméno].

Total součet. Tento řádek se zapojuje/odpojuje činností menu View-Totals. Vlastnost v poli se vybírá z nabídky možných vlastností. Např. Group By znamená, že výsledek dotazu bude seřazen do skupin stejných hodnot v této položce, Where znamená, že následující podmínka (Criteria) bude použita pro výběr záznamů pro zobrazení, použití funkce (Sum, Avg apod.) znamená, že bude zobrazen pouze výsledek funkce s obsahem položky. Výsledná položka dostane implicitně nový název např. součet v položce Věk bude SumOfVěk. Pokud položce určíte vlastní nový název, bude akceptován.

Sort setřídění. Určuje, zda výsledek dotazu bude setříděn podle hodnot této položky. Můžeme volit setřídění vzestupné (Ascending) nebo sestupné (Descending). Pokud chceme třídit podle více položek (např. Příjmení a Jméno), pak pořadí priorit pro třídění je v pořadí zadávání těchto vlastností jednotlivým položkám. Access má tendenci zařadit položky podle kterých se třídí jako první (vlevo), právě v pořadí jejich priorit. Pokud nemáme jistotu v prioritách řazení, můžeme nahlédnout do SQL definice dotazu, jak bude ukázána dále.

Show zobrazení. Určuje, zda položka bude zobrazena (křížek) nebo nebude zobrazena (prázdné pole). Můžeme tak definovat podmínky pro výběr záznamů, které se nebudou zobrazovat.

Criteria kritéria výběru. Sem zadáváme podmínku, kterou musí položka splňovat, aby byl záznam vybrán pro zobrazení. Tyto podmínky je možno kombinovat. Přitom platí, že podmínky umístěné v jednom řádku, v různých sloupcích jsou spojeny operátorem AND. Podmínky v jednom sloupci jsou spojeny operátorem OR. V podmínkách se mohou objevovat jakékoliv funkce, včetně funkcí vytvořených uživatelem v programu (Module). Mohou se tady objevovat také proměnné parametry, které definujeme v činnosti menu Query-Parameters. Na obr. 26 vidíme okno, které se přitom otevře. Sem zadáme název parametru (je to libovolný text, který bude použit jako hlášení při zadávání hodnoty parametru) a typ proměnné z nabídky, která je na obrázku rozvinuta. Pokud má parametr více slov musíme ho při odkazu uzavřít do hranatých závorek (stejně jako víceslovný název položky). Při provedení dotazu se objeví požadavek pro zadání hodnoty parametru, viz obr. 27. Podle nastavené hodnoty se pak provede dotaz.

Při porovnávání můžeme využít řady operátorů, např :

Like "K*" vyhledá všechny záznamy, jejichž zvolená položka začíná hodnotou "K",

"J?rka" vyhledá všechny záznamy, jejichž zvolená položka má hodnotu "Jirka", "Jarka", apod.,

In ("Jan", "Jiří", "Karel") vyhledá všechny záznamy, jejichž zvolená položka má hodnotu uvedenou v seznamu,

In ([TKalendář].[Jméno]) tímto seznamem mohou být také hodnoty položky.

Kromě uvedeného nastavení mají jednotlivé položky ještě několik vlastností, které se nastavují v samostatném okně (Field Properties) :

Description popis položky, zobrazuje se ve stavovém řádku při práci s položkou.

Format formát zobrazení dat v položce.

Input Mask vstupní maska.

Caption název položky, který bude vypsán v záhlaví tabulky.

Význam vlastností je stejný, jako u položek tabulky, kde byly také popsány.

Obr. 26 Okno pro zadání parametrů dotazu

Obr. 27 Okno pro zadání hodnoty parametru dotazu

Celý dotaz má své vyjádření v SQL. Pokud se nám lépe pracuje přímo v SQL, můžeme tam také dotazy vytvářet. Činností v menu View-SQL vyvoláme jednoduchý editor příkazu SQL, viz obr. 28.

Obr. 28 Editor příkazů jazyka SQL

Jako příklad uvedeme dotaz, který vytvoří seznam osob z tabulky TOsoby, k nim připojí jejich pracoviště z tabulky TMísta a vypočte počet celých let odpracovaných u jednotlivých zaměstnavatelů.

V SQL bude dotaz vypadat následovně :

4.6.6. Dotaz typu Crosstab

Typ Crosstab umožňuje vytvořit výstup ve tvaru sestavné tabulky. Ve vlastnostech položek přibude jeden řádek :

Crosstab v nabídce je několik možných hodnot

Row Heading hodnoty položky určí řádky výsledné tabulky. Alespoň jedna položka musí být takto označena.

Column Heading hodnota položky určí sloupce výsledné tabulky. Právě jedna položka musí být takto označena.

Value hodnoty položky zaplní vnitřek tabulky. Právě jedna položka musí být takto označena. Navíc musí mít tato položka uvedenu výpočetní operaci, která se provede s jejími hodnotami (SUM, AVG apod.). Do pole výstupní tabulky se umístí výsledek operace.

Ukažme si použití tohoto typu na řešeném příkladu. Máme zdrojovou tabulku s údaji o osobách a jejich zaměstnáních. Je nepodstatné, že vznikla dotazem typu Select. Z této tabulky chceme vytvořit sumář údajů, kdo u kterého zaměstnavatele odpracoval kolik let.

zdrojová tabulka [QOsoba místo doba] vzniklá spojením tabulek TOsoby a TMísta dotazem:

Titul	Jméno	Příjmení	Datum	Místo	Doba
Bc.	Kamil	Magnussen	3.12.1971
Ing.	Jan	Jícha	1.6.1945	Podzemní s.	2
MUc.	Milada	Kanyzová	7.2.1965	Kuželkárna	12
MUc.	Milada	Kanyzová	7.2.1965	Let Letov	2
RNDr.	Ignác	Herman	16.2.1955	Podzemní s.	11

V dotazu nastavíme vlastnost Crosstab pro jednotlivé položky podle obr. 29.

Obr. 29 Definice dotazu typu Crosstab

V SQL bude mít dotaz tvar :

Výsledkem bude virtuální tabulka následujícího vzhledu. Všimněte si především sloupce, který obsahuje hodnoty, které v položce Místo (použité jako zdroj sloupců) neměly uvedeny žádné hodnoty.

Obr. 30. Výsledek dotazu typu Crosstab

4.6.7. Dotaz typu Make Table

Dotaz, který vytvoří novou tabulku má stejný vzhled jako dotaz typu Select. Pouze výsledkem dotazu není virtuální tabulka (Dynaset), ale fyzická tabulka (Table). Typ se liší pouze vlastnostmi (Query Properties), které obsahují navíc název cílové tabulky, viz obr. 31. Při provedení dotazu nebude zobrazena výsledná tabulka, ale jen hlášení, kolik záznamů bylo zkopírováno do nové tabulky, viz obr. 32. Toto hlášení lze však zablokovat nastavením globálních parametrů (Options).

Obr. 31 Dotaz pro tvorbu nové tabulky

Obr. 32 Hlášení při provedení dotazu s vytvořením nové tabulky

V SQL má dotaz tvar :

4.6.8. Dotaz typu Update

Dotaz typu Update slouží ke změně obsahu položek v tabulce. Ve vlastnostech položek se objevuje nový řádek :

Update to určuje novou hodnotu, kterou přiřadíme vybraným záznamům.

Např. v tabulce osob chceme změnit titul ze zadané hodnoty na novou zadanou hodnotu. Na obr. 33 vidíme zadání dotazu (je vynechán řádek Totals, který nemá smysl). Při jeho provedení (Run) jsme informováni, kolik záznamů bylo upraveno.

V SQL bude mít dotaz tvar :

Obr. 33 Definice dotazu typu Update

4.6.9. Dotaz typu Append

Dotaz typu Append slouží k výběru záznamů z jedné tabulky a jejich uložení do nových záznamů jiné tabulky. Název cílové tabulky se zadává ve vlastnostech dotazu (Query Properties). Ve vlastnostech položek se objevuje nový řádek :

Append To - určuje název položky cílové tabulky, do které se má uložit hodnota ze zdrojové tabulky.

Např. v tabulce [TCelkový přehled] chceme doplnit záznamy o osobách, které přibyly do tabulky TOsoby. Na obr. 34 vidíme zadání dotazu (je vynechán řádek Totals, který nemá smysl). Do jednotlivých sloupců dotazu přitom musíme zadat názvy všech položek zdrojové tabulky, které mají být přeneseny. Není vhodné používat hvězdičku "*" pro přenesení všech položek. Přenášela by se také hodnota počítadla (typu Counter), která by způsobila hlášení nekompatibility hodnot. Při provedení dotazu (Run) jsme informováni, kolik záznamů bylo připojeno.

V SQL bude mít dotaz tvar :

Obr. 34 Definice dotazu typu Append

4.6.10. Dotaz typu Delete

Dotaz typu Delete slouží k rušení záznamů v tabulce. Ve vlastnostech položek se objevuje nový řádek :

Delete určuje podmínku, podle které se budou vybírat záznamy ke zrušení, implicitně WHERE.

Např. v tabulce [TCelkový přehled] chceme zrušit všechny záznamy. Na obr. 35 vidíme zadání dotazu (je vynechán řádek Totals, který nemá smysl). Do jednotlivých sloupců dotazu přitom zadáváme jen názvy těch položek, podle kterých budeme vyhledávat. Při provedení dotazu (Run) jsme informováni, kolik záznamů bylo zrušeno.

V SQL bude mít dotaz tvar :

Obr. 35 Definice dotazu typu Delete