Tipy pro MS Access - práce s dotazy

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