02   >   jak   >   Tipy pro MS Access - práce s dotazy
 

Autor: Jiří Mičke


Výběr pouze určitého počtu záznamů
K zobrazení určitého počtu záznamů s nejvyšší či nejnižší hodnotou jako příklad můžeme uvést seznam 20 prodejců s nejvyšší provizí použijte následující postup: V návrhovém zobrazení zvolte pole, která mají být zobrazena (např. příjmení prodejce a provize), dále zvolte Řadit sestupně podle pole provize, a v okně Vlastnosti dotazu zadejte hodnotu 20 v poli Nejvyšší hodnoty (v tomto poli můžete zadat i údaj v procentech, např. 5 %).
Pokud chcete, aby uživatel při spuštění dotazu k výběru záznamů měl možnost zvolit parametr, třeba příjmení prodejce, použijte tento postup: V návrhové mřížce dotazu v políčku Kritéria u pole prijmeni zapište text (v hranatých závorkách [Zadejte jméno prodejce:]), který se zobrazí v dialogovém okně pro zadání parametru před vlastním provedením dotazu (viz obr. 1).
Access dovoluje vnořovat dotazy SELECT; např. ke zjištění prodejců, kteří vyhovují podmínkám jiného dotazu (Dotaz2), můžete použít klíčové slovo EXISTS SELECT prijmeni, skupina FROM Prodejci WHERE EXISTS
SELECT * FROM dotaz2 WHERE Prodejci.Prijmeni=Prijmeni);
Použití dotazů v programovém kódu
Dotaz uložený v databázi (např. qryProdejci) můžete spustit v programu příkazem DoCmd.OpenQuery "qryProdejci" nebo dotaz přímo zapište DoCmd.RunSQL "DELETE * FROM prodejci WHERE obrat <5000" nebo Dbs.Execute "SELECT * FROM prodejci WHERE obrat>0 ORDER BY obrat DESC" (kde Dbs je proměnná typu Database).
Nechcete-li, aby se před provedením dotazu objevilo okno s upozorněním a nutností následného potvrzení (viz obr. 2), použijte metodu SetWarnings příkazu DoCmd. Probíhá-li dotaz delší dobu, použijte k zobrazení hodin metodu Hourglass. Ukázka použití by mohla vypadat takto:
DoCmd.Hourglass True
DoCmd. SetWarningsFalse
...
DoCmd.OpenQuery"qryProdejci"
...
DoCmd.Set WarningsTrue
DoCmd. Hourglass False
Chcete-li otevřít množinu záznamů na základě dotazu, můžete použít metodu OpenRecordset a jako parametr zadat dotaz SQL; např.:
Dim dbs As DATABASE, rst As Recordset, dotaz As
String
Set dbs = CurrentDb
dotaz = "SELECT * FROM Smlouvy WHERE ([Vysledek] =
\storno\)"
Set rst = dbs.OpenRecordset(dotaz)
Při použití dotazu, který obsahuje údaj typu datum, musíte tento údaj zapsat ve formátu používaném v USA (tedy mm/dd/yy). Špatně je tedy příkaz SELECT * FROM prodejci WHERE ([Datum_nar] >= # 11.1.1968#), správně pak příkaz ([Datum_nar] >= #1/11/1968#\). Pokud v dotazu používáte proměnnou, velmi užitečné je k převodu formátu datumu použít funkci Format.
Křížové dotazy
Nejjednodušší způsob, jak vytvořit křížový dotaz, je použití průvodce (v okně Nový dotaz zvolíte Průvodce křížovým dotazem). V jednotlivých krocích zvolíte potřebné parametry; definici dotazu můžete samozřejmě dodatečně pozměnit v návrhovém zobrazení nebo přímo kódu SQL (Access obsahuje příkaz TRANSFORM navíc oproti normě ANSI SQL).
Agregační funkce
Velmi užitečné při používání dotazů jsou agregační funkce Avg, Count, Min, Max, Sum a statistické funkce pro směrodatnou odchylku a rozptyl StDev a Var (a jejich odhady StDevP a VarP). Při použití těchto funkcí však nelze použít klauzuli DISTINCT, např. SELECT COUNT(DISTINCT id_prodejce) FROM prodeje pro zjištění počtu prodejců, kteří uskutečnili alespoň jeden obchod.
Zadáte-li jako parametr agregační funkce Count název určitého pole (např. Count([jmeno])), budou do součtu zahrnuty pouze záznamy, jejichž pole jmeno není prázdné (tedy neobsahuje hodnotu NULL). Chcete-li zjistit počet všech záznamů, použijte Count(*). Počet všech záznamů s neprázdnými hodnotami v polích jmeno a prijmeni zároveň pak zjistíte funkcí Count([jmeno]&([prijmeni]).
Na rozdíl od funkce Count funkce Sum (např. Sum([plat])) do výsledného součtu zahrne i záznamy s hodnotou NULL.
Funkce na výpočet aritmetického průměru Avg do výpočtu nezahrne hodnoty NULL. Při výpočtu průměrného věku obchodních partnerů je použití Avg([vek] v pořádku, neboť partneři s neznámým stářím prázdnou hodnotou v poli vek nejsou do výpočtu zahrnuti. Chybou však je výpočet průměrného počtu objednávek prodejců, kdy hodnota NULL "reprezentuje" nulový počet objednávek. V tomto případě použijte funkci Nz; tedy místo Avg([poc_obj]) použijte Avg(Nz([poc_obj])).
Další
Přidáváte-li záznamy do tabulky, která obsahuje pole typu Automatické číslo, budou přidané záznamy číslovány od nejvyšší hodnoty zvětšené o jednu tohoto pole v cílové tabulce (pokud jste ovšem předtím nesmazali záznamy v cílové tabulce s nejvyššími hodnotami bez provedení následné komprimace).
Ke zjištění záznamů, jež existují v jedné tabulce a nejsou v tabulce druhé; např. prodejci, kteří neuzavřeli ani jednu objednávku (tabulky Prodejci a Objednavky, vazba přes pole ID_prodejce), použijte buď Průvodce vyhledávacím dotazem (chybějící záznamy), nebo v návrhovém zobrazení vytvořte dotaz (u pole smlouvy.ID_Prodejce zadejte Zobrazit Ne, Kritéria Null). Jiná možnost je v zobrazení SQL přímo zapsat příkaz SELECT prodejci.* FROM prodejci LEFT JOIN objednavky ON Prodejci.ID_Prodejce = Smlouvy.ID_Prodejce WHERE ((smlouvy. ID_Prodejce IS NULL)).
Chcete-li zobrazit tzv. vypočítávané pole, např. cenu s DPH, a v tabulce máte cenu bez DPH (pole Cena), zadejte v mřížce návrhu do políčka Pole text Cena s DPH: [Cena]*1,22. Text Cena s DPH pak tvoří název vypočítávaného pole.
9 0047/OK