Pro snazší práci s daty v databázi slouží dotazy. V prostředí MS Access je možné data v tabulkách řadit a filtrovat podle zvolených kritérií, což nejjednodušší dotazy může nahradit, ale v okamžiku kdy potřebujete pracovat s více tabulkami naráz se bez dotazů neobejdete. Ve větších DB systémech jako je Oracle jsou dotazy základní možností přístupu k datům. V případě že vám nebudou stačit informace na této stránce, určitě se podívejte na návody na práci s dotazy.
Dotaz je požadavek na zobrazení určitých dat, případně i na jejich změnu. Běžně se zapisuje v jazyce SQL, ale v Access je možnost vytvořit ho pomocí návrhového zobrazení, stejně jako ostatní části databáze. Výsledkem dotazu je vždy tabulka s daty, která následně může sloužit jako zdroj dat pro další dotaz, formulář nebo sestavu. Umožňuje získat informace z jedné tabulky, z několika tabulek najednou, vypočítat nové hodnoty ze stávajících dat, počítat souhrnné hodnoty nebo hledat extrémy. Dá se také použít k automatizované změně většího množství dat nebo k jejich odstranění.
Za určitých okolností lze data v dotazu i upravovat. Chová se tedy jako pohled na data, výsledky v dotazu nejsou oddělené od dat v tabulce. Bez problémů to funguje pokud je dotaz jen nad jendou tabulkou, nebo nad dvěma tabulkami s vazbou 1:1. Pokud je nad tabulkami s vazbou 1:N, je možné upravovat jen sloupce které nemají nic společného s vazbou. Pozor, je možné upravit i pole, které je součástí kritéria dotazu, dokud ale nebude dotaz spuštěn znovu, nebude z něj výsledek odstraněn. Například pokud vytvořím dotaz vyhledávající lodě s výtlakem nad 10 000 tun a u jedné z nalezených lodí její výtlak změním na 5000 tun, pořád ji ve výsledných datech uvidím, pokud ale dotaz spustím podruhé, už tam nebude.
Tvorba dotazu
K tvorbě dotazu se dostanete v kartě "Vytvořit", máte k dispozici možnost využít průvodce nebo jen navrhnout dotaz v návrhovém zobrazení.
Průvodce snadno použijete sami, takže se podíváme na návrhové zobrazení. Hned po jeho otevření se zobrazí výběr tabulek, nad kterými dotaz chcete pokládat - pokud je mezi nimi vazba, Access sám zajistí, aby výsledky splňovaly její podmínky, není je tedy nutné vkládat ručně. Následně si můžete v rozbalovacích seznamech vybrat, která pole mají být zobrazena. Je možné vybírat si jednotlivá pole, nebo použít symbol "*" pro všechna pole z tabulky. U každého pole je také možné zvolit směr řazení které bude použito a jestli má být viditelné.
Podmínka v dotazu
Takto vytvořený dotaz jen zobrazí data z jedné nebo více tabulek, obvykle chceme trochu víc. Nejčastěji je to využití podmínky, tedy nastaveníkritérií vyhledávání. Podmínky se dají psát do spodního řádku, označeného "Kritéria". Nejsnazší je podmínka založená na porovnávání - čísel, dat nebo slov, porovnání je vždy stejné. Pokud budu chtít například najít lodě s výtlakem přes 10 000 tun, stačí přidat sloupec "Výtlak" z tabulky "Loď" a do řádku "Kritéria" napsat ">= 10 000". Dotaz najde všechny záznamy u kterých je ve sloupci Výtlak hodnota vyšší nebo rovna 10 000. Podmínky je možné spojovat logickými spojkami. Pokud tedy chci najít lodě s výtlakem v rozmezí 7 000 - 12 000 tun, podmínka vypadá následovně: ">= 7000 and <=12000". Lze používat spojky "and", "or" a "not", tedy obě podmínky platí zároveň, stačí že je splněna jedna z podmínek a podmínka není splněna.
Při práci s textem je to podobné. Pokud chcete hledat nějaký konkrétní text, stačí ho do položky "Kritérium" jen napsat. Pokud tedy do sloupce "Jméno" z tabulky "Loď" napíšu "Poseidon", najde Access všechny záznamy, kde je jméno lodi přesně Poseidon. Můžu chtít hledat jen podle části slova, například všechny lodě začínající na P - v takovém případě bude podmínka "Like P*". Výsledkem pak budou všechny lodě, jejichž jméno začíná na "P". Znak * zastupuje libovolný počet písmen. Základ práce se zástupnými znaky je shrnut v tabulce:
| Podmínka | Záznamy které najde |
|---|---|
| "Slovo" | Jen položky kde je přesně Slovo, nic víc ani méně. |
| Not "Slovo" | Všechny položky s jinou hodnotou než Slovo. |
| Like A* | Všechny položky začínající na písmeno A. |
| Like "*Slovo*" | Všechny položky které obsahují Slovo - před tím i za tím může být něco dalšího. |
| Like "*A" | Všechny položky, které končí na písmeno A. |
| Like "[A-F]*" | Všechny položky které začínají na jedno z písmen z rozsahu A-F. |
| Like "A???" | Všechny položky začínající na A, za kterým následují právě 3 znaky. |
| Is Null | Všechny záznamy, které nemají položku vyplněnu - má hodnotu Null. |
Existují i další druhy podmínek které se mohou objevit, jejich úplný přehled najdete tady. Základní jsou shrnuty v tabulce dole. Dávejte pozor na datový typ - nelze porovnávat čísla se slovy a tak dále, kritérium musí pracovat se stejným datovým typem jako sloupec nad kterým je.
| Podmínka | Záznamy které najde |
|---|---|
| Between 50 and 60 | Záznamy, jejichž hodnota je vyšší než 50 a nižší než 100. |
| In (10, 20, 30, 40) | Záznamy které mají jednu z hodnot uvedenou v seznamu (nemusí to být jen čísla, může jít i o text, pak musí být každá hodnota v uvozovkách). |
| #1/1/2005# | Pro prohledávání sloupce s datem, datum musí být z obou stran označeno znakem #. Najde záznamy které mají hodnotu 1.1.2005. |
| Date() | Vrátí záznamy obsahující aktuální datum. |
| Date() + 5 | Vrátí záznamy obsahující datum 5 dní po aktuálním datu. |
| DatePart("m", [Datum]) = 12 | Vrátí záznamy, jejichž datum obsahuje 12. měsíc - tedy všechna prosincová data. [Datum] je jméno sloupce, ve kterém jsou data uložena, v tomto případě je nutné ho do podmínky napsat explicitně. |
Zejména práce s datem je komplikovaná a umožňuje spoustu triků, podívejte se na ní v MS návodu věnovanému datu a času v dotazech.
Dotaz lze z návrhového zobrazení přepnout do zobrazení výsledků (datového listu) nebo SQL. SQL je možné modifikovat podle libosti, pokud ale dotaz příliš zkomplikujete, už nepůjde přepnout zpátky do návrhového zobrazení. Obecně platí, že SQL má větší "sílu" - umožňuje konstruovat víc dotazů, než je možné pomocí návrhového zobrazení
Při psaní dotazů majá zvláštní význam některé znaky. Uvozovky označují řětězce textu, takže "slovo" nebo "123" je vždy chápáno jako text a tak se s tím pracuje, není tedy možné k "123" přičítat a podobně. Do hranatých závorek se píší jména sloupců - jak je to vidět třeba při dotazu na prosincová data výše. Jestliže se pracuje s několika tabulkami, je nutné odlišit do jaké tabulky který sloupec patří, jeho jméno tedy tvoří jméno tabulky a jméno sloupce oddělené tečkou - např. [Loď.Postavena]. To je možné využít při sestavování podmínek k vlastnímu propojení tabulek. Kdyby mezi tabulkami Loď a majitel nebyla relace (kterou Access automaticky spravuje), bylo by tak možné tabulky propojit ručně přímo v dotazu. To se může hodit v případě, že mezi tabulkami žádná relace není, ale pro nějaký konkrétní úkol by se hodila. V našem případě by bylo do dotazu třeba přidat sloupce Majitel_ID z tabulky Loď a ID z tabulky Majitel a do sloupce Majitel_ID vložit podmínku [Majitel.ID] - viz obrázek.
Agregační dotazy
Agregační dotazy slouží k výpočtům souhrnných informací (jako je třeba průměr) nad vyhledanými daty. Podrobně jsou popsány v příslušném návodu. Můžete s nimi zjistit informace jako "Jaký je průměrný výtlak všech tankerů zadaného majitele" nebo "Jako celkovou tonáží (výtlakem) disponuje zadaný majitel", "Jaká je nejstarší loď registrovaná v zadaném přístavu" a podobně. Abyste mohli se souhrny pracovat v návrhovém zobrazení musíte je zapnout - v kartě "Dotazy-nástroje" a "Návrh" úplně vpravo najdete symbol velké sigma označený "Souhrny". Po jeho zapnutí se mezi řádky "Tabulka" a "Řadit" objeví nová položka - "Souhrn". V tomto řádku si pak můžete vybrat z několika agregačních funkcí. Základní jsou shrnuty v tabulce:
| Funkce | K čemu je |
|---|---|
| Sum | Součet všechn hodnot |
| Avg | Průměrná hodnota |
| Min | Nejmenší nalezená hodnota |
| Max | Největší nalezená hodnota |
| Count | Počet všech záznamů |
Nejsnáze si agregační funkci vyzkoušíte, když do dotazu dáte jen jeden sloupec a necháte spočítat jeho agregační funkci - například průměr. Výsledkem bude jedna hodnota, samozřejmě je nutné aby sloupec měl datový typ použitelný pro výpočet průměru. Často je ale úloha složitější. Typicky jsou data rozdělená na skupiny a agregační funkci je třeba spočítat pro každou skupinu zvlášť. Například zjistit kolik lodí má který majitel. Potřebujeme funkcu "Count", ale nedá se použít pro celou tabulku, musí být použita zvlášť pro lodě každého majitele. K tomu slouží funkce "Seskupit", kterou mezi agregačními funkcemi také najdete
K vytvoření takového dotazu potřebujete znát majitele a jejich lodě, do dotazu tedy vložímee jak tabulku lodí tak majitelů. Relace mezi nimi opět zajistí že budou data správně propojena. Do dotazu vložtě sloupec "Majitel" (ve kterém je jméno majitele) a jako agregační funkci zvolte "Seskupit". Data budou nyní seskupována podle jména majitele. Druhý sloupec bude obsahovat počet lodí v každé skupině. Je nutné sčítat nějaká data, nejlepší bude ID lodě, ale je možné použít jakýkoliv sloupec. Aby ve výsledku bylo vidět o co jde, využijeme alias - nazveme sloupec "Počet:ID" - Access ví že má pracovat se sloupcem "ID" z tabulky "Loď", ale bude zobrazovat název Počet. Agregační funkce - nastavení v poli Seskupit - bude Count.
I v agregačním dotazu může být podmínka - řekněme že bychom chtěl v našem příkladu spočítat jen lodě s výtlakem nad 10 000 tun. V takovém případě normálně přidejte sloupec s výtlakem a napište do něj podmínku. Je ale nutné aby tento sloupec nebyl viditlěný a také aby v řádkou "Souhrn" bylo zvoleno "Kde" - podle toho Access pozná že jde o podmínku. Pozor, nesmí tam být "Seskupit"! Jiank podmínka nebude fungovat.
Vypočtená pole
Poslední užtečnou možností je využití vypočítávaných polí. Výpočet musí být celý napsaný v názvu pole, v případě že by byl složitější, klikněte do jména vypočítávaného pole pravým tlačítkem a zvolte "Sestavit" - to otevře okno, ve kterém se dá snáze pracovat s vytvářeným výrazem. Výraz musí vždy začínat jménem sloupce, výpočet je za dvojtečkou. Například pokud by v databázi byla cena bez DPH, můžete vytvořit v dotazu sloupec "Cena s DPH" - výraz bude následující: "Cena s DPH: [Cena] * 1.2" - v novém sloupci tedy bude hodnota o 20% vyšší než v původním sloupci "Cena". Názvy sloupců musí být v hranatých závorkách. V případě že se používá víc tabulek, je třeba aby byl název o se jménem tabulky. V tomto případě je oddělovačem vykřičník, nikoliv tečka - tedy [Jméno tabulky]![Jméno sloupce].
Cvičení
Vaším úkolem je do vaší tabulky lodí doplnit několik dotazů - zkuste si následující možnosti:
- Dotaz nad jednou tabulkou, který vynechá některé sloupce
- Dotaz spojující dvě nebo více tabulek
- Dotaz s jednou nebo více podmínkami
- Dotaz s agregační funkcí - zkuste k němu i přidat nějakou podmínku
- Dotaz s vypočteným sloupcem, vyzkoušejte si i sestavení výrazu v Tvůrci výrazů
Zkuste se podívat na SQL vytvořených dotazů a porovnat si ho s tím co má dotaz dělá - snažte se identifikovat jednotlivé prvky SQL, kde jsou jména sloupců a tabulek, jak je udělaná vazba, kde je podmínka a podobně.
